Case Background Information

Payant Pickled Peppers, Inc. is evaluating their current vegetable operations and considering the construction of a new plant in Abbeville, SC$.$ The new plant can produce $2,000$ pounds of canned peppers each hour, $24$ hours per day, $365$ days per year, for the next $15$ years. Land in Abbeville suitable for the plant will cost approximately $\backslash (\backslash$$ $2,500,000\backslash ).$ Construction of the physical plant building will cost approximately $\backslash (\backslash$$ $18,500,000\backslash ),$ and the manufacturing equipment will require an additional $\backslash (\backslash$$ $10,000,000\backslash ).$

The initial investment in net working capital necessary to have the plant operating at full capacity will include $\backslash (\backslash$$ $1,000,000\backslash )$ in cash, $\backslash (\backslash$$ $4,800,000\backslash )$ in receivables, and $\backslash (\backslash$$ $3,000,000\backslash )$ in inventory, which will be partially funded by $\backslash (\backslash$$ $1,600,000\backslash )$ of supplier financing. This initial net working capital will represent the only net working capital investment for the life of the project. Assume that the net working capital will be recaptured at the end of the project.

The vegetables processed in Abbeville will sell in year $\backslash (=1\backslash )$ for $\backslash (\backslash$$ $2\mathrm{.}50\backslash )$ per pound. This selling price per pound is expected to increase by $\backslash (0\mathrm{.}25\backslash \%\backslash )$ per year throughout the life of the project.

Operating expense has two components: $1)$ a fixed component, and $2)$ a variable component. The fixed overhead expenses will equal $\backslash (\backslash$$ $10,000,000\backslash )$ the first year. These fixed operating expenses will increase each year by $\backslash (2\mathrm{.}50\backslash \%\backslash )$ throughout the life of the project. The variable operating expenses are equal to $\backslash (\backslash$$ $1\mathrm{.}25\backslash )$ per pound produced. These variable operating expenses are expected to increase by $\backslash (2\mathrm{.}50\backslash \%\backslash )$ per year.

The plant building will be depreciated straight$-$line on a $39-$year schedule. The equipment within the plant will be depreciated MACRS according to the $7-$year schedule $($appropriate MACRS$-7$ percentages are provided within the spreadsheet template$).$

The original manufacturing equipment will need to be replaced at the end of year $8.$ The original manufacturing equipment can be sold for $400,000$ on the last day of year $\backslash (=8\backslash ),$ and this equipment will be replaced on the first day of year $\backslash (=9\backslash )$ for $\backslash (\backslash$$ $10,000,000\backslash ).$ The replacement equipment will also be depreciated according to MACRS$-7.$

The Abbeville plant can be sold at the end of the life of the project. Assume that the land portion of this sale $($pre$-$tax$)$ will reflect an annual growth rate in the land value of $\backslash (0\mathrm{.}50\backslash \%\backslash ).$ Assume that the building portion of this sale $($pre$-$tax$)$ will reflect an annual growth rate in the building value of $\backslash (-3\mathrm{.}50\backslash \%\backslash )($negative $3\mathrm{.}5$ percent per year$).$ Assume that the equipment portion of this sale $($pre$-$tax$)$ will be $\backslash (\backslash$$ $500,000\backslash ).$

For analysis purposes, the firm uses $\backslash (22\mathrm{.}5\backslash \%\backslash )$ for their marginal tax rate for operating income. They assume a tax rate of $\backslash (15\mathrm{.}0\backslash \%\backslash )$ for capital gains. They use a discount rate of $\backslash (15\mathrm{.}0\backslash \%\backslash )$ for capital budgeting purposes. $1.$ Calculate the net investment required at time $\backslash (=0\backslash )$ to pursue this project in Abbeville?

$2.$ Calculate the estimated net cash flow from the project for year $\backslash (=1\backslash )?$

$3.$ Use the spreadsheet to estimate the net cash flows throughout the life of the project.

$4.$ Calculate the NPV of the project.

$5.$ Calculate the IRR of the project.

$6.$ Calculate the MIRR of the project, assuming that the financing and reinvestment rates are both equal to the project's discount rate.

$7.$ Calculate the Profitability Index of the project.

$8.$ Calculate the project's Payback Period and Discounted Payback Period.

$9.$ Perform a sensitivity analysis to find the following variable outcomes$/$assumptions required for a break$-$even NPV$.$ Please use the separate worksheets for these sensitivities$.$ Finish your baseline analysis, then copy the whole baseline analysis worksheet onto the sensitivity analysis worksheets. Then use each sensitivity sheet for the respective sensitivity analyses $($a$-$d$)$ below.

a$.$ Pounds of vegetables produced per hour $($worksheet $"$SA$\_$A $($pounds produced$)",$ cell B$14)$

b$.$ Initial sale price of produced vegetables per pound $($worksheet $"$SA$\_$B $($price$)",$ cell B$18)$

c$.$ Growth rate of selling price per pound per year $($worksheet $"$SA$\_$C $($price growth$)",$ cell B$19)$

d$.$ Growth rate of variable operating expenses $($worksheet $"$SA$\_$D $($var delta$-$O growth$)",$ cell B$23)$

$10.$ Now perform a scenario analysis. The scenario to consider here should be based on assumptions that are half$-$way between the base$-$case assumptions and the break$-$even assumptions you found in $\backslash$#$9$ above for all sensitivities$.$ In other words, this scenario will alter the base$-$case NPV assumptions for pounds produced per hour, initial sale price, growth rate of selling price per pound per year, and growth rate of variable operating expenses per pound per year to the respective half$-$way points between base$-$case and break$-$even. For example, if the pounds of vegetables produced per hour break$-$even $($found in $\backslash$#$9$a above$)$ is $1,800$ pounds per hour, then the assumption for the scenario is $\backslash ((2,000+1,800)/2=1,900\backslash )$ pounds per hour.