Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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

image text in transcribedimage text in transcribed

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Analysis And Decision Making

Authors: David E. Vance

1st Edition

0071406654, 9780071406659

More Books

Students also viewed these Accounting questions

Question

Can violence in the workplace be totally eliminated? Why?

Answered: 1 week ago

Question

Define induction and what are its objectives ?

Answered: 1 week ago

Question

Discuss the techniques of job analysis.

Answered: 1 week ago

Question

How do we do subnetting in IPv6?Explain with a suitable example.

Answered: 1 week ago

Question

Explain the guideline for job description.

Answered: 1 week ago

Question

What is job description ? State the uses of job description.

Answered: 1 week ago