You were recently hired by LW's Bottle Emporium Ltd. ("LW") as financial controller. LW sells a high-end set of baby bottles including various sizes to meet the child's 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:

LW's 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

October1,200

November2,150

December2,400

January900

February850

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 month's 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 .Use formulas wherever possible - you will thank yourself!Your spreadsheet should be formatted to show amounts to the nearest dollar (no cents).

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:October's 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.

i.Subtotals for EBIT and EBT should be included.

ii.List all expenses separately (do not combine).

iii.Show long-term and short-term interest separately.

iv.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 didn't occur.

Check figure: Total assets should be $1,454,286.

Other information:

Format:

For numbers, use the Accounting format with zero decimal places.

If your ending balance sheet is out by $1 or $2, do not worry about it, it's just rounding error.

Marks will be awarded for a neat, well laid out and formatted spreadsheet.It should be easy to follow and have a good flow to it.

Schedules should flow down a worksheet, not side by side.So, the opening balance sheet would be at the top, the cash receipts schedule below it.

Lines should be included to show where calculations occur, with double lines at the bottom of each schedule.See the balance sheet on the previous page.

Dollar signs should be used only at the top and bottom of columns in a schedule, not on every number.

Formulas:Many of the numbers in the schedules, most of the numbers in the income statement, and all of the numbers in the retained earnings statement and the closing balance sheet should be the result of formulas or called cells.For the pro-forma balance sheet, some of the formulas will start with a reference to the opening balance sheet.For example, taxes payable will be the opening amount from September 30, plus total tax expense, minus total tax instalments.

Cell referencing:Whenever possible, cell referencing must be used - you will not get the check figures unless you use cell referencing whenever possible.An efficient schedule design allows you to make changes in one area and have them update automatically in others