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

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

Month                                             Sales (units)

October 2022                                                             410

November                                                                    440

December                                                                     520

Japan, 2023                                                                 240

February                                                                       Z.20

March                                                                            410

April                                                                                450

May                                                                                620

June                                                                                750

July                                                                                 ;40

Auamt                                                                           3S0

September                                                                   420

Octobef                                                                        420

N.-..7..embe:                                                                450

December                                                                    600

Jar ear; 2; ==                                                               280

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:

Balance Sheet

Dagrercamp.Pty Ltd As al 31 December- 2022

Cash                                                                             $1,800     Accounts payable                                                                       $55,680

Account, receivable                                            253,960      Interest payable                                                                    1,8.75

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 equir.-                                                                                 2,230,410

Income Statement MaggrceMP. Ltd

For the period January—December 2022

Saks revenue                                               $4,240:000

Cost of goods sold                                        1,3 BO: 000

Gross profit                                                        460:000

Less:

Operating expenses                                         4-36: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 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.

ter Budget Do not copy and paste numbers: only