Question: 1. Using the data from 2019 in Exhibit 9.1, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of

1. Using the data from 2019 in Exhibit 9.1, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI Inc., raging from a 20 percent decrease to 20 percent increase. Use Exhibits 9.2 and 9.6 as a guide. Assume that two-thirds of fixed costs are manufacturing related; the remaining one-third are selling-related. The variable manufacturing cost per unit is $30, while the variable selling cost per unit is $5. (Hint: Calculate the DOL for 2019 at a sales volume of 2,400 units. How does this figure help you interpret your sensitivity analysis results?)

2. Using the spreadsheet you created, compute the new operating profit assuming a 10% decrease in demand.

3. Use the Goal Seek tool within Excel to determine the sales price per unit that would allow HFI to earn an operating profit of $100,000, assuming that all the other cost information is the same as in Exhibit 9.1. Use Exhibit 9.5 as a guide.

Step by Step Solution

★★★★★

3.34 Rating (172 Votes )

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

1 A variety of possible spreadsheets could satisfy this requirement One example is given below using ... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Cost Management Strategic Questions!

1. Using the data from 2016 in Exhibit 9.1, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI, Inc. Use Exhibits...

Turn to Kool-Times flexible budget in Exhibit 10-3. 1. Using the data from Exhibit 10-3, develop flexible budgets for four- and nine-pool levels of output. 2. Would Kool-Times managers use the...

Using the data from Exercises 3-1 and 3-2, prepare financial statements for May, including income statement, retained earnings statement, balance sheet, and statement of cash flows.

EXHIBIT 9.2 Contribution Income Statements for HFI's Proposed Gaming Table 2019 2020 Amount Percent Amount Percent Change Notes $180,000 100.00% $195,000 100.00% $15,000 Sales Variable costs 84.000...

1. Using the data from 2022 in Exhibit 9.1, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI Inc., ranging from a...

Contribution Income Statements Using Excel; Sensitivity Analysis; Goal Seek Input Data: Per-Unit 2010 2011 Fixed mfg costs $40,000 $40,000 Fixed selling costs $20,000 $20,000 Revenue $75.00 Variable...

EFB106 COST AND BENEFIT ANALYSIS FOR PROJECT APPRAISAL Assessment Item 1: Sensitivity Analysis Online resource: http://www.cbabuilder.co.uk/ CBA Builder Excel Tool:...

Please help me solve this Required: 1. Using the data from 2022 in Gaming Table Cost Data, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential...

Required: 1. Using the data from 2022 in Gaming Table Cost Data, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operatinq profit of potential chanqes in demand for HFI...

Required: 1 . Using the 2 0 X 1 data in the Gaming Table Cost Data tab, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand...

Refer to Exercise 2.72. Prepare the cumulative frequency, cumulative relative frequency, and cumulative percentage distributions using the frequency distribution table constructed for the data of...

With voter turnout during presidential elections around 50% a vital task for politicians is to try to predict who will actually vote. Develop a regression model to predict intention to vote...

. Descriptive statistics _ _ _ _ _ _ _ _ . quickly describe large amounts of data can predict future stock returns with surprising accuracy help statisticians understand non - numeric information,...

(b) "Economic analysis suggests that there is good reason to expect trade restrictions to exist in the real world." Could both (a) and (b) be true? Explain.

a. It is said (S. Branch Walker) that the Indian who sold Manhattan for $24 was a sharp salesman. If he had put his $24 away at 6% compounded semiannually, it would now be worth over $9 billion, and...

VacuTech is a high-technology company that manufactures sophisticated instruments for testing microcircuits. Each instrument sells for $3,500; variable manufacturing cost per unit (all cash) equals...

Rob Roy Corporation has been using its present facilities at its annual full capacity of 10,000 units for the last three years. Still, the company is unable to keep pace with continuing demand for...

3 f An options portfolio consists of the following positions: 1. Call: Position = -1000, Delta = 0.50, Gamma = 2.2, Vega= 1.8 2. Call: Position=-500, Delta = 0.80, Gamma = 0.6, Vega=0.2 3. Put:...

1. Annualizing Percentage Returns James Leddy and Austin Power compete with each other. Each one argued that he is the best investor. Please be a judge for them. James Leddy bought 200 shares of XYZ...

What will a deposit of $4.500 left in the bank be worth under the following conditions. Left for nine years at 7% interest Left for six years at 10% compounded semiannually Left for five years at 8%...