It is expected that this problem will be complete using

It is expected that this problem will be complete using an Excel spreadsheet using formulas. Please see the Excel Tutorial that is available under the course home tab. The Hale Company finished their sales projections for the coming year. The company produces one product. Part of next year's sales projections are as follows:

 


JulyAugustSeptemberOctoberNovember
Projected Sales in units100000125000156000165000185000
It is expected that this problem will be complete using


The budget committee has also compiled the following information on inventories:


 


Raw materialsWork-in-ProcessFinished Goods
Ending Balance, June22,000 lbsNone13,000 units
Desired ending levels (monthly)5% of next month's production needsNone12% of next month's sales


Engineering has developed the following standards upon which the production budgets will be developed:

 

ItemStandard
Materials usage5 lbs per unit
Material price per pound$1.50 per pound
Labor usage0.4 hours per unit
Labor rate$30 per hour
Machine hours3 machine hours per unit


The Hale Company uses a modified allocation method for allocating overhead costs. The rates that will be used in the coming year are as follows:

 

Overhead itemAllocation rate
Utilities$0.50 per machine hour
Inspection$10 per unit produced
Factory supplies$2 per unit produced
Depreciation$35,000 per month
Supervision$12,000 per month


Required:

Prepare the following production budgets for July, August, and September for the Hale Company:

1. Productionbudget