You were recently hired by LWs Bottle Emporium Ltd (LW) as financial controller LW sells a high end set of baby bottles including various sizes to meet the childs needs throughout their early years One of your first tasks is to prepare the master budget for the fourth quarter of 2020 To assist you in the preparation of the budget, and supporting schedules, you have been provided with the following information As of September 30, 2020 LW had the following balance sheet LWs Bottle Emporium Ltd Balance Sheet September 30, 2020 Cash $ 10,000 Accounts payable $ 100,640 Accounts receivable 371,250 Taxes payable 15,000 Inventory 133,200 Total current liabilities 115,640 Prepaid insurance 10,000 Long term loan payable 250,000 Total current assets 524,450 Total liabilities 365,640 Property, plant and equipment 1,125,000 Common shares 50,000 Accumulated amortization 454,000 Retained earnings 779,810 Net property, plant and equipment 671,000 Total shareholders equity 829,810 Total assets $ 1,195,450 Total liabilities and equity $ 1,195,450 LW sells the bottle sets for $275 each Recent and forecasted sales (in units) are as follows July (actual) 1,000 August (actual) 1,150 September (actual) 1,600 October 1,200 November 2,150 December 2,400 January 900 February 850 Management has a policy of having enough bottle sets on hand at the end of each month to cover 60 of the next months projected sales At the end of September, the company had 720 bottle sets in inventory Each bottle set costs the company $185 Inventory purchases are paid 60 in the month of purchase and 40 the following month All sales are on credit with 30 of receivables being collected in the month of sale, 50 collected the month after sale and the final 20 collected two months after sale Fixed operating costs are expected to be $42,000 every month Out of this amount, $1,000 is expensing of prepaid insurance (insurance is paid once a year on August 1 st ) and $12,000 is depreciation The company also expects variable operating costs to be $17 per bottle set sold Management plans to purchase a new delivery truck in October at a cost of $54,000 The company will pay cash A dividend of $11,000 will be declared and paid in December Interest is paid monthly on the long term loan at a rate of 6 per year The full principal amount will be repaid on September 30 th , 2026 Income tax expense is calculated as 28 of the earnings before taxes The company pays income tax installments of $10,000 per month The company must maintain a minimum cash balance of $9,000 A short term loan is available to cover any shortfall Interest is paid monthly on the previous months loan balance at a rate of 7 per annum Any cash above $9,000 at month end will be used to reduce any existing short term loan Required Use a spreadsheet application such as Excel to complete this assignment Each student is to create his her own Excel file (from a blank Excel I will be checking the file properties), and complete the assignment individually Use formulas wherever possible you will thank yourself Your spreadsheet should be formatted to show amounts to the nearest dollar (no cents) Your instructor will tell you exactly what to submit and when, and will inform you as to penalties for late submission The items in the budget should appear in the following order The balance sheet for September 30, 2020 (as given) A cash receipts schedule for October, November and December Check figure Cash receipts for October should be $382,250 A purchases schedule in units for October, November and December Check figure October purchases should be 1,770 units A cash payments for purchases for October, November and December Check figure October cash payment for purchases should be $297,110 A cash payments schedule for October, November and December Check figure Octobers total cash payments should be $411,760 A cash budget for October, November and December, including a calculation of cumulative loan at the bottom Check figure At the end of October the cash balance should be $9,000 and the cumulative loan should be $28,510 The pro forma income statements for October, November and December You should also have a total column which totals all three months Subtotals for EBIT and EBT should be included List all expenses separately (do not combine) Show long term and short term interest separately Hint Cost of goods sold is not the same thing as purchases Check figure October earnings after taxes should be $31,932 A pro forma retained earnings schedule for the quarter ended December 31 st Check figure Ending retained earnings should be $977,232 A pro forma balance sheet at December 31 st Hint Consider what will cause balances to change from the September 30, 2020 balance sheet Some items will change even though a cash payment didnt occur Check figure Total assets should be $1,454,286

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 24, 2024

You were recently hired by LWs Bottle Emporium Ltd. (LW) as financial controller. LW sells a high-end set of baby bottles including various sizes to

You were recently hired by LWs Bottle Emporium Ltd. (LW) as financial controller. LW sells a high-end set of baby bottles including various sizes to meet the childs needs throughout their early years. One of your first tasks is to prepare the master budget for the fourth quarter of 2020. To assist you in the preparation of the budget, and supporting schedules, you have been provided with the following information:

As of September 30, 2020 LW had the following balance sheet:

LWs Bottle Emporium Ltd.
Balance Sheet
September 30, 2020

Cash	$ 10,000	Accounts payable	$ 100,640
Accounts receivable	371,250	Taxes payable	15,000
Inventory	133,200	Total current liabilities	115,640
Prepaid insurance	10,000	Long-term loan payable	250,000
Total current assets	524,450	Total liabilities	365,640
Property, plant and equipment	1,125,000	Common shares	50,000
Accumulated amortization	454,000	Retained earnings	779,810
Net property, plant and equipment	671,000	Total shareholders equity	829,810
Total assets	$ 1,195,450	Total liabilities and equity	$ 1,195,450

LW sells the bottle sets for $275 each. Recent and forecasted sales (in units) are as follows:

July (actual) 1,000

August (actual) 1,150

September (actual) 1,600

October 1,200

November 2,150

December 2,400

January 900

February 850

Management has a policy of having enough bottle sets on hand at the end of each month to cover 60% of the next months projected sales. At the end of September, the company had 720 bottle sets in inventory. Each bottle set costs the company $185. Inventory purchases are paid 60% in the month of purchase and 40% the following month.

All sales are on credit with 30% of receivables being collected in the month of sale, 50% collected the month after sale and the final 20% collected two months after sale.

Fixed operating costs are expected to be $42,000 every month. Out of this amount, $1,000 is expensing of prepaid insurance (insurance is paid once a year on August 1^st) and $12,000 is depreciation. The company also expects variable operating costs to be $17 per bottle set sold.

Management plans to purchase a new delivery truck in October at a cost of $54,000. The company will pay cash.

A dividend of $11,000 will be declared and paid in December.

Interest is paid monthly on the long-term loan at a rate of 6% per year. The full principal amount will be repaid on September 30^th, 2026.

Income tax expense is calculated as 28% of the earnings before taxes. The company pays income tax installments of $10,000 per month.

The company must maintain a minimum cash balance of $9,000. A short-term loan is available to cover any shortfall. Interest is paid monthly on the previous months loan balance at a rate of 7% per annum. Any cash above $9,000 at month end will be used to reduce any existing short-term loan.

Required:

Use a spreadsheet application such as Excel to complete this assignment. Each student is to create his/her own Excel file (from a blank Excel I will be checking the file properties), and complete the assignment individually. Use formulas wherever possible you will thank yourself! Your spreadsheet should be formatted to show amounts to the nearest dollar (no cents). Your instructor will tell you exactly what to submit and when, and will inform you as to penalties for late submission.

The items in the budget should appear in the following order:

The balance sheet for September 30, 2020 (as given).
A cash receipts schedule for October, November and December. Check figure: Cash receipts for October should be $382,250.
A purchases schedule in units for October, November and December. Check figure: October purchases should be 1,770 units.
A cash payments for purchases for October, November and December.

Check figure: October cash payment for purchases should be $297,110.

A cash payments schedule for October, November and December. Check figure: Octobers total cash payments should be $411,760.
A cash budget for October, November and December, including a calculation of cumulative loan at the bottom. Check figure At the end of October the cash balance should be $9,000 and the cumulative loan should be $28,510.
The pro-forma income statements for October, November and December. You should also have a total column which totals all three months.
1. Subtotals for EBIT and EBT should be included.
2. List all expenses separately (do not combine).
3. Show long-term and short-term interest separately.
4. Hint: Cost of goods sold is not the same thing as purchases.

Check figure: October earnings after taxes should be $31,932.

A pro-forma retained earnings schedule for the quarter ended December 31^st. Check figure: Ending retained earnings should be $977,232.
A pro-forma balance sheet at December 31^st. Hint: Consider what will cause balances to change from the September 30, 2020 balance sheet. Some items will change even though a cash payment didnt occur. Check figure: Total assets should be $1,454,286.