Course event

You were recently hired by Spears Classic Globes as controller of its Toxic Globes Subsidiary (TG). One of your first tasks is to prepare the master budget for the fourth quarter of 2021. 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, 2021 TG had the following balance sheet:

Spear's Classic Globes - Toxic Globe Division
Balance Sheet
September 30, 2021
Cash	$ 14,000		Accounts payable	$ 135,375
Accounts receivable	396,000		Taxes payable	37,000
Inventory	199,500		Total current liabilities	172,375
Prepaid insurance	8,000		Long-term loan payable	300,000
Total current assets	617,500		Total liabilities	472,375
Property, plant and equipment	1,275,000		Common shares	20,000
Accumulated amortization	482,000		Retained earnings	918,125
Net property, plant and equipment	793,000		Total shareholders equity	938,125
Total assets	$ 1,410,500		Total liabilities and equity	$ 1,410,500

2. TG sells the globes for $300 each. Recent and forecasted sales (in units) are as follows:

July (actual) 1,200

August (actual) 1,300

September (actual) 1,500

October 1,400

November 2,200

December 2,500

January 800

February 700

3. Management has a policy of having enough globes on hand at the end of each month to cover 75% of the next months projected sales. At the end of September the company had 1,050 globes in inventory. Each globe costs the company $190. Inventory purchases are paid half in the month of purchase and half the following month.

4. All sales are on credit with 25% of receivables being collected in the month of sale, 60% collected the month after sale and the final 15% collected two months after sale.

5. Fixed operating costs are expected to be $55,000 every month. Out of this amount $800 is expensing of prepaid insurance (insurance is paid once a year in August) and $14,000 is depreciation. The company also expects variable operating costs to be $20 per globe sold.

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

7. A dividend of $15,000 will be declared and paid in November.

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

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

10. The company must maintain a minimum cash balance of $10,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 6% per annum. Any cash above $10,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, and complete the assignment individually. Use formulas wherever possible. 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, 2021 (as given).

2. A cash receipts schedule for October, November and December. Check figure: Cash receipts for October should be $433,500.

3. A purchases schedule in units for October, November and December. Check figure: October purchases should be 2,000 units.

4. A cash payments for purchases for October, November and December.

Check figure: October cash payment for purchases should be $325,375

5. A cash payments schedule for October, November and December. Check figure: Octobers total cash payments should be $482,575.

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 $10,000 and the cumulative loan should be $45,075.

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 $51,100.

8. A pro-forma retained earnings schedule for the quarter ended December 31^st. Check figure: Ending retained earnings should be $1,180,739.

9. A pro-forma balance sheet at December 31^st. Hint: Consider what will cause balances to change from the September 31 2021 balance sheet. Check figure: Total assets should be $1,711,793.

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, its 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.

Plagiarism: Each person is to create his/her own Excel file and do this assignment individually. You can help each other with formulas, checking numbers, etc., but no copying. This is a 5% pass/fail in-class assignment.