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

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