Business
of Potatoes NPV Spreadsheet Page
NPV Analysis of Boone Brothers’
North
Dakota Expansion
You have been asked to conduct a NPV analysis of the Boone
Brothers’ North Dakota expansion. Your task is to
complete an initial analysis and a sensitivity test of the
analysis. If you have not read the Boone
Brothers’ Farms, LLC case, then do so at this
time.
The assumptions made by Boone Brothers’ management
about production, prices, expenses and cost of capital associated
with expanding potato production to North Dakota. The spreadsheet
tool for analyzing the NPV can be downloaded. On it
you will find the Boone Brothers’ initial assumptions.
You may alter these to create your own base case, then compare
scenarios through the sensitivity analysis. The following
instructions guide you through the process.
Phase I: Build the NPV
1) The front page is the main page of the NPV analysis
tool. The buttons you see are used to navigate through the
analysis tool. There are two parts to the tool, an initial
analysis phase (in the blue area) and a sensitivity analysis
phase (the green area) used for comparisons. We will start
with the initial analysis.
2) Click “Initial Assumptions” to take you
to a table that looks like the table attached to this document.
Here you should type the numbers for a base case. Numbers
are already in place – you should alter these as you
see appropriate.
3) After typing the numbers for the “Initial Assumptions”
table you can click on “Income Analysis” to
look at the resulting income and expenses for the farm given
the assumptions you just typed in (plus some that were already
typed in for you).
4) Hit the “Return to Main Menu” button to
return to the main page. From the main page, click the “Cost
of Capital” button. Alter these numbers to produce
your own cost of capital assumptions in the yellow cells
of the spreadsheet. Once you have finished, note the weighted
average cost of capital computed at the bottom of the sheet
and then hit the “Return to Main Menu” button.
5) At this point you have finished inputting all of the
necessary data to conduct a NPV analysis. Click on the “Initial
NPV Analysis” button to go to the form that computes
the NPV for the initial assumptions. You will note that
the calculations in this sheet result in the NPV that is
shown to the right of the buttons on the main sheet in the
blue section. Click on the “Return to Main Menu”
button to return to the main page.
6) To check the financial feasibility of the expansion
click on the “Initial Feasibility Analysis”
button. This will take you to the form that does the calculations
for financial feasibility. Have your group note the results
on this page and discuss any potential positives or negatives.
Click on “Return to Main Menu.”
Phase II: Sensitivity Analysis
7) Now that you have completed the initial analysis, you
are ready to move to Phase II. In Phase II your group should
think carefully about the analysis of the farm expansion
and “stress test” the NPV. Ultimately, your
group should determine what the critical assumptions are
that make this investment profitable.
8) Click on the “Sensitivity Analysis Inputs”
button located on the left side of the green section on
the main page. Here you will see a form that allows you
to put in an alternative set of numbers for the expansion
to see the impacts of the assumptions on the final outcomes
for the NPV and feasibility analysis. The first set of inputs
located in the top left of the form are the same as the
initial assumptions. You may either put in the same numbers
as were in the initial inputs or change them if you prefer.
9) The second section of the “Sensitivity Analysis
Inputs” lists a set of key forecast variables in the
analysis. The numbers are entered as percents of the original
set of inputs. So, 100% for the given variable in the given
year means that the input would remain the same in your
scenario. If you wanted a lower number, you would put in,
say 90%, to lower the number by 10 percent. You could also
increase a number by putting in something greater than 100%.
Any percentage you put in results in a new input value which
is shown in the table just below the percentage inputs.
You can decide to enter whatever you choose in these inputs.
However, you should be prepared to explain your rationale
for the numbers you chose.
10) By clicking on the “Income Analysis” button
at the top of the “Sensitivity Assumptions”
page you can view the new income and expense projects for
the dairy based on your scenario analysis. Once completed
you can click on the “Return to Main Menu” button.
11) The NPV to the right of the buttons in the green area
is the NPV of your scenario. You can see how this was calculated
by clicking on “Sensitivity NPV Analysis.”
12) You can also see the feasibility results of your scenario
analysis by clicking on “Sensitivity Feasibility Analysis”
button from the main page.
13) Repeat the sensitivity analysis if you wish.
14) Prepare your team’s overhead for discussion.
|
