After reviewing your cost analysis from assignment 2, management has asked you t

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

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

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

Kindly post the question all over again with the relevant data from the respective assignmets.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.