Question

Please show work and formulas used for excel

The SK Industries, Inc. manufactures and sells two products, Accel Active and Accel Regular. In September 2018, SK Industries Budget Department gathered the following data in order to prepare budgets for 2019:

2019 Projected Sales

Product	Units	Price
Accel Active	45,000	$225
Accel Regular	75,000	$155

Expected Target Inventories

Product	January 1, 2019	December 31, 2019
Accel Active	9,000	12,000
Accel Regular	17,000	25,000

The following direct materials are expected to be used in the two products:

		Material Used per Unit
Direct Material	Unit	Accel Active	Accel Regular
One	pound	6	4
Two	pound	4	2
Three	pound	2	0

Projected data for 2019 with respect to direct materials are as follows:

Direct Material	Expected Purchase Price	Expected Inventories (January 1, 2019)	Target Inventories (December 31, 2019)
One	$15	27,000 lb	35,000 lb
Two	$7	30,000 lb	35,000 lb
Three	$ 4	4,000 lb	7,000 lb

Projected direct labor requirements and rates for 2019 are as follows:

Product	Hours Per Unit	Rate Per Hour
Accel Active	4	$18
Accel Regular	3	$15

Manufacturing overhead is allocated at the rate of $22 per direct manufacturing labor-hour. Required: Based on the preceding projections and budget requirements for Accel Active and Accel Regular, prepare the following budgets for 2019:

1. Revenues budget (in dollars)
2. Production budget (in units)
3. Direct materials purchases budget (in quantities)
4. Direct materials purchases budget (in dollars)
5. Direct manufacturing labor budget (in dollars)
6. Direct manufacturing overhead budget (in dollars)
7. Budgeted finished goods inventory at December 31, 2019 (in dollars)
8. Cost of Goods Sold budget (in dollars)

Answer 1

Revenues budget (in dollars)
	Accel Active	Accel Regular	Total
Units Sold	45,000	75,000
Multiply: Selling Price per unit	$                   225	$                     155
Sales Revenue	$     10,125,000	$      11,625,000	$     21,750,000
Production budget (in units)
	Accel Active	Accel Regular
Units sold	45,000	75,000
Add: ending finished goods inventory	12,000	25,000
Units available for sale	57,000	100,000
Less: Beginning finished goods inventory	9,000	17,000
Units Produced	48,000	83,000
Direct materials purchases budget (in quantities)
	Material one	Material two	Material Three
Units Produced for Accel Active	48,000	48,000	48,000
Multiply: Material Used per Unit for Production of Accel Active	6	4	2
Material required for Production of Accel Active	288,000	192,000	96,000
Units Produced for Accel Regular	83,000	83,000	83,000
Multiply: Material Used per Unit for Production of Accel Regular	4	2	-
Material required for Production of Accel Regular	332,000	166,000	-
Material required for Production of Accel Active	288,000	192,000	96,000
Material required for Production of Accel Regular	332,000	166,000	-
Material required for Production of finished goods	620,000	358,000	96,000
Material required for Production of finished goods	620,000	358,000	96,000
Add: ending raw material	35,000	35,000	7,000
Raw material Available for use	655,000	393,000	103,000
Less: Beginning raw material	27,000	30,000	4,000
Purchase of material (in pounds)	628,000	363,000	99,000
Direct materials purchases budget (in dollars)
	Material one	Material two	Material Three	Total
Purchase of material (in pounds)	628,000	363,000	99,000
Multiply by: cost per Pound	$                      15	$                         7	$                        4
Cost of material purchased	$       9,420,000	$         2,541,000	$           396,000	$      12,357,000
Direct manufacturing labor budget (in dollars)
	Accel Active	Accel Regular	Total
Units Produced	48,000	83,000
Multiply: Hour per unit	4	3
Total Hour needed	192,000	249,000
Multiply: labour rate per hour	$                      18	$                       15
Direct labor cost	$       3,456,000	$         3,735,000	$       7,191,000
Direct manufacturing overhead budget (in dollars)
	Accel Active	Accel Regular	Total
Total Hour needed	192,000	249,000
Multiply: Manufacturing overhead per labour hour	$                      22	$                       22
Direct manufacturing overhead	$       4,224,000	$         5,478,000	$       9,702,000
Budgeted finished goods inventory at December 31, 2019 (in dollars)
	Accel Active	Accel Regular	Total
Material cost per unit
Accel Active (($15*6)+($7*4)+($4*2))	$                   126
Accel Regular (($15*4)+($7*2)+($4*0))		$                       74
Labor cost per unit
Accel Active ($18*4)	$                      72
Accel Regular ($15*3)		$                       45
Manufacturing overhead per unit
Accel Active ($22*4)	$                      88
Accel Regular ($22*3)		$                       66
Manufacturing cost per unit	$                   286	$                     185
Ending Finished goods inventory, December 31, 2019	12,000	25,000
Multiply: Manufacturing cost per unit	$                   286	$                     185
Budgeted finished goods inventory at December 31, 2019 (in dollars)	$       3,432,000	$         4,625,000	$       8,057,000
Cost of Goods Sold budget (in dollars)
	Accel Active	Accel Regular	Total
Units Sold	45,000	75,000
Multiply: Manufacturing cost per unit	$                   286	$                     185
Cost of Goods Sold budget (in dollars)	$     12,870,000	$      13,875,000	$     26,745,000