Comparative Survey Data Analysis
Budget: €30 – €250 EUR
I'm looking to analyze some survey results. The ultimate goal is to conduct a comparative analysis that will enable me to make meaningful interpretations from the data. The objective is to distil complex data served from multiple survey results and extract information that can be used for decision-making purposes.
In the scope of this project, the deliverable should include:
Financial Plan (in Excel)
The Finance & Accounting related tasks include four key elements: Budgetary assumptions and sales forecast[1], Operating budgets, Cash Budget and financing budget, and Financial Statement budgets. Each of these elements is discussed briefly below.
The budgetary period is 12 months long: starting on July 1, 2024, and ending on June 30, 2025. You need to create monthly budgets except for the balance sheet and the budgeted cash flow statement, which are prepared on a quarterly basis. Euro (€) is preferred as the currency of the financial plan. If you use another currency, please make sure you consistently apply it throughout the whole financial plan.
1. Budgetary assumptions (in the text boxes of the Excel sheet)
State your assumptions related to the standard cost price. Which direct cost elements did you take into account in relation to the goods that your company buys and sells?
State your assumptions in the indicated text boxes in the master budget (excel sheet template).
2. Operating budgets (In Excel sheet)
You need to create the operating budgets of your company for the budgetary period in the template provided. Most details are found in the company profiles. In order to complete this task, the following specific operating budgets must be included in your implementation plan:
Sales Budget
To show estimated sales revenue per month.
Explain the sales numbers in the text boxes.
S&OP Budget
To show the ordering of goods (purchases), taken into account the demand (based on sales budget), safety stock, leading to end inventory
Cost of Goods Purchased Budget
To show the estimated cost of the units to be purchased.
Cost of Goods Sold Budget
To show the estimated cost of units to be sold.
CAPEX Budget
To show the estimated amount and timing of purchasing non-current assets. This should also include considerations regarding depreciation.
Selling, General & Administrative Expenses Budget (SG&A)
To show the estimated amount of operating expenses, including depreciation. (Interest expense and taxes are not part of SG&A!).
3. Cash budget (in Excel sheet)
You need to prepare the cash budget for the budgetary period of 12 months in the template provided. Explain the end of the month target cash balance and how you finance this because it is not allowed to have a negative end balance of cash (based on your figures; this is not a theoretical question).
Bank Loan is to be considered. A bank loan is needed when the month-end balance of cash shows a negative amount.
IMPORTANT! If you face a cash deficit at the end of a period ("end balance of cash"), you need to take measures using the described facilities to cover the deficit or reconsider the financial plan.
4. Financial budgets (in Excel sheet)
You need to prepare the budgeted income statement for the budgetary period of 12 months on a monthly basis, the budgeted balance sheet at the end of every quarter within the fiscal year 2024/2025, and the budgeted statement of cash flows for every quarter of the fiscal year as well as for the total fiscal year in Excel by using the operating budgets and the cash budget as input. The indirect method is used to prepare the cash flow statement.
Comment on the expected profitability of the company based on your figures by exploring the underlying reasons for positive/negative/increasing/decreasing profit figures (use text box in sheet Income Statement). Briefly comment on the expected financial position of your company at the end of the fiscal year (use text box in sheet Balance Sheet).
Students should ensure that the three financial statements (income statement, balance sheet, cash flow statement) are nicely related by the use of proper formulas.
In the scope of this project, the deliverable should include:
Financial Plan (in Excel)
The Finance & Accounting related tasks include four key elements: Budgetary assumptions and sales forecast[1], Operating budgets, Cash Budget and financing budget, and Financial Statement budgets. Each of these elements is discussed briefly below.
The budgetary period is 12 months long: starting on July 1, 2024, and ending on June 30, 2025. You need to create monthly budgets except for the balance sheet and the budgeted cash flow statement, which are prepared on a quarterly basis. Euro (€) is preferred as the currency of the financial plan. If you use another currency, please make sure you consistently apply it throughout the whole financial plan.
1. Budgetary assumptions (in the text boxes of the Excel sheet)
State your assumptions related to the standard cost price. Which direct cost elements did you take into account in relation to the goods that your company buys and sells?
State your assumptions in the indicated text boxes in the master budget (excel sheet template).
2. Operating budgets (In Excel sheet)
You need to create the operating budgets of your company for the budgetary period in the template provided. Most details are found in the company profiles. In order to complete this task, the following specific operating budgets must be included in your implementation plan:
Sales Budget
To show estimated sales revenue per month.
Explain the sales numbers in the text boxes.
S&OP Budget
To show the ordering of goods (purchases), taken into account the demand (based on sales budget), safety stock, leading to end inventory
Cost of Goods Purchased Budget
To show the estimated cost of the units to be purchased.
Cost of Goods Sold Budget
To show the estimated cost of units to be sold.
CAPEX Budget
To show the estimated amount and timing of purchasing non-current assets. This should also include considerations regarding depreciation.
Selling, General & Administrative Expenses Budget (SG&A)
To show the estimated amount of operating expenses, including depreciation. (Interest expense and taxes are not part of SG&A!).
3. Cash budget (in Excel sheet)
You need to prepare the cash budget for the budgetary period of 12 months in the template provided. Explain the end of the month target cash balance and how you finance this because it is not allowed to have a negative end balance of cash (based on your figures; this is not a theoretical question).
Bank Loan is to be considered. A bank loan is needed when the month-end balance of cash shows a negative amount.
IMPORTANT! If you face a cash deficit at the end of a period ("end balance of cash"), you need to take measures using the described facilities to cover the deficit or reconsider the financial plan.
4. Financial budgets (in Excel sheet)
You need to prepare the budgeted income statement for the budgetary period of 12 months on a monthly basis, the budgeted balance sheet at the end of every quarter within the fiscal year 2024/2025, and the budgeted statement of cash flows for every quarter of the fiscal year as well as for the total fiscal year in Excel by using the operating budgets and the cash budget as input. The indirect method is used to prepare the cash flow statement.
Comment on the expected profitability of the company based on your figures by exploring the underlying reasons for positive/negative/increasing/decreasing profit figures (use text box in sheet Income Statement). Briefly comment on the expected financial position of your company at the end of the fiscal year (use text box in sheet Balance Sheet).
Students should ensure that the three financial statements (income statement, balance sheet, cash flow statement) are nicely related by the use of proper formulas.