Problem - 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:
|
|
July
|
August
|
September
|
October
|
November
|
|
Projected Sales in units
|
100,000
|
125,000
|
156,000
|
165,000
|
185,000
|
The budget committee has also compiled the following information on inventories:
|
|
Raw materials
|
Work-in-Process
|
Finished Goods
|
|
Ending Balance, June
|
22,000 lbs
|
None
|
13,000 units
|
|
Desired ending levels (monthly)
|
5% of next month's production needs
|
None
|
12% of next month's sales
|
Engineering has developed the following standards upon which the production budgets will be developed:
|
Item
|
Standard
|
|
Materials usage
|
5 lbs per unit
|
|
Material price per pound
|
$1.50 per pound
|
|
Labor usage
|
0.4 hours per unit
|
|
Labor rate
|
$30 per hour
|
|
Machine hours
|
3 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 item
|
Allocation 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: Materials purchase budget.
Note: 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.