BUDGETING AND SENSITIVITY ANALYSIS ASSIGNMENT
Due date: 30 May 2023 at 08:00
Weighting: 40% (40 marks) of total marks to be awarded for the course
GUIDANCE TO COMPLETE THE ASSIGNMENT:
This assignment is a group assignment of 5 students per group. It consists of the following three parts:
1. Master budget (21 marks): use the workbook “Budgeting Assignment Template” to enter the data and prepare the set of budgets of the Master Budget of MasterComp Pty Ltd for the year 2023.
2. Sensitivity Analysis (3 marks): manipulate key operating policy variables using the “What if Budgets”, created from the Master Budget, to conduct sensitivity analysis answering the questions and make decisions.
3. Questions (14 marks): answer the five questions related to financial analysis, variable costing, cash flow analysis, and short-run decisions in the “Questions” worksheet.
Please follow the instructions below:
1. You have to submit your Master Budget using the template workbook you downloaded from “Budgeting Assignment”.
2. The set of budgets included in the Master Budget must be prepared in the spaces provided in the “Master Budget” worksheet (the template). Data must be entered in the “Data Section” of the template. The links between the data and the budgets and between the budgets must be through Excel formulas. Do not type, or copy and paste, numbers in your budgets. Numbers should come from the formulas in each cell.
3. One objective of this assignment is the examination of the budgeting procedure and accuracy of your master budget. So, please check and re-check the formulas and be sure you have completed your master budget without errors.
4. The second part of the assignment asks for assessing changes in policies to conduct a sensitivity (“What if”) analysis. Your answers to the sensitivity analysis questions must be on the “What if Budget” worksheet and they must come from the budgets of the “What if Budget” scenario. A cross-checking between your “What if Budget” and your answers will be performed. This procedure is to ensure that your group has prepared the budgets, you understand the budgets, and you can interpret the results correctly. To answer the questions, you must copy your Master Budget, once completed, into “What if Budget” tab of your Excel workbook and make the changes required by the alternative or case scenario. Only in the “What-if” worksheet you can modify the data either manually or with formulas to do the changes in the budgets copied from the Master Budget (Do notmodify data in the Data Section).
5. The third part of the assignment relates to the answer of five questions detailed in “Part 3: Questions” These questions must be answered in the spaces provided in the Excel document, worksheet “Questions”.
6. No extension will be granted for this assignment.
Part 1: MASTER BUDGET
MasterComp Pty Ltd
MasterComp Pty Ltd imports a new brand of laptops, the TH1 laptop, from a foreign supplier. There are other similar laptops in the market but at higher prices with similar quality and characteristics. The TH1 laptops are sold directly to organisations and retailers in Australia.
MasterComp Pty Ltd started two years ago. The company owns a property in Brisbane in which is located the administration and sales office, and the warehouse. You are the owner and Managing Director of this company. The company has a total of 7 employees, 5 for the administration department and 2 salespersons.
Actual sales (from October to December 2022) and projected sales (from January to December 2023) of laptops, in units, are the following:
Month
Sales (units)
October 2022
410
November
440
December
520
January 2023
240
February
320
March
410
April
450
May
620
June
750
July
340
August
380
September
420
October
420
November
450
December
600
January 2024
280
2. The current and estimated selling price of each laptop is $400, which has not been changed since January 2022. Cash sales, received in the month of the sale, account for 30% of total sales. The remaining 70% of sales are on credit. Pattern of Collection of credit sales is: 40% are collected in the month following the sale, 30% in the second month following the sale and 30% in the third month following the sale.
3. Purchase cost of the laptops delivered to MasterComp’s warehouse is $300 per unit, which is the same cost per laptop during 2022. MasterComp Pty Ltd pays 60% of purchases in the same month of purchase and 40% in the month following the purchase.
4. The desired monthly ending inventory is 20% of the following month’s estimated sales. The inventory on 31 December 2022 is 48 laptops.
5. Operating expenses are all paid in cash (except depreciation) in the month they are incurred. The company has identified the following monthly operating expenses for the budgeted period:
· Managing director salary: $8,000
· Average salary per employee of administration department: $2,800
· Salary salespersons (each): $400 plus sales commission 3% of sales of the month.
· Insurance: $500
· Depreciation: $2,800
· Utilities: $1,200
· Other operating expenses: $2,800
6. Dividends of $6,000 will be paid in March 2023.
7. MasterComp purchased furniture for the administration office for $12,000, paid cash, on 1 July 2023. The office furniture has a useful life of 4 years without residual value. The company uses the straight-line method of depreciation.
8. MasterComp has a policy of maintaining a cash balance of $1,800 at the end of each month to cover unforeseen needs of cash. If this requirement cannot be met, MasterComp has a standby line of credit for a total of $200,000 arranged with its bank to borrow the exact amount needed to achieve the desired cash balance. If MasterComp estimates a cash shortage by the end of the month, sufficient cash is borrowed at the first day of the month to cover the shortage and end the month with the required cash balance. If MasterComp has an estimated cash balance greater than $1,800 at the end of any month and an outstanding balance in the line of credit at the beginning of the month, then the cash over $1,800 is used to repay the bank at the first day of the month to end the month with a cash balance of $1,800. The interest rate applicable to the line of credit is 15% per annum to be paid monthly on the first day of the following month the interest is incurred, calculated on the principal amount outstanding at the end of the month. The line of credit used (owed to the bank) at 31 December 2022 is $150,000.
9. The tax rate is 30%. Income tax for the year 2022 will be paid in April 2023. Income tax for the year 2023 will be paid in April 2024 (assume the tax year and the financial year end on 31 December)
10. The following balance sheet at 31 December 2022 and income statement for the period January – December 2022 have been prepared from the accounting records of MasterComp Pty Ltd:
Balance Sheet
MasterComp Pty Ltd
As at 31 December 2022
Cash
$1,800
Accounts payable
$55,680
Accounts receivable
253,960
Interest payable
1,875
Inventory
14,400
Tax payable
840
Loan payable (line of credit)
150,000
Share capital
2,021,075
Equipment (net)
1,960,250
Retained earnings
940
Total Assets
2,230,410
Total liabilities and equity
2,230,410
Income Statement
MasterComp Ltd
For the period January – December 2022
Sales revenue
$1,840,000
Cost of goods sold
1,380,000
Gross profit
460,000
Less:
Operating expenses
436,000
Interest expenses
21,200
Net profit before tax
2,800
Tax expense
840
Net profit after tax
1,960
Part 1: Master Budget (21 marks according to marking guidelines)
As the owner of MasterComp Pty Ltd you are to prepare a master budget for the year 2023. The master budget is to consist of the following budgets (the budgets should show the figures for each month and a total for the twelve-month period, ending on 31 December 2023, where appropriate. The figures shown in the worksheet (using the template) should be without decimals, but the calculations (formulas) should use all decimals.
a Sales budget (dollars)
b Purchases budget (dollars)
c Operating expenses budget
d Schedule of collections and accounts receivable at 31 December 2023
e Schedule of payments and accounts payable at 31 December 2023
f Cash budget
g Budgeted income statement for the period January – December 2023
h Budgeted balance sheet at 31 December 2023
The budget documents are to be prepared using the Excel workbook template you downloaded from the budgeting assignment on the course Canvas. This workbook consists of four worksheets: 1) one Data worksheet to identify your group including a template to add all the relevant data which will be used to prepare the set of budgets in the Master Budget worksheet; 2) one Master Budget worksheet, which includes the templates to prepare the set of budgets required linked by formulas to the data worksheet; 3) one worksheet with the “What if” scenario, in which you will copy the Master Budget worksheet into What if Budget worksheet following the instructions in this document; and 4) one worksheet “Questions”, in which you have to answer the five questions in the spaces provided in this worksheet.
It is important to note that the figures in the What if Budgets worksheet will initially be the same as the Master Budget worksheet derived from formulas that relates to the information in the Data worksheet. Then, you can change manually in the “What-if” scenario the data that have been modified by the case scenario.
The following instructions must be followed for constructing the master budget:
· Display the budgets in the worksheets with all measures and dollar amounts without decimals, but your calculations and formulas in the worksheets should use all decimals.
· Monthly budgets columns and the total for the twelve-months period column are required.
· Only formulas are to be used in the budgets, you must keep the data in the “Data worksheet”. Create links between the data worksheet and the Master Budget worksheet and also between the budgets of the Master Budget. Do not copy and paste numbers, only formulas.
Part 2: “What if” budgets (3 marks)
You are not happy with the resulting budgeted income statement, and you want to increase the level of profitability of the company. In addition, you want to reduce the debt of the line of credit with the bank as much as possible. After a brainstorming, thinking of possible alternatives for improving the profits of the company, and after some conversations with the customers you believe the following alternative that may have an impact on the company’s profitability and an improvement of cash flows to reduce the level of debt is an achievable alternative:
Increase by 5% the price per laptop, effective from 1st March 2023, which will decrease the estimated number of units sold by 10% from the same month. To avoid a higher decrease in the number of laptops sold, hire one additional salesperson, in the same conditions as the other two salesperson, starting on 1 March 2023.
You are required to conduct a what if analysis for this alternative to assess its effect on the level of profitability and cash flows for MasterComp for the year ending on 31 December 2023 answering the following questions:
Related to the Master Budget:
· In the year 2023 profit increases/(decreases) by $………………
· At the end of December 2023, the Loan increases/(decreases) by $………………
· What is your decision regarding this alternative? Why? …………………
Hint: Once completed the Master Budget (with no further changes), copy the “Master Budget” worksheet into the What if worksheet and replace the data given for the What if alternative using formulas or directly the figures given in the new worksheet, creating new links if necessary (do not modify the data in the Data worksheet and Master Budget worksheet).
Part 3: Questions (14 marks)
Answer the following questions in the “Questions” worksheet of the Excel document:
1) Compare income statement for the year 2022 with the budgeted income statement (Master Budget) for the year 2023, and using the balance sheet at 31 December 2022 and the balance sheet at 31 December 2023, make a comment on the changes of profitability (based on net profit margin and return on equity), liquidity (based on quick ratio) and capital structure (based on debt ratio). To calculate the ratios, use the formulas provided in topic 6 using the ending balances of the balance sheet accounts. You can use percentages with two decimals, or as a number with four decimals. Consider the line of credit as current liability. (3 marks)
2) Prepare a budgeted contribution margin income statement for the period January – December 2023. Compare the profit after tax in both the budgeted contribution margin income statement and the traditional budgeted income statement. Are the profits of both income statements the same or different? Why? (3 marks)
3) Based on the original data provided, determine the budgeted breakeven point for this company in units and dollars for the period January – December 2023. (2 marks)
4) You receive a special order from a retailer in Fiji, which is a new customer, for 100 laptops paid in cash, for a price of $340 per unit, but MasterComp has to pay the air freight delivery of $4,200. Would you accept or not this order? Why? (explain your decision made by quantitative factors and include one qualitative factor that may change your decision). (3 marks)
5) At the end of 2023, the statement of cash flows for the year 2023 was as shown below. Compare the actual statement of cash flows with the budgeted cash flows (that you prepared in the “Budget” column below) and explain the favourable/unfavourable variances in the net cash from operating, investing and financing activities. (3 marks)
Actual ($)
2023
Budget ($)
2023
Variances
Cash flows from operating activities
Cash receipts from customers
2,090,446
Cash payments for operating activities
(2,023,814)
Net cash provided by operating activities
66,632
Cash flows from investing activities
Purchase of office furniture
(16,000)
Net cash used in investing activities
(16,000)
Cash flows from financing activities
Repayment of line of credit
(44,632)
Payment of dividends
(6,000)
Net cash provided by financing activities
(50,632)
Net increase in cash
0
Cash at the beginning of the year
1,800
Cash at the end of the year
1,800
Marking Guidelines Budgeting Assignment
1. Master Budget:Total 21 marks
The marks for the master budget are given for the accuracy of the following figures less 50% of the marks in each item if formulas are not used in each cell.
a. Sales budget: 1 mark
b. Purchases budget (units and dollars): 1 mark
c. Operating expenses budget (separated into variable and fixed costs): 2 marks
d. Schedule of collections and accounts receivable: 2 marks
e. Schedule of payments and accounts payable: 2 marks
f. Cash budget 5 marks
g. Budgeted income statement 4 marks
h. Budgeted balance sheet 4 marks
Total 21 marks
Note. There are no partial marks. To get each mark you must have the correct figure in each item. You can see the items that will be marked in the worksheet template highlighted in yellow color.
2. “What if” budgets: 3 marks
3. Questions:
1) Financial analysis comparing 2022 and 2023 3 marks
2) Contribution margin income statement 3 marks
3) Breakeven point in units and dollars 2 marks
4) Special order (quantitative decision and qualitative factor) 3 marks
5) Variance analysis of cash flows 3 marks
Total 14 marks
Total Marks: 40 marks
END OF THE ASSIGNMENT
