The budget director of Hi Performance Athletic Co., with the assistance of the controller,
treasurer, production manger, and sales manager, has gathered the following data for
use in developing the budgeted income statement for January 2012.

a. Estimated sales for January
Batting helmets 305 unites @$70 per unit
Football helmet 630 units @$135 per unit

b. Estimated inventories at January
Direct materials: Finished products:
Plastic 80-lbs Batting helmet 35 units @$40 per unit
Foam lining 60-lbs Football helmet 40 units @$60 per unit

c. Desired Inventories at January 31:
Direct materials: Finished products:
Plastic 90-lbs Batting helmet 30 units @40 per unit
Foam lining 55-lbs Football helmet 50 units @$58 per unit

d. Direct materials used in production:
In manufacturing of batting helmet:
Plastic 1.20-lbs. per unit of product
Foam lining 0.50-lb. per unit of product
In manufacturing of football helmet
Plastic 2.80-lbs. per unit of product
Foam lining 1.40-lbs. per unit of product

e. Anticipated cost of purchases and beginning and ending inventory of direct material:
Plastic $5.50 per lb.
Foam lining $4.00 per lb.

f. Direct labor requirements:
Batting helmet:
Molding department 0.20 hr. @$15 per hr.
Assembly department 0.50 hr. @$13 per hr.
Football helmet
Molding department 0.30 hr. @$15 per hr.
Assembly department 0.65 hr. @$13 per hr.

g. Estimated factory overhead costs for January:
Indirect factory wages $14,500; Power and light $2,000
Depreciation of plant &
equipment $4,200
Insurance &property tax $1,700

h. Estimated operating expenses for January:
Sales salary expense $15,400
Advertising expense $8,500
Office salaries expense $11,500
Depreciation expense – office equipment $3,200
Telephone expense – selling $950
Telephone expense – administrative $600
Travel expense – selling $2,300
Office supplies expense $550
Miscellaneous administrative expense $00

i. Estimated other income and expense fro January:
Interest revenue $140
Interest expense $172

j. Estimated tax rate: 30%

Instructions: using Excel with the appropriate formulas do the following using one sheet for each.
1 Prepare a sales budget for January
2 Prepare a production budget for January
3 Prepare a direct materials purchases budget fro January
4 Prepare a direct labor cost budget for January
5 Prepare a factory overhead cost budget for January
6 Prepare a cost of goods sold budget for January. Work-in process at the beginning
of January is estimated at $12,500, and work in process at the end of January is
desired to be $13,500.
7 Prepare a selling and administrative expense budget for January
8 Prepare a budgeted income statement for January