Part 1: MASTER BUDGET

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

1. 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.
2. 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.
3. The desired monthly ending inventory is 20% of the following month’s estimated sales. The inventory on 31 December 2022 is 48 laptops.
4. 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: $500Depreciation: $2,800• Utilities: $1,200• Other operating expenses: $2,800
5. Dividends of $6,000 will be paid in March 2023.
6. 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.
7. 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.
8. 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)
9. 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:

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 budgetd Schedule of collections and accounts receivable at 31 December 2023e Schedule of payments and accounts payable at 31 December 2023f Cash budgetg Budgeted income statement for the period January – December 2023h 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.