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:

1. 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

2. 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

3. 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.

4. 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.

5. 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.

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

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

8. 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.

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

10. 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:

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

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

5. A cash payments schedule for October, November and December. Check figure: Octobers total cash payments should be $411,760.
6. 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.
7. 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.

8. A pro-forma retained earnings schedule for the quarter ended December 31^st. Check figure: Ending retained earnings should be $977,232.
9. 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.