After reviewing your cost analysis from assignment 2, management has asked you t
ID: 2462246 • Letter: A
Question
After reviewing your cost analysis from assignment 2, management has asked you to perform a “what-if” (AKA a sensitivity analysis) analysis for expected changes in fixed costs and the number of different expected direct labor hour changes in production.
To complete assignment 5 include the following in your worksheet(s). Remember to carefully plan the layout of the requested schedules to provide a professional, clear and easily understood presentation. You can create new worksheets for the schedules if you wish.
Worksheet requirements:
Using your assignment 2 cost data complete the following in Excel:Using the data from assignment 2, calculate the total fixed and variable costs for the business. (Assume the cost behaviors provided on the following sheet to adjust your calculations, if needed).
Use the Slope() and Intercept() functions to determine variable and/or fixed costs. Remember the intercept function may give very small fixed costs for some items and may be ignored.
Calculate the average number of quarterly direct labor hours.
Clearly label each of the calculated values in a separate section of the worksheet or in a new worksheet.
Using your calculated fixed and variable costs create a formula to calculate total costs based on your calculated fixed cost, variable cost, and the average direct labor hours.
Management expects average quarterly direct labors to be as provided on the following page.
Management also expects fixed costs to be as provided on the following page.
Using the formula you created in d above and the provided quarterly direct labor hours and fixed costs, prepare an analysis estimating total quarterly costs at each of the expected DL and fixed costs. Use a data table as demonstrated in class.
Submission requirements:
Save your worksheet using the following naming convention: UNIQUENAMEDATATABLE.XLSX
Submit your Workbook through the Chapter 5 Assignment on Canvas
Advertising Stickers
Variable
Apprentice Carpenter Labor
Variable
Carpenter Labor
Variable
Cleaning supplies
Mixed
Depreciation
Fixed
Finishing Supplies
Variable
General Supplies
Fixed
Hardware
Variable
Indirect Salaries
Fixed
Information Stickers
Variable
Insurance
Fixed
Master Carpenter Labor
Variable
Packaging
Variable
Parts and Repairs
Mixed
Shipping Costs
Variable
Utilities
Mixed
Warehouse Rent
Fixed
Wood
Variable
Expected total quarterly fixed costs:
177,500
197,100
219,000
243,500
267,800
294,500
324,000
Expected total quarterly direct labor hours:
6,500
7,500
8,500
9,500
10,500
11,500
12,500
this is the assignent 2 data
Quarterly Costs
Q1
Q2
Q3
Q4
Annual Total
Advertising Stickers
$ 2,700
$ 3,300
$ 4,050
$ 4,950
$ 15,000
Apprentice Carpenter Labor
18,900
23,100
28,350
34,650
105,000
Carpenter Labor
33,075
40,425
49,613
60,637
183,750
Cleaning supplies
4,700
5,500
6,400
7,400
24,000
Depreciation
90,000
90,000
90,000
90,000
360,000
Finishing Supplies
86,400
105,600
129,600
158,400
480,000
General Supplies
9,000
9,000
9,000
9,000
36,000
Hardware
21,600
26,400
32,400
39,600
120,000
Indirect Salaries
118,500
118,500
118,500
118,500
474,000
Information Stickers
8,100
9,900
12,150
14,850
45,000
Insurance
3,000
3,000
3,000
3,000
12,000
Master Carpenter Labor
20,250
24,750
30,375
37,125
112,500
Packaging
3,375
4,125
5,063
6,179
18,741
Parts and Repairs
3,850
4,456
4,722
4,972
18,000
Shipping Costs
32,400
39,600
48,600
59,400
180,000
Utilities
11,172
14,997
12,336
21,495
60,000
Warehouse Rent
18,000
18,000
18,000
18,000
72,000
Wood
540,000
660,000
810,000
990,000
3,000,000
Total Expenses
$1,025,022
$1,200,653
$1,412,158
$1,678,158
$5,315,991
Quarterly DL Hours
Total DLH
6,525
7,975
9,788
11,963
36,251
Advertising Stickers
Variable
Apprentice Carpenter Labor
Variable
Carpenter Labor
Variable
Cleaning supplies
Mixed
Depreciation
Fixed
Finishing Supplies
Variable
General Supplies
Fixed
Hardware
Variable
Indirect Salaries
Fixed
Information Stickers
Variable
Insurance
Fixed
Master Carpenter Labor
Variable
Packaging
Variable
Parts and Repairs
Mixed
Shipping Costs
Variable
Utilities
Mixed
Warehouse Rent
Fixed
Wood
Variable
Explanation / Answer
we summarise as follows
Total Variable cost
Total Fixed cost
Advertising 15000 Apprentice carpenter labor 105000 Carpenter labor 183750 Finishing supplies 480000 Hardware 120000 Information stickers 45000 Master carpenter labor 112500 PAckaging 18741 Shipping 180000 Wood 3000000 Total VAriable 4259991 Total direct labor hours 36251 VAriable overhead per hour = 4259991 / 36251 117.51