Allied Food Products is considering expanding into the fruit juice business with a new fresh
lemon juice product. Assume that you were recently hired as assistant to the director of capital
budgeting and you must evaluate the new project.
The lemon juice would be produced in an unused building adjacent to Allieds Fort Myers plant;
Allied owns the building, which is fully depreciated. The required equipment would cost
$200,000, plus an additional $40,000 for shipping and installation. In addition, inventories would
rise by $25,000, while accounts payable would increase by $5,000. All of these costs would be
incurred at t = 0. By a special ruling, the machinery could be depreciated under the MACRS
system as 3-year property. The applicable depreciation rates are 33%, 45%, 15%, and 7%.
The project is expected to operate for 4 years, at which time it will be terminated. The cash
inflows are assumed to begin 1 year after the project is undertaken, or at t = 1, and to continue
value of $25,000.
Unit sales are expected to total 100,000 units per year, and the expected sales price is $2.00 per
unit. Cash operating costs for the project (total operating costs less depreciation) are expected
to total 60% of dollar sales. Allied's tax rate is 40%, and its WACC is 10%. Tentatively, the lemon
juice project is assumed to be of equal risk to Allied's other assets.
You have been asked to evaluate the project and to make a recommendation as to whether it
should be accepted or rejected. To guide you in your analysis, your boss gave you the following
set of tasks/questions.

This from Fundamentals of financial management concise 9e chapter 12 mini case need help to solve the sensitivity analysis on excel for part f.

PART F
(2) How would you perform a sensitivity analysis on the unit sales, salvage value, and WACC
for the project? Assume that each of these variables deviates from its base-case, or
expected, value by plus or minus 10%, 20%, and 30%.
The base case value for unit sales was 100,000; therefore, if you were to assume that this value
deviated by plus and minus 10%, 20%, and 30%, the unit sales values to be used in the sensitivity
analysis would be 70,000, 80,000, 90,000, 110,000, 120,000, and 130,000 units. You would then go
back to the table at the beginning of the problem, insert the appropriate sales unit number, say
70,000 units, and rework the table for the change in sales units arriving at different project
free cash flow values. Once you had the free cash flow values, you would calculate the NPV,
IRR, MIRR, and payback as you did previously. (Note that sensitivity analysis involves making a
change to only one variable to see how it impacts other variables.) Then, you would go back
and repeat the same steps for 80,000 units--this would be done for each of the sales unit values.
Then, you would repeat the same procedure for the sensitivity analysis on salvage value and on
cost of capital. (Note that for the cost of capital analysis, the free cash flows would remain the
same, but the cost of capital used in the NPV and MIRR calculations would be different.)
Excel is ideally suited for sensitivity analysis. Using data tables, the sensitivity analysis is simple.
	SENSITIVITY ANALYSIS DATA TABLES
		Unit Sales			Salvage Value		WACC
-0.3	70,000			17,500		7.0%
-0.2	80,000			20,000		8.0%
-0.1	90,000			22,500		9.0%
0.0	100,000			25,000		10.0%
0.1	110,000			27,500		11.0%
0.2	120,000			30,000		12.0%
0.3	130,000			32,500		13.0%