Correction to the Balancesheet (updated)

Balance Sheet		Dec-19
Cash		5,000,000
Accounts Receivable		22,856,100
RM Inventory		40,440
FG Inventory		10,456,088
Land		10,000,000
Equipment		75,000,000
Accumulated Depreciation		(25,000,000)
Total Assets		98,352,628
Accounts Payable		1,500,000
Wages Payable		810,000
Line of Credit		-
Total Liabilities		2,310,000
Common Shares		25,000,000
Retained Earnings		71,042,628
Total Equity		96,042,628
Total Liabilities and Equity		98,352,628

Prepare a Master budget using the following informations. 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. 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. 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. Once its built, your financial statements should balance and reconcile and any change to assumptions should flow through and balance on the financial statements. Actual and forecast quarterly sales are below: Sales ($000) Information about the company 42,000 Actual 38,500 Actual 56,700 Actual 36,402 Actual 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 Nagar Waqar 50,400 Forecast 46,200 Forecast 68,040 Forecast 43,680 Forecast 47,880 Forecast 43,890 Forecast 66,452 Forecast 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 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 $1M 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. Direct labour cost is $30 per hour 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. Prepare a Master budget using the following informations. 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. 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. 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. Once its built, your financial statements should balance and reconcile and any change to assumptions should flow through and balance on the financial statements. Actual and forecast quarterly sales are below: Sales ($000) Information about the company 42,000 Actual 38,500 Actual 56,700 Actual 36,402 Actual 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 Nagar Waqar 50,400 Forecast 46,200 Forecast 68,040 Forecast 43,680 Forecast 47,880 Forecast 43,890 Forecast 66,452 Forecast 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 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 $1M 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. Direct labour cost is $30 per hour 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