PLEASE SHOW HOW I WOULD DO THIS ON EXCEL Waterways Corporation is continuing its
ID: 2567642 • Letter: P
Question
PLEASE SHOW HOW I WOULD DO THIS ON EXCEL
Waterways Corporation is continuing its budget preparations. Waterways had the following static budget and actual overhead for October. (Refer to Waterways 9 if you are uncertain about variable versus fixed costs.)
Waterways Corporation Waterways Corporation
Manufacturing Overhead Budget Manufacturing Overhead Costs (Actual)
(Static) For the Month of October For the Month of October
Budgeted Production (Units)
117,500
Production in units
118,500
Indirect materials
$ 5,875
Indirect materials
$ 5,910
Indirect labor
14,100
Indirect labor
14,195
Utilities
11,750
Utilities
11,880
Maintenance
8,225
Maintenance
8,275
Salaries
42,000
Salaries
42,000
Depreciation
16,800
Depreciation
16,800
Property taxes
3,000
Property taxes
3,000
Insurance
1,200
Insurance
1,200
Janitorial
1,500
Janitorial
1,500
Total budgeted costs
$104,450
Total costs
$104,760
Waterways produced 118,500 units in October rather than the budgeted number of units.
Instructions:
a. Prepare a flexible budget overhead budget based on the following amounts produced.
(1) 115,000 units
(2) 116,000 units
(3) 117,000 units
(4) 118,000 units
(5) 119,000 units
b. Prepare a flexible budget performance report showing the differences (favorable and unfavorable) in manufacturing overhead costs for the month of October.
c. Prepare a responsibility report for the manufacturing overhead for October, assuming only variable costs are controllable.
Budgeted Production (Units)
117,500
Production in units
118,500
Indirect materials
$ 5,875
Indirect materials
$ 5,910
Indirect labor
14,100
Indirect labor
14,195
Utilities
11,750
Utilities
11,880
Maintenance
8,225
Maintenance
8,275
Salaries
42,000
Salaries
42,000
Depreciation
16,800
Depreciation
16,800
Property taxes
3,000
Property taxes
3,000
Insurance
1,200
Insurance
1,200
Janitorial
1,500
Janitorial
1,500
Total budgeted costs
$104,450
Total costs
$104,760
Explanation / Answer
Answer 1
budgeted production Actual Production Flexible units
particular 117500 cost / unit 118500 115000 116000 117000 118000 119000
VARIABLE COST
Indirect Material 5875 0.05 5910 5750 5800 5850 5900 5950
Indirect Labour 14100 0.12 14195 13800 13920 14040 14160 14280
Utilities 11750 0.1 11880 11500 11600 11700 11800 11900
Maintainance 8225 0.07 8275 8050 8120 8190 8260 8330
Total Variable cost 39950 0.34 39100 39440 39780 40120 40460
FIXED COST
Salaries 42000 42000 42000 42000 42000 42000 42000
Depreciation 16800 16800 16800 16800 16800 16800 16800
property tax 3000 3000 3000 3000 3000 3000 3000
Insurance 1200 1200 1200 1200 1200 1200 1200
Janitorial 1500 1500 1500 1500 1500 1500 1500
Total Fixed cost 64500 64500 64500 64500 64500 64500 64500
ANSWER 2
budgeted production Actual Production Variance F/U
particular 117500 118500
VARIABLE COST
Indirect Material 5875 5910 -35 U
Indirect Labour 14100 14195 -95 U
Utilities 11750 11880 -130 U
Maintainance 8225 8275 -50 U
Total Variable cost 39950 39950
FIXED COST
Salaries 42000 42000
Depreciation 16800 16800
property tax 3000 3000
Insurance 1200 1200
Janitorial 1500 1500
Total Fixed cost 64500 64500