You are the assistant controller for Stellar Statues.After the abysmal failure of their original business plan, Stellar Statues has found success by selling various styles of garden statues.You have been asked to prepare the master budget for the quarter ended June 30^th, 2018.You have assembled the following information:

The statues sell for $40 each.Recent and forecasted sales (in units) are as follows:

April2,800

May3,500

June4,900

July3,600

August2,200

September1,400

Inventories of finished goods on hand at the end of each month are to be equal to 50% of the following months budgeted sales.As of March 31st the company had 1,400 statues in inventory.

Each statue requires two kilograms of clay, which the company purchases for $3 per kilogram.Stellar Statues keeps an ending inventory of clay at the end of each month equal to 60% of the next month's production needs.As of March 31st the company had 3,780 kilos of clay on hand.

Purchases of raw materials are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month.

All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 60% of a month's sales are collected by month-end. An additional 40% is collected in the month following. Bad debts have been negligible.

Each statue requires 30 minutes to cast and paint.Employees who make the statues are paid $15 per hour and never work overtime (i.e. the company has enough casual workers that they can call in if additional work is required).

Manufacturing overhead includes all the costs of production other than direct materials and direct labor.The variable component is $4 per statue in production and the fixed component is $10,000 per month (this amount includes depreciation of $3,000 per month on the kilns and molds).Direct labor hours is used as an allocation base for assigning manufacturing overhead to units produced.

Stellar statues' monthly operating expenses are given below:

Variable:

Shipping costs$5 per statuette sold

Fixed:

Wages & salaries$8,000

Utilities3,000

Depreciation750

Miscellaneous2,000

All operating expenses are paid during the month in cash, with the exception of the depreciation.Management has decided to release a new line of statues.To accommodate the expected increase in demand the company will be purchasing a new kiln in April for $20,000 and some new moulds in May for $10,000.

The balance sheet at March 31^st is given below:

Stellar Statues

Balance Sheet

March 31, 2018

Assets

Cash$12,000

Accounts receivable57,600

Inventory, raw materials11,340

Inventory, finished goods 26,020

Total current assets$106,960

Property Plant and Equipment

Fixed assets$163,500

Accumulated depreciation(47,000)116,500

Total Assets$ 223,460

Liabilities

Accounts payable, raw materials$ 8,430

Equity

Capital stock, no par60,000

Retained earnings155,030

Total liabilities and equity$ 223,460

Management of Stellar Statues requires a minimum ending cash balance each month of $38,000.The company can borrow money from its bank at 12% annual interest or 1% per month simple interest (not compounded). All borrowing must be done at the beginning of a month, and repayments must be made at the end of a month. Repayments of principal must be in round $1,000 amounts. Borrowing is also in round $1,000 amounts. Interest is computed and paid at the end of each quarter on all loans outstanding during the quarter. Round all interest payments to the nearest whole dollar. Compute interest using whole months. The company wishes to use any excess cash to pay loans off as rapidly as possible.

REQUIRED:

Using excel, on one worksheet, prepare the following:

1.Opening balance sheet, March 31, 2018 in good format (heading, dollar signs etc.)

2.Sales budget for April, May, and June and with totals for the quarter.

3.Schedule of expected cash collections for April, May, and June and with totals for the quarter.

4.Accounts receivable, June 30, 2018

5.Production budget for April, May, and June and with totals for the quarter.

6.Direct materials budget for April, May, and June and with totals for the quarter.

7.Schedule of expected cash disbursements for materials for April, May, and June and with totals for the quarter.

8.Accounts payable, June 30, 2018

9.Manufacturing overhead budget for April, May, and June and with totals for the quarter.

10.Ending finished goods inventory budget.When calculating the predetermined overhead rate and the cost of a unit, use 2 decimal places.E.g. $6.15.Include a calculation of budgeted finished goods inventory, June 30, 2018.

11.Selling and administration expense budget with totals for the quarter.

12.Cash budget with totals for the quarter.Below the cash budget, show the balance in the loan account.

13.Schedule of cost of goods manufactured for the quarter ended June 30, 2018 in good form.This is one schedule for the full quarter.

14.Income statement for the quarter ended June 30, 2018 in good form.Prepare one statement for the full quarter.

15.Balance sheet, June 30, 2018 in good form.

All schedules and statements should be formatted professionally.

For statements, including schedule of cost of goods manufactured you must have headings.

$ signs go at the top of the column and at the end of column beside the final total.

For other schedules only put $ signs beside dollar amounts and have $ signs at the top of the column and at the end beside the final total.

Ensure you have quarter totals for the schedules and budgets.

Except when calculating the predetermined overhead rate and the cost per unit, round all answers to the nearest $1.