Background: Sensitivity Analysis

The summary narrative $($i$.$e$.,$ Word document$)$ should include a sensitivity analysis showing

the impact of changes in $1$ or $2$ key variables. The sensitivity analysis should be presented

in a table format, and the scenario used for Schedule $1$ should be highlighted $($see below for

examples$).$ It should always show the impact on NPV$.$ The impact on IRR can also be

included at the preparer's discretion; however, it should not be included if the resulting IRR is

negative for any of the scenarios included in the table.

Potential variables to consider when creating the sensitivity analysis:

Changes to Cost of Goods Sold $($Gross Profit Margin$)$

Sales Growth $($Operating Capacity$)$

Timing of Equipment Needs

FTEs needed

Capital Outlay $($Variations from design and initial contractor quotes$)$

Ramp Up Period

Examples

Net Present Value Sensitivity Analysis

Net Present Value Sensitivity Analysis

Based on Changes in Incremental FTE Operational Forecast

$($$ thousands$)$

The Desk Corp Fincial Impact of Expansion

Springfield Plant

Facility Expansion Present Value Payback Period

The present value payback period is the length of time required for the present value of future cash flows from the project to recover the cash outlays.

CAUTION: If columns are added$/$removed on Schedule $1,$ it may impact the links used below. Either verify the links are still correct or override them by manually entering the cash flows.

You are the manager of the profitable arm of Desk Corp and have been approached by a neighboring land owner to purchase adjacent land that could house an expansion attached to the existing factory. You have been wanting to expand capacity but didn't want to have a second location to manage so the location and opportunity couldn't be better from an expansion standpoint. The land could be acquired for $$4,000,000$ and our accounting counsel has advised us to analze the project depreciating the land costs over $30$ years. The building structure and associated start$-$up costs of increasing the capacity into the new space along with a remodeling of the existing building to accommodate the expansion will cost an estimated $$16,000,000$ with an approved useful life of $20$ years. Lastly, there will be $$5,000,000$ of new equipment needed to be installed in order to complete the project and expand capacity. It's useful life is $5$ years and will need to be purchased again for an estimated $$6,000,000$ after $5$ years but we anticipate this new equipment to be tradeed in with a value of $$1,000,000$ at that time. From an operations standpoint, we expect the facility to operate at $80\%$ capacity which would generate $$8,000,000$ of annual revenue. The cost of goods sold will be expected to remain at our existing $30\%$ of sales revenue while manufacturing and labor costs will begin at $$1,40,000$ and grow by $5\%$ per year. Likewise, administrative costs associated with the expansion will begin at $$100,000$ and are expected to grow at $10\%$ per year. The Table Corp is able to borrow from a local bank at a fixed rate of $7\mathrm{.}5\%$ for both long and short$-$term loans but has also had an offer from one of the owners of the compnay to fund the project at the same $7\mathrm{.}5\%$ rate. For this analysis, do not consider interest costs as an increased expense to the operations. Based on the current staffing of the existing operations, there are serveral new hires that will be needed to staff the expansion including Table builders $7\mathrm{.}0$ FTE, Management $2\mathrm{.}0$ FTE and Maintenance $0\mathrm{.}5$FTE.

Assignment:

Calculate the annual incremental impact of the proposed project. Include capital outlays in row $9($shown as negative numbers to show an outlay of cash$)$ and revenue and expenses as positive numbers. Because the expansion will require new, external hires to be brought in$,$ there is some uncertainty on how many FTE will be needed to staff the facility. Run a sensitivity analysis on what levels of staffing will do to the outcome of the proposal. Operating at well over $90\%$ is common in the current facility but there's a concern that the facility might not be able to reach that levels unless the sales team can increase its sales quickly. However, there is optimism that the new facility could reach that level early on$.$ Run a sensitivity analysis on what varying levels of utilization of the new facility could do to the return on investment. Use the Sensitivity tab as a guide to better understand how these variables can be shared. Most importantly, use the baseline assumptions to calculate IRR, NPV and the Present Value Payback Period of the proposal as defined above over the $10$ year period defined in the template provided. Lastly, write up a business plan proposal of your findings. Show the Schedule $1$A to support your recommendations. List out the key variables in the report that could drive up or down the IRR and the sensitivity analysis to support your recommendations.