COULD YOU PLEASE HELP ME SOLVE THIS MASTER BUDGET!

2 Master Budget Assignment (1) Saved to my Mac References Mailings Review View Required: Today is December 31, 2019. Year-end is done and the company's balance sheet at December 31, 2019 is available to you. Please build a quarterly budget forecast for 2020 using the information below. Your forecast will result in projected financial statements including an income statement and a balance sheet. Build your spreadsheet so that the calculations are linked avoiding hard-coded numbers where ever possible. Where a per cent or number needs to be entered, list it in the data input section and then link to it. That way a user can see what assumptions you have used and change them easily. Once its built, your financial statements should balance and reconcile and any change to assumptions should flow through and balance on the financial statements. There is a starting template provided to give you some guidance on the structure, Information about the company The balance sheet at December 31, 2019 is below: Balance Sheet Cash Accounts Receivable RM Inventory FG Inventory Land Equipment Accumulated Depreciation Total Assets Dec-19 5,000,000 22,856,100 40,440 10,343,098 10,000,000 75,000,000 (25,000,000) 98,239,638 1,500,000 810,000 Accounts Payable Wages Payable Line of Credit Total Liabilities 2,310,000 Common Shares Retained Earnings Total Equity Total Liabilities and Equity 25,000,000 70,929,638 95,929,638 98,239,638 Direct labour cost is $30 per hour Focus E F nited States) L ucu TV rences Mailings Review View In December 2019, 250,000 kg of material was purchased and 180,000 direct labour hours were worked. Labour rates in 2019 are the same as in 2020. The number of finished good units on hand at December 31, 2019 is in line with the targeted inventory levels There are no cash sales Each pound of material costs $15 and this was the same in 2019 as well. Actual and forecast quarterly sales are below: Sales ($000) 2019 42,000 Actual 38,500 Actual 56,700 Actual 36,402 Actual 50,400 Forecast 46,200 Forecast 68,040 Forecast 43,680 Forecast 47,880 Forecast 43,890 Forecast 66,452 Forecast There is 2,696 kg of raw materials in inventory on December 31, 2019. The company likes to keep 15% of the next quarter's production requirements on hand as ending raw materials inventory. Collections of sales occurs over time. On average, 40% is collected in the quarter of sale, 50% in the following quarter and 5% in the quarter after that. Dividends of SIM will be declared in the fourth quarter of 2020. The selling price per unit is $3,000. The company uses absorption costing and allocates manufacturing overhead on the basis of direct labour hours, $50,000,000 of equipment will be purchased in Q3, 2020, but won't be in service until 2021. Fixed manufacturing overhead is $1,000,000 per month and the variable portion is $850 per unit. Depreciation expense is included in this amount for $500,000 per quarter. Due to payroll processing time, 85% the wages earned as direct labour are paid in the quarter of production and the balance in the following quarter. Labour can be adjusted up and down each quarter without any penalty or minimum costs. ates) Focus E nces Mailings Review View Variable selling and admin costs are $20 per unit and fixed costs are $190,000 per quarter. There is no depreciation in this number. The company wants a minimum cash balance if $10M at the end of each quarter. Finished goods inventory is to be maintained at 20% of the combined next two quarter's sales Each unit takes 10 direct labour hours and 20 kg of material to manufacture. Accounts payable (direct materials only) are paid 60% on the quarter of purchase and 40% in the following quarter. The company has an unused line of credit for up to $10M at an interest rate of 4% per annum. Assume borrowing happens on the first day of the quarter, and interest is calculated quarterly and deducted from the bank account on the last day of the quarter. (Note: To avoid a circular reference in excel, you can allow the ending cash balance to drop below $10M by the amount of interest expense deducted) Repayments of the line of credit should occur as soon as excess cash is available. Assume repayments happen on the last day of the quarter. tes) Focus E 2 Master Budget Assignment (1) Saved to my Mac References Mailings Review View Required: Today is December 31, 2019. Year-end is done and the company's balance sheet at December 31, 2019 is available to you. Please build a quarterly budget forecast for 2020 using the information below. Your forecast will result in projected financial statements including an income statement and a balance sheet. Build your spreadsheet so that the calculations are linked avoiding hard-coded numbers where ever possible. Where a per cent or number needs to be entered, list it in the data input section and then link to it. That way a user can see what assumptions you have used and change them easily. Once its built, your financial statements should balance and reconcile and any change to assumptions should flow through and balance on the financial statements. There is a starting template provided to give you some guidance on the structure, Information about the company The balance sheet at December 31, 2019 is below: Balance Sheet Cash Accounts Receivable RM Inventory FG Inventory Land Equipment Accumulated Depreciation Total Assets Dec-19 5,000,000 22,856,100 40,440 10,343,098 10,000,000 75,000,000 (25,000,000) 98,239,638 1,500,000 810,000 Accounts Payable Wages Payable Line of Credit Total Liabilities 2,310,000 Common Shares Retained Earnings Total Equity Total Liabilities and Equity 25,000,000 70,929,638 95,929,638 98,239,638 Direct labour cost is $30 per hour Focus E F nited States) L ucu TV rences Mailings Review View In December 2019, 250,000 kg of material was purchased and 180,000 direct labour hours were worked. Labour rates in 2019 are the same as in 2020. The number of finished good units on hand at December 31, 2019 is in line with the targeted inventory levels There are no cash sales Each pound of material costs $15 and this was the same in 2019 as well. Actual and forecast quarterly sales are below: Sales ($000) 2019 42,000 Actual 38,500 Actual 56,700 Actual 36,402 Actual 50,400 Forecast 46,200 Forecast 68,040 Forecast 43,680 Forecast 47,880 Forecast 43,890 Forecast 66,452 Forecast There is 2,696 kg of raw materials in inventory on December 31, 2019. The company likes to keep 15% of the next quarter's production requirements on hand as ending raw materials inventory. Collections of sales occurs over time. On average, 40% is collected in the quarter of sale, 50% in the following quarter and 5% in the quarter after that. Dividends of SIM will be declared in the fourth quarter of 2020. The selling price per unit is $3,000. The company uses absorption costing and allocates manufacturing overhead on the basis of direct labour hours, $50,000,000 of equipment will be purchased in Q3, 2020, but won't be in service until 2021. Fixed manufacturing overhead is $1,000,000 per month and the variable portion is $850 per unit. Depreciation expense is included in this amount for $500,000 per quarter. Due to payroll processing time, 85% the wages earned as direct labour are paid in the quarter of production and the balance in the following quarter. Labour can be adjusted up and down each quarter without any penalty or minimum costs. ates) Focus E nces Mailings Review View Variable selling and admin costs are $20 per unit and fixed costs are $190,000 per quarter. There is no depreciation in this number. The company wants a minimum cash balance if $10M at the end of each quarter. Finished goods inventory is to be maintained at 20% of the combined next two quarter's sales Each unit takes 10 direct labour hours and 20 kg of material to manufacture. Accounts payable (direct materials only) are paid 60% on the quarter of purchase and 40% in the following quarter. The company has an unused line of credit for up to $10M at an interest rate of 4% per annum. Assume borrowing happens on the first day of the quarter, and interest is calculated quarterly and deducted from the bank account on the last day of the quarter. (Note: To avoid a circular reference in excel, you can allow the ending cash balance to drop below $10M by the amount of interest expense deducted) Repayments of the line of credit should occur as soon as excess cash is available. Assume repayments happen on the last day of the quarter. tes) Focus E