Joshies a company that manufactures and sells outdoor yard sculptures. The company president, Josh, has expressed a desire to improve the companys planning for cash needs. She has approached you, a management accounting student, for help in preparing the budget for the upcoming third quarter. She would like to see the sources and uses of cash by month, for the next quarter ending Sept. 30. Your investigations of the companys records have revealed the following information:

(PLEASE PROVIDE EXCEL FORMULAS)

1. Sales forecast:

First Quarter ended March 31 4,700 units

Second Quarter ended June 30 4,500 units

Third Quarter ended September 30 5,000 units

Fourth Quarter ended December 31 3,000 units

2. History has shown the sales pattern is consistent in each quarter, where sales in the first month of the quarter are 35% of the total for the quarter, 30% in the second month, and 35% in the third month of the quarter.

3. Sales are on a cash and credit basis, with 70% collected during the month of the sale, and 30% the following month. The selling price is $110 per unit throughout the year.

4. From previous experience, management has determined that an ending finished goods inventory equal to 20% of the next months sales is required to meet customer demand. Opening finished goods inventory consists of 350 sculptures.

5. The primary raw material used is a concrete and resin mix that is purchased by the kg and mixed with water, then molded into the statue shape. Each statue requires 10 kg of mix, and the price of mix is $3.00 per kg. Garden Beauty finds it necessary to maintain an inventory balance equal to 15% of the following months production needs of mix as a precaution against stock-outs. Opening raw materials inventory consists of 2,550 kg of mix. Garden Beauty pays for 75% of a months purchases in the month of purchase and 25% in the following month. The cost of water is included in utilities cost, and the molding machines are considered long lived assets.

6. Beginning accounts payable will consist of $12,176 arising from the estimated direct material purchases for June.

7. While much of the molding process is automated, the finishing process is labor intensive. Employees are paid an average of $28 per hour, including the employers portion of employee benefits. All payroll costs are paid in the period in which they are incurred.

Each sculpture spends a total of 45 minutes in production.

8. Due to the companys concentration on a single product, manufacturing overhead is allocated based on volume (i.e. the units produced). The variable overhead manufacturing rate is $10.25 per unit.

9. The fixed manufacturing overhead costs for the quarter are as follows:

Property and business taxes 6,000

Supervisors salary 24,000

Depreciation on equipment 36,000

$ 66,000

• The property and business taxes are paid on July 1 of each year.

• Garden Beauty uses the straight line method of depreciation.

10. Selling and administrative expenses are known to be a mixed cost, consisting of $2.00 per unit variable cost and $2,000 per month fixed costs. These costs are paid as they are incurred.

11. During the fiscal year ended December 31, 2020, Garden Beauty will be required to make quarterly income tax installment payments of $6,500 on the last day of each quarter.

12. Garden Beauty is planning to acquire additional manufacturing equipment for $56,000 cash. 40% of this amount is to be paid in July, 2020, the rest in September, 2020. The manufacturing overhead costs shown above already include the depreciation on this equipment.

13. The president tells you that the board of directors of Garden Beauty is planning on continuing their policy of declaring dividends of $10,000 per quarter.

14. A listing of the relevant balances in the companys ledger accounts as of June 30, 2020 follows:

	Cash				$ 63,365
	Accounts receivable				51,975
	Inventory-raw materials (2,550 kg)				7,650
	Inventory-finished goods (350 units)				26,191
	Accounts payable				12,176

Required:

Prepare a monthly master budget for Garden Beauty for the quarter ended September 30, 2020, including the following schedules:

Sales Budget & Schedule of Cash Receipts

Production Budget

Direct Materials Budget & Schedule of Cash Disbursements

Direct Labour Budget

Manufacturing Overhead Budget

Ending Finished Goods Inventory Budget (including calculation of budgeted Cost of Goods Sold)

Selling and Administrative Expense Budget

Cash Budget

Additional Information:

Students are required to prepare a monthly master budget for the quarter ended September 30, 2020 based on the information posted. The budget must be prepared in Microsoft Office Excel with the following criteria:

1. For ease of formatting, use one worksheet for each schedule
2. Prepare the schedules with the months going across the worksheet, not down. You must also have a total column at the end to show the quarters budget results.
3. Prepare a data page detailing the information, to be used as reference other worksheets.
4. Link as many of the numbers as possible using formulas. Every number on the cash budget should be referenced from a supporting page, or a sum on the page.
5. Use Excel calculation tools as much as possible.
6. If the headings are set up on the first worksheet, they can be copied to all the other worksheets to save time.
7. Proper formatting should be used so the budgets will look professional.
8. Look under the word format in the toolbar to find the correct way to underline accounting numbers (single and double).
9. Set Excel to record negative numbers within parenthesis e.g. (1,000), not -1,000
10. Round all numbers to the nearest dollar, with the exception of per unit amounts.

A sample budget spreadsheet has been posted to use as an example, but due to the numerous differences, it will not be efficient to use it as a template. You must create an original excel workbook for your submission.