HW7
TIM-125/225
Management of Technology II: Supply Chain Management
“Excel Tutorial” Last modified:01/18/2012
written by:
R.Polany and S.Desa
UC Santa Cruz, Baskin School of Engineering
Table of Contents Purpose ..................................................................................................................................... 1
Context ...................................................................................................................................... 1
Important Note for Mac users ..................................................................................................... 1
Tutorial platform ..................................................................................................................... 1
Solver Add-In .......................................................................................................................... 1
Problem Definition ...................................................................................................................... 2
Treatment .................................................................................................................................. 3
Execution: Pre-Processing (Setup) ............................................................................................ 4
Step 0: .................................................................................................................................... 4
Execution: Processing ................................................................................................................ 5
Step 1: Build a ScenarioParameters worksheet ...................................................................... 5
Step 2: Build the BaseCase NPV analysis worksheet ............................................................. 6
Step 3: Create the +10% DevCost Worksheet ........................................................................ 8
Step 4: Create the -10% DevCost Workshet ..........................................................................10
Step 5: Create the +20% DevCost Workshet .........................................................................11
Step 6: Create the -20% DevCost Worksheet ........................................................................12
Step 7: Create the +30% DevCost Worksheet .......................................................................13
Step 8: Create the -30% DevCost Worksheet ........................................................................14
Step 9: Create the DevelopmentCost_Sensitivity summary worksheet ..................................15
Step 10: Worksheet Tab Review ...........................................................................................16
Step 11: Graph the Change in NPV ($) vs. Change in DevCost (%) ......................................17
Step 12: Create the +10% SalesVol Worksheet.....................................................................21
Step 13: Create the -10% SalesVol Worksheet .....................................................................23
Step 14: Create the +20% SalesVol Worksheet.....................................................................24
Step 15: Create the -20% SalesVol Worksheet .....................................................................25
Step 16: Create the +30% SalesVol Worksheet.....................................................................26
Step 17: Create the -30% SalesVol Worksheet .....................................................................27
Step 18: Create the SalesVolume_Sensitivity summary worksheet .......................................28
Step 19: Graph the Change in NPV ($) vs. Change in Sales Volume (%) ..............................29
Step 20: Using Solver to Determine Max Development Cost .................................................30
Results: Post-Processing ..........................................................................................................36
Step 21: The Trade-off for Change in NPV ($) vs. Change in DevCost (%) ...........................36
Step 22: The Trade-off for Change in NPV ($) vs. Change in Sales Volumes (%) .................37
Step 23: Determine Maximum Development Cost with Solver ...............................................38
Step 24: Relationship of Development Cost to Sales Volume ................................................39
Conclusions ..............................................................................................................................40
Works Cited ..............................................................................................................................41
Page 1
Purpose
In this tutorial, you will develop intermediate-level Excel skills, as follows:
Pre-processing
1. Setting Up Excel Workbooks
a. Linking worksheets
b. Writing formulas
c. Formatting Spreadsheets
Software
Automation
Rapid
Problem
Solving
Processing
2. Exploring Scenarios
a. Entering parameters
b. Adjusting parameters
c. Performing sensitivity
analysis,
d. Optimization
e. Simulation
f. Summarizing results
Post-
Processing
3. Presenting Results
a. Graphing
b. Generating reports
c. Exporting content
d. Drawing conclusions
Figure 1: Structured Approach to Rapid Problem Solving with Software Automation This tutorial will walk you through the Pre-Processing, Processing, and Post-Processing
approach to developing software automation utilizing Microsoft Excel. The lessons you learn
will teach you how to solve new problems rapidly, and, efficiently. You will then apply this new
knowledge to solving Supply Chain problems in the coursework homework, exams, and project.
Important Note:
The skills identified above are essential for the subsequent work you will be performing in the
TIM-125/225 coursework. We recommend you invest the time now and develop your skills as
the coursework ramps up quickly. This tutorial can also help you identify your strengths and
weaknesses using Excel, so that you can get the help you need and stay current with the
coursework. You will be in very good shape for the course, once you can 1) Duplicate the
results of this tutorial, 2) Achieve equal to or better quality and 3) Understand the process.
Page 1
Context
In TIM-105/205 (1), you were taught how to create and implement a quantitative financial model
(i.e., NPV Analysis) for product development and commercialization (2). We will use this
context1 to develop your Excel skills as listed above.
Important Note for Mac users
Tutorial platform
This tutorial is currently written for use on the Microsoft Windows operating system, loaded with
Microsoft Excel 2010. So please beware: Some of the Graphic User Interface features are
slightly different in a Mac environment.
Solver Add-In
As part of this tutorial, we walk you through how to use the Solver Add-In to Excel and there are
issues with compatibility of this Add-In and the Mac version of Excel.
Therefore, please first read “Getting Started with Excel” on the TIM-125/225 course webpage
before working through this tutorial and specifically attempting the portion with the Solver Add-
In. You will need to make sure that your version of the Microsoft Excel is capable of performing
the prescribed computations in Excel, and can utilize the Solver Add-in.
If you find that your version of Excel does not support the Solver Add-In, two possible
resolutions are:
1) Work on a UCSC SOE Windows computer with Excel 2010, with the Solver Add-In
enabled, in one of the labs.
2) Update to the Mac Excel 2011 with Service Pack 1 software:
a. Mac Excel 2011: Office for Mac 2011 Service Pack 1 (14.1.0) needed.
Download Service Pack: http://support.microsoft.com/kb/2525412
b. Mac Excel 2008: http://www.solver.com/mac/dwnmacsolver.htm
1 The NPV Analysis is adopted from Ulrich, K. T., and Eppinger, S.D., Product Design and Development, Fourth
Edition, McGraw-Hill, 2008. Chapter 15.
Page 2
Problem Definition
Use the following Base Case scenario parameters:
(a) The total project length is four (4) years.
(b) The total Development Cost is $25,000,000.
(c) The average sales price (wholesale) is $2,500 per unit.
(d) The average production cost is $1,250 per unit.
(e) The total Ramp-up costs are estimated at $2,500,000.
(f) Ongoing market and support costs are $200,000 per month.
(g) Development time is 12 months.
(h) Production ramp-up time is 6 months.
(i) Ramp-up starts 9 months after the start of product development and continues for 6
months.
(j) Ongoing “market and support" starts one quarter before Production (of the product) and
selling (“sales” of the product ) start.
(k) Production (of the product) and selling (“sales” of the product) occur immediately after
the end of the ramp-up period, and concludes at the end of year 4 (four).
(l) Assume that you could sell 35,000 units per year.
(m) The annual discount factor is 10% (i.e., 2.5% per quarter).
Answer the following questions:
(1) What is the NPV of the Base Case scenario?
(2) What is the maximum development cost beyond which the development of the product cannot be
justified? (i.e., what is the development cost which makes NPV=0). Use the Solver Add-In to
answer this question. Show your work.
(3) Explain the trade-off law for NPV versus development cost.
(4) Explain the trade-off law for NPV versus sales volume.
(5) Create a graph of the trade-off law relationship for the (Change in NPV, $) (y-axis) versus
(Change in Development Cost, %) (x-axis). What is the equation of the Regressed trendline?
Give the answer in the standard form for an equation of a line: y=mx+b.
(6) Create a graph of the Trade-off law relationship for the to the (Change in NPV, $) (y-axis) versus
(Change in Sales Volume, %) (x-axis). What is the equation of the Regressed trendline?
Give the answer in the standard form for an equation of a line: y=mx+b.
(7) If there is a 10% increase in development cost, by how much does the sales volume need to
increase, to compensate for the drop in NPV?
Page 3
Treatment
1. Build a Base Case NPV Scenario
2. Use the Base Case to create: +/- 10%, +/- 20%, +/- 30% Development Cost analysis
3. Use the Base Case to create: +/- 10%, +/- 20%, +/- 30% Sales Volume analysis
4. Create a worksheet with summary tables
5. Graph the results to determine the slope of the line using Regression Analysis trendline.
6. Use the Solver Add-In to automate the determination of the Maximum development cost
for the project at which the project is no longer economically practical (i.e., NPV=0).
7. Follow Ulrich’s (2) method (p.320) to determine the impact of 10% increase in
development cost on NPV and the sales volume needed to compensate for the drop.
8. Check the work.
9. Copy and Paste content into MS Word.
10. Label each Figure (i.e., Figure #: Title) and Table (i.e., Table #: Title).
11. Explain each Figure and Table.
12. Explain the Results.
13. Discuss the Conclusions.
Important Notes
As you work through this tutorial, it is essential that:
1) Worksheet tab names are formatted exactly as presented in the tutorial, matching letter for
letter. This is important because we will be using linked worksheets that will reference the
precise spelling of the tabs. A deviation from the exact spelling will cause the worksheets
links to fail. Therefore, pay attention to UPPERCase and lower_case, and spacing, as it
pertains to the formatting of the tab names. We used a consistent nomenclature, therefore
you need to adhere to the format we used. You can change it later (and improve it) once
you understand how it works; and
2) The numerical scenario data values and formulas must entered in each corresponding cell
position, exactly as presented. If the tutorial defines to enter something in a Cell B1 then
enter that specific information in Cell B1; otherwise the links will fail, in relation to the rest of
this tutorial. We use the Fixed Cell References “$” and will discuss this symbol latter; and
3) Save your work often. A simple approach is to email the file to yourself so you have an
easily accessible copy in case your computer crashes, that you can get from another
computer to continue working to meet deadlines.
Page 4
Execution: Pre-Processing (Setup)
Step 0:
Below is a checklist for you to make sure you have everything you need before you get started:
1. Make sure that Microsoft Excel is loaded on your computer.
2. Enable the “Solver Add-In” with Excel
Windows OS instructions:
Useful video guide:
http://office.microsoft.com/en-us/excel-help/video-install-and-activate-the-analysis- toolpak-and-solver-VA101956375.aspx?CTT=1
Excel 2010: http://office.microsoft.com/en-us/excel-help/load-the-solver-add-in-HP010342660.aspx?CTT=1
Excel 2007: http://office.microsoft.com/en-us/excel-help/load-the-solver-add-in-HP010021570.aspx?CTT=1
Excel 2003: http://office.microsoft.com/en-us/excel-help/load-the-solver-add-in-HP001127725.aspx?CTT=1
Mac OS instructions:
Excel 2011: Office for Mac 2011 Service Pack 1 (14.1.0) needed.
Download Service Pack: http://support.microsoft.com/kb/2525412
Excel 2008: http://www.solver.com/mac/dwnmacsolver.htm
3. Enable the “Excel Analysis ToolPak” and “Analysis Toolpak – VBA” within Excel.
Windows OS instructions:
Excel 2010: http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP010021569.aspx?CTT=3
Excel 2007: http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP010021569.aspx
Excel 2003: http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP001127724.aspx
Mac OS instructions:
Excel 2011: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17198
Excel 2008: Not Available. VBA is not Supported.
4. Recommend to print a hard-copy of this Tutorial (double-sided).
5. Have a copy of your TIM-105/205 Lecture Notes on NPV Analysis.
6. Have textbook: Ulrich, K. T., and Eppinger, S.D., Product Design and Development, Fourth Edition, McGraw-Hill, 2008, Chapter 15.
Page 5
Execution: Processing
Step 1: Build a ScenarioParameters worksheet
Create a worksheet and name the worksheet Tab: ScenarioParameters
The worksheet is formatted as follows with the parameters:
Figure 1: ScenarioParameters worksheet format
Cell A1 and Cell B1 have been MERGED. Then the SCENARIO INPUT PARAMETERS typed
inside this single merged cell.
The project schedule, from inception through end of the production and selling will look like
Figure 2.
Figure 2: Complete Project Time Line from Inception to End
Observe how, that the production and selling concludes at the “end of year four (4)”, as defined
in the problem statement. But, given the other events that occur beforehand, the production and
selling does not start until Year 2-Q2.
Page 6
Step 2: Build the BaseCase NPV analysis worksheet
Now we show you how to build and setup the BaseCase scenario to look as follows:
Figure 3: Base Case NPV Analysis
Note: The clean looking format for the number values is utilizing a format configuration of:
NUMBER, Decimal Places=0 with 1000 Separator (,) and format -1,234. You can achieve this
format by selecting all the cells. Then in the Number ribbon (Figure 4), click the Show Number
tab icon, , and configure the number format (Figure 5). Rows 11 and Row 14 are configured
to Decimal places = 2.
Figure 4
Figure 5
Note: We use the symbol “$” on purpose and it is very important. It is called a Fixed Cell Reference.
Putting the “$” sign before the column or row variable, causes the link to remain fixed to that cell
reference. “One of the major ways to control a cell reference is to set either the column or row reference
or both references. This is typically done by using a dollar sign ("$") before the column or row reference
(e.g., "$A4", "A$4", or "$A$4"). The dollar sign indicates that the reference will not change, even if the
formula is copied and pasted.” 2 This is useful when you need to reference a specific input parameter but
want to copy and paste formulae. Using the ("$") saves time in programming the worksheets and ensures
accuracy.
2 http://www.sjsu.edu/depts/geography/resource/swdocs/xl-guide/edit/celref.htm
Click here to show the
Format Cells:Number
options.
Page 7
The programming format to create the BaseCase worksheet is as follows:
If you have programmed the BaseCase worksheet as defined above, and made the correct
links to the ScenarioParameters worksheet, then the Project NPV, $ will equal, precisely,
$62,166.18; rounded to 0 decimal places, it is $62,166. Recall this value is in thousands.
Therefore, the conclusion is that the project has a high NPV value of $62,166K and should be
pursued.
CELL CELL FORMULAE COPY TO
B6
=(-1)*ScenarioParameters!$B$3/4*(1/1000)
Note: Dividing by (1/1000) converts the dollar value from Millions to Thousands. We do
this to simply the presentation of the spreadsheet.
C6:E6
E7 =(-1)*ScenarioParameters!$B$6/2*(1/1000) F7
F8 =(-1)*ScenarioParameters!$B$7/4*(1/1000) G8:Q8
G9 =(-1)*(ScenarioParameters!$B$5)*(ScenarioParameters!$B$2/4)*(1/1000) H9:Q9
G10 =ScenarioParameters!$B$2/4 H10:Q10
G11 =G9/G10 H11:Q11
G12 =(ScenarioParameters!$B$4*ScenarioParameters!$B$2/4)*(1/1000) H12:Q12
G13 =ScenarioParameters!$B$2/4
Note: Dividing by 4 converts from Yearly to Quarterly H13:Q13
G14 =G12/G13 H14:Q14
B16 =SUM(B6+B7+B8+B9+B12) C16:Q16
B17 =B16
C17
=C16/(1+(ScenarioParameters!$B$8/100)/4)^B3
Note: ScenarioParameters!$B$8/100 converts the parameter of 10 to 10%.
Then, division of by 4 converts from Yearly to Quarterly value as needed.
Therefore, you get 1+(.025)=1.025 which is the needed value. Lastly, the cell B3 is
shifted one to the left for the periods, so we start at one (1) for the exponential factor.
This setup allows to quickly change the Discount Factor in the ScenarioParameters,
worksheet to automatically update the entire workbook.
D17:Q17
B19 =SUM(B17:Q17)
Page 8
Step 3: Create the +10% DevCost Worksheet
To quickly create a worksheet that is derived on the BaseCase worksheet, we will use the
worksheet COPY feature of the BaseCase worksheet and then make a few small changes.
To copy the worksheet, you will RIGHT-CLICK on the Tab of worksheet BaseCase.
Then select “MOVE OR COPY…”
Figure 6: Right Click to Copy a Worksheet
Select the BaseCase worksheet, and Check the box to CREATE A COPY.
Figure 7: Create a Worksheet Copy
Also, select MOVE TO END so the new worksheet will be positioned at the end of the group of
worksheets. The Tan bame will be called BaseCase (2). So rename the worksheet by double
clicking on the Tab, and typing a tab name of: +10% DevCost.
The name must be entered precisely to match this tutorial as we will link to this Tab name later.
Then hit enter to save the Tab name. You can also right click on the Tab and select a color to
help you identify the sheets. You can see that in we colored ours a light blue,
Suggested Reference:
Watch a short and very useful video on YOUTUBE to show you the process of how to:
Move or Copy a Worksheet (3)
http://www.youtube.com/watch?v=OucSxFiLyBc
Page 9
Once you have the +10% DevCost worksheet created. It is quick to edit the worksheet
parameters as follows:
The tab name to enter is: 10% DevCost
1. Cell A1, change to: Development Cost Analysis 2. Cell B1: change to: 10%
3. Cell B6: change to: =(-1)*(ScenarioParameters!$B$3/4*(1/1000))*(1+$B$1) a. Note: Multiplying by (-1) to create the negative effect of cost. b. Note: Dividing ScenarioParameters!$B$3 by 4 converts from Yearly to Quarter. c. Note: Multiplying ScenarioParameters!$B$3 by (1/1000) converts from Millions to
Thousands. d. Note: Multiplying by (1+$B$1), in which $B$1 is the 10% for sensitivity value. Also, notice
the use of “$” before the Column ‘B’ and Row ‘1’. Putting the $ sign before the column and row, causes the link to remain fixed to that cell reference. It called Fixed Cell References. “One of the major ways to control a cell reference is to set either the column or row reference or both references. This is typically done by using a dollar sign ("$") before the column or row reference (e.g., "$A4", "A$4", or "$A$4"). The dollar sign indicates that the reference will not change, even if the formula is copied and pasted.” (4)
4. Copy B6 to C6:E6.
Figure 8: +10% DevCost Analysis
If you have followed this process correctly, then the result is a Project NPV of $59,756K for the
10% DevCost worksheet, as shown in Figure 8.
Page 10
Step 4: Create the -10% DevCost Workshet
Create a copy of the 10% DevCost worksheet.
The tab name to enter is: -10% DevCost
Once you have the -10% DevCost worksheet created, it is quick to edit the Worksheet
parameters as follows:
1. Cell B1: Change to -10%
Figure 9: -10% DevCost NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $64,576K for the
-10% DevCost worksheet, as shown in Figure 9.
Page 11
Step 5: Create the +20% DevCost Workshet
Create a copy of the -10% DevCost worksheet.
The tab name to enter is: 20% DevCost
Once you have the 20% DevCost worksheet created, it is quick to edit the Worksheet
parameters as follows:
1. Cell B1: Change to 20%
Figure 10: +20% DevCost NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $57,346K for the
20% DevCost worksheet, as shown in Figure 10.
Page 12
Step 6: Create the -20% DevCost Worksheet
Repeat a copy of the 20% DevCost worksheet.
The tab name to enter is: -20% DevCost
Once you have the -20% DevCost worksheet created, it is quick to edit the worksheet
parameters as follows:
1. Cell B1: Change to -20%
Figure 11: -20% DevCost NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $66,986K for the
-20% DevCost worksheet, as shown in Figure 11.
Page 13
Step 7: Create the +30% DevCost Worksheet
Create a copy of the -20% DevCost worksheet.
The tab name to enter is: 30% DevCost
Once you have the 30% DevCost worksheet created, it is quick to edit the worksheet
parameters as follows:
1. Cell B1: Change to 30%
Figure 12: 30% DevCost NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $54,936K for the
30% DevCost worksheet, as shown in Figure 12.
Page 14
Step 8: Create the -30% DevCost Worksheet
Create a copy of the 30% DevCost worksheet.
The tab name to enter is: -30% DevCost
Once you have the -30% DevCost worksheet created, it is quick to edit the worksheet
parameters as follows:
1. Cell B1: Change to -30%
Figure 13: -30% DevCost NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $69,396K for the
-30% DevCost worksheet, as show in Figure 13.
Page 15
Step 9: Create the DevelopmentCost_Sensitivity summary worksheet
Now we want to consolidate all the results into one worksheet, to rapidly discern the results.
To do this, Insert a new, blank worksheet. This is done under the HOME->INSERT menu
option.(or you use the Shift+F11 shortcut).
Figure 14: Insert Sheet (Shift+F11)
Name the Tab: DevelopmentCost_Sensitivity
Format and program the worksheet as follows:
Figure 15: Cell Programming for the DevelopmentCost_Sensitivity worksheet
You will notice that the text in Cell A1 is formatted into in three lines, within one Cell. This is
done using the ALT-ENTER command to create a Line Break, within the Cell. Type “Change
In”, then press ALT-ENTER on the keyboard, type “Development”, then press ALT-ENTER
again, type “Cost,%”, then press ENTER on the keyboard. This process will create the three line
break, within one Cell. Then you can Center the text using the built-in alignment tool .
Page 16
Figure 16: Format of the DevelopmentCost_Sensitivity Worksheet
In Figure 16, is shown the final output format for the DevelopmentCost_Sensitivity worksheet.
These results are now fully linked to all the worksheets, which are connected to the
ScenarioParameters worksheet. Now, if you change one parameter in the scenario, the
Sensitivity Analysis is instantly updated for you. You are now capable of rapidly solving new
problems!
Step 10: Worksheet Tab Review
You can make sure your worksheet tab names look as follows. You can drag and drop to move
them to different positions along the horizontal workbook axis.
Page 17
Step 11: Graph the Change in NPV ($) vs. Change in DevCost (%)
Next, you want to graph the relationship between the (Chain in NPV, $ Thousand)(y-axis) vs.
(Change in Development Cost, %)(x-axis).
To do this, you Insert a Chart. You can select the Scatter with Smooth Lines and Markers.
Figure 17: Chart Format Option
Next, Select the Data.
Figure 18: Select the Data
Then ADD a data series:
Figure 19: Add a Data Series
Page 18
Configure the Series as follows, in which you type the text into the white box. If you see “={1}” in
the Series Y values white space, delete it. Then enter the information below
Figure 20: Edit Data Series X and Y axis
SERIES NAME: Change in NPV ($) vs Change in Dev Cost (%)
SERIES X VALUES: =DevelopmentCost_Sensitivity!$B$2:$B$8
SERIES Y VALUES: =DevelopmentCost_Sensitivity!$G$2:$G$8
Lastly, using the LAYOUT Ribbon, you will find all the options to Label each axis and change
the units of the graph along with other visual tools
Figure 21: Layout Ribbon Tool to Label Charts
The end result, should look similar to the following chart in Figure 22:
Figure 22: Change in NPV ($) vs. Change in DevCost (%)
Page 19
One last useful technique is to determine the slope of the line. There is a useful tool in Excel
called Trendline. You can search to learn more about Excel Trendline.
Suggested Reference:
Watch a short and very useful video on YOUTUBE to show you the process of how to:
Use Trendline and Regression Analysis (5):
http://www.youtube.com/watch?v=6rOlGbLeQxI
The shortcut approach is to right-click on the line, and selects the ADD TRENDLINE.
Figure 23: Add Trendline Shortcut (right-click)
Then format the Trendline as follows:
Figure 24: Trendline Configuration
For this data series, we set the Regression
Line to Linear, because we are assuming
linearity in the data with the Base Case
crossing at x=0 and y=0, set Intercept to
equal 0. Restated, we are including the
BaseCase, which crosses both the X and Y
axis at exactly zero and therefore, zero will
be the Intercept of the equation.
Also, the important feature here is to check
the box to: Display Equation on Chart. This
will automate the regression analysis and put
the equation of the line on the chart.
Page 20
The new Chart will look as show in Figure 25. Nnotice a thin black line was created inside the
original line and the equation of the line is now visible:
Figure 25: Change in NPV ($) vs. Change in DevCost (%)
The equation in this example, in y=mx+b format, is . In which m=-24100 and
b=0. That translates to a trade-off law: For each 10% increase (x-axis value) in development
cost, there is a $2410 (in thousands) reduction in NPV.
To format the textbox containing the equation, like in Figure 25, click on the floating textbox that
contains the equation, and then change the Font Color and Font Size and Fill the box with a
color, all to help it stand out. In this example, the font has been increased to size 18, colored
Red, and filled with a White background to make it easier to read against the grid lines. You can
also move the textbox and change its location, as necessary.
Figure 26: “Paste Special” to Insert Graphics
You can copy the Chart in Excel, then open an MS Word
document and then can use the “Paste Special” option to insert
as a image (JPEG, BITMAP, or PNG) to ensure it is inserted
uniformly into the document. The format of BITMAP is the least
compressed and therefore will increase the size of your
document the most. However, “what you see it what you get”, so
is a safe option until you are comfortable adjusting the graphic
formats that minimize the file size and maximize readability.
When you paste images into MS Word, you can automate the process of the sequential
numbering and labeling of the Figures/Table using Insert Captions.
Useful Instructions Adding/Deleting Captions for Figures & Tables: http://office.microsoft.com/en-us/word-help/add-or-delete-captions-HP001228982.aspx
Page 21
Step 12: Create the +10% SalesVol Worksheet
Create a copy of the BaseCase worksheet.
The Tab name to enter is: 10% SalesVol
Once you have the 10% SalesVol worksheet created, it is quick to edit the worksheet
parameters as follows:
1. Cell A1, change to Sales Volume Analysis
2. Cell B1: change to 10%
3. Cell G9: change to: =(-1)*(ScenarioParameters!$B$5)*( (ScenarioParameters!$B$2/4) *(1+$B$1))*(1/1000)
a. Note: ScenarioParameters!$B$2 is the link to Sales & Production Volume (units/year). b. Note: ScenarioParameters!$B$5 is the link to the Unit Productions Cost ($/unit). c. Note: Dividing ScenarioParameters!$B$2 by 4, converts Volume from Yearly to Quarterly d. Note: Multiplying (ScenarioParameters!$B$2/4) by (1+$B$1), in which $B$1 is the
sensitivity value, creates the change in measure for sensitivity. e. Note: Multiplying (1/1000), converts from Millions $ to Thousands $.
4. Cell G10: change to: =(ScenarioParameters!$B$2/4)*(1+$B$1)
a. Note: ScenarioParameters!$B$2 is the link to Sales & Production Volume (units/year). b. Note: Dividing ScenarioParameters!$B$2 by 4, converts Volume from Yearly to
Quarterly. c. Note: Multiplying (ScenarioParameters!$B$2/4) by (1+$B$1), in which $B$1 is the
sensitivity value, creates the change in measure for sensitivity.
5. Cell G12: change to: =(ScenarioParameters!$B$2)*((ScenarioParameters!$B$4/4)*(1+$B$1))*(1/1000)
a. Note: ScenarioParameters!$B$2 is the link to Sales & Production Volume (units/year). b. Note: ScenarioParameters!$B$4 is the link to Unit price ($/unit). c. Note: Dividing ScenarioParameters!$B$4 by 4, converts Volume from Yearly to Quarterly d. Note: Multiplying (ScenarioParameters!$B$4/4) by (1+$B$1), in which $B$1 is the
sensitivity value, creates the change in measure for sensitivity. e. Note: Multiplying (1/1000), converts from Millions $ to Thousands $.
6. Cell G13: change to: =(ScenarioParameters!$B$2/4)*(1+$B$1)
a. Note: ScenarioParameters!$B$2 is the link to Sales & Production Volume (units/year). b. Note: Dividing ScenarioParameters!$B$2 by 4, converts Volume from Yearly to Quarterly. c. Note: Multiplying (ScenarioParameters!$B$2/4) by (1+$B$1), in which $B$1 is the
sensitivity value, creates the change in measure for sensitivity.
7. Copy G9 to H10:Q10.
8. Copy G10 to H10:Q10.
9. Copy G12 to H10:Q10.
10. Copy G13 to H10:Q10
Page 22
Figure 27: 10% Sales Volume NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $71,594K for the
10% SalesVol worksheet, as show in Figure 27.
Page 23
Step 13: Create the -10% SalesVol Worksheet
Create a copy of the “10% SalesVol” worksheet.
The Tab name to enter is: -10% SalesVol
Once you have the -10% SalesVol worksheet created, it is quick to edit the worksheet
parameters as follows:
1. Cell B1: change to: -10%
Figure 28: -10% Sale Volume NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $52,739K for the
-10% SalesVol worksheet, as show in Figure 28.
Page 24
Step 14: Create the +20% SalesVol Worksheet
Create a copy of the “-10% SalesVol” worksheet.
The Tab name to enter is: 20% SalesVol
1. Cell B1: change to 20%
Figure 29: 20% Sales Volume NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $81,021K for the
20% SalesVol worksheet, as shown in Figure 29.
Page 25
Step 15: Create the -20% SalesVol Worksheet
Create a copy of the “20% SalesVol” worksheet.
The Tab name to enter is: -20% SalesVol
1. Cell B1: change to -20%
Figure 30: -20% SalesVol NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $43,311 for the
-20% SalesVol worksheet, as show in Figure 30.
Page 26
Step 16: Create the +30% SalesVol Worksheet
Create a copy of the “-20% SalesVol” worksheet.
The Tab name to enter is: 30% SalesVol
1. Cell B1: Change to 30%
Figure 31: 30% Sales Volume NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $90,449K for the
30% SalesVol worksheet, as show in Figure 31.
Page 27
Step 17: Create the -30% SalesVol Worksheet
Create a copy of the “30% SalesVol” worksheet.
The Tab name to enter is: -30% SalesVol
1. Cell B1: Change to -30%
Figure 32: -30% Sales Volume NPV Analysis
If you have followed this process correctly, then the result is a Project NPV of $33,884 for the
-30% SalesVol worksheet, as show in Figure 32.
Page 28
Step 18: Create the SalesVolume_Sensitivity summary worksheet
Create a new worksheet and name the worksheet Tab: SalesVolume_Sensitivity
Format and program the worksheet as follows:
Figure 33: Programming format for SalesVolume_Sensitivity Summary Table
Figure 34: SalesVolume_Sensitivity Summary Table Results
As shown in Figure 34, the table is linked to all the Sales Volume worksheets and provides a
clearly organized summary of the SalesVolume_Sensitivity.
Page 29
Step 19: Graph the Change in NPV ($) vs. Change in Sales Volume (%)
Lastly, graph the relationship between the (Change in NPV, $ Thousands) vs. (Change in
Sales Volume, %) as shown in Figure 35.
Figure 35: Change in NPV ($) vs. Change in Sales Volume (%)
The equation in this example, in y=mx+b format, is .
That translates to a trade-off law: For each 10% (x-axis value) increase in Sales Volumes, there
is $9,427.50K increase in NPV.
Page 30
Step 20: Using Solver to Determine Max Development Cost
Note: Recall that in TIM-105 (1), you were given a problem on the Final Exam to determine the
maximum development cost, for which a project would no longer be feasible. Note, that on the
Final Exam, we also gave you (via email), a process for solving the problem.
Now, we will walk you through an alternative method for solving this problem using an Add-In
tool in Excel called SOLVER. This tool will be extremely helpful later in this course (TIM-
125/225) and in your project. You can reference the “Getting Started in Visual Basic” on the
TIM-125/225 course website to learn more.
A fast approach (i.e., takes under 1 minute for the author to do) to solving the maximum
development cost problem is using the Solver Excel Add-in, to quickly determine what is the
maximum development cost is the as follows:
On the ScenarioParameters worksheet, create a Cell Reference link to the NPV value in the
BaseCase worksheet.
As shown in Figure 36, on ScenarioParameters Cell B1 enter: =BaseCase!B19. This will make
a cell reference to the cell B19 of the BaseCase, which contains the NPV we want the Solver
Add-In to optimize.
Figure 36: Programming of the ScenarioParemeters link to the BaseCase NPV
Page 31
Next, Open the Solver in the DATA: Analysis Ribbon:
Figure 37
Figure 38: Solver Configuration
To configure Solver to perform a simple optimization, to determine the optimal values in Cell
$B$3, that makes Cell $B$11 equal to Value of 0 (zero), Solver will automatically make many
Page 32
rapid changes in Cell $B$3 until Cell $B$11 reaches zero (0). Then it will stop and insert the
solution in cell $B$3. This process will be almost instantaneous. Select the GRG Nonlinear
Solving Method.
You need to also adjust a few of the OPTIONS to adjust for the accuracy of the optimizations.
You can find the Options next the Solving Method (see Figure 38).
Figure 39: Adjustment of Options for Constraint Precision
Set the OPTIONS: ALL METHODS: CONSTRAINT PRECISION to: 0.00001
Do not check any of the boxes.
Page 33
Figure 40
Set the Convergence for the GRG NonLinear Engine to: 0.000001
Leave the Derivate as Forward, and leave the Multi-start and Required Bonds unchecked.
Now click the Solve button to run the optimization.
If you did it correctly, then the result will be a maximum development cost = $89,487,343.115
In the next windows that popups up, you will have the option to KEEP or RESTORE the results.
And, o have Reports generated for your work. These reports are good to include with your work
and results.
Page 34
Figure 41: Solver Results
If you agree with the on-screen result, then select the Keep Solver Solution. Or, you can
Restore the Original Values and investigate your framework.
Before clicking OK, select the Reports (e.g., Answer) that you want to include with your results.
When you click OK, Solver will automatically create and insert a new worksheet called
ANSWER REPORT with this information.
(Hint: Submit these reports to help earn the Extra Credit in TIM-125/225 Homework #1)
An Example of the Solver Results Answer Report for this problem is show in Figure 42.
Figure 42: Example of Solver “Answer” Report.
Select the Reports that
can help you study the
results of the optimization.
Page 35
Figure 43: Maximum development Cost after using Solver
After you Keep the Solver Solution, as show in Figure 43, look at the automatically updated
BaseCase worksheet (Figure 43). The NPV should be equal to zero and the Quarterly
development Cost = -22,372 (if you are using 2 decimal places, then it is -22,371.84 per
quarter).
Great!
What you previously solved using tedious trial and error,
now takes seconds, thanks to software automation.
We realize it took some time to learn how build these worksheets and to learn the techniques.
From here on out you have the knowledge for Rapid Problem Solving. You will easily recoup the
time invested by solving problems in the future faster than you would through continued trial and
error brute force methods. You have learned how to program this type of analysis, for
subsequent work to perform automated optimization analysis that is fast and precise, within
Microsoft Excel.
Re-enforced your learning :
To illustrate how you can rapidly solving problems through a structured process of software
automation:
Go back to the Ulrich textbook (2), and re-solve the problem in Exhibit 15-3, using the given
parameters in the textbook and the linked worksheets you built from this tutorial.
You should now be able to determine the Max Development Cost in less than one minute!
Page 36
Results: Post-Processing
Step 21: The Trade-off for Change in NPV ($) vs. Change in DevCost (%)
Here we present the overall result of the trade-off between the (Chain in NPV, $ Thousand) vs.
(Change in Development Cost, %).
Figure 44: Format of the DevelopmentCost_Sensitivity Worksheet
Figure 45: Change in NPV ($) vs. Change in DevCost (%)
The equation in this example, in y=mx+b format, is . m=-24100 and b=0
That translates to trade-off law: For each 10% increase in Development Cost, there is a $2410
(in thousands) reduction in NPV.
Page 37
Step 22: The Trade-off for Change in NPV ($) vs. Change in Sales Volumes (%)
Here we present the overall result of the trade-off between the (Change in NPV, $ Thousands)
vs. (Change in Sales Volume, %).
Figure 46: SalesVolume_Sensitivity Summary Table Results
Figure 47: Change in NPV ($) vs. Change in Sales Volume (%)
The equation in this example, in y=mx+b format, is . m=94275 and b=0
That translates to a trade-off law: For each 10% (x-axis value) increase in Sales Volumes, there
is a $9,427.50 (in thousands) increase in NPV.
Page 38
Step 23: Determine Maximum Development Cost with Solver
Utilizing Solver, the maximum development Cost for proceeding with the project = $89,487,343
Figure 48: Example of Solver “Answer” Report.
The Objective Cell (NPV) has Original Value of $62,166 and Final Value of $0
The Final Value of the variable (Development Cost Total Cost, $) is: 89,487,343
Page 39
Step 24: Relationship of Development Cost to Sales Volume3
Shown in Figure 49, a 10% increase in development cost equates to a decrease in NPV of 3.88%.
Figure 49: Development Cost Sensitivity Analysis Summary
Show in Figure 50, a 10% increase in Sales Volume equates to an increase in NPV of 15%.
Figure 50: SalesVolume Sensitivity Analysis Summary
Therefore, under the assumption of linearity, a 2.59% increase in Sales Volume would increase
NPV by 3.88%.
(1)
To summarize, a 2.59% increase in Sales Volume is needed to offset the 3.88% drop in NPV created from the 10% increase in development cost.
3 Adopted from Ulrich and Eppinger, 4
th Edition
Page 40
Conclusions
In this tutorial you have now learned a powerful and structured approach, to problem solving
using linked Excel Worksheets with the Solver Add-In for optimization, to perform sensitivity
analysis.
The result of completing this tutorial is that you have gained knowledge of a structured approach
to rapid problem solving using software automation (Figure 51):
Pre-processing
1. Setting Up Excel Workbooks
a. Reading reference
b. Linking worksheets
c. Writing formulas
d. Formatting Spreadsheets
Software
Automation
Rapid
Problem
Solving Processing
2. Exploring Scenarios
a. Entering parameters
b. Adjusting parameters
c. Performing sensitivity
analysis, Regression
Analysis, and Optimization
d. Summarizing results
Post-Process
3. Presenting Results
a. Graphing
b. Generating reports
c. Exporting content
d. Drawing conclusions
Figure 51: Structured Approach to Rapid Problem Solving with Software Automation
You are now ready to take the next step and apply this new knowledge towards optimization of
Supply Chain quantitative models.
Page 41
Works Cited
1. Desa, Subhas. TIM-105/205: Management of Technology 1. UC Santa Cruz : Baskin School
of Engineering, Fall 2011. <https://courses.soe.ucsc.edu/courses/tim105/Fall11/01>.
2. Ulrich, Karl T. and Eppinger, Steven D. Product Design and Development. Fourth Edition.
New York, NY : McGraw-Hill, 2008. 978-0-07-31042-2.
3. techtutortv. Move or Copy a Worksheet in Excel 2007. www.YouTube.com. [Online] [Cited:
01 07, 2012.] <http://www.youtube.com/watch?v=OucSxFiLyBc>.
4. Taketa, Richard. Cell References. Software Guides. [Online] San Jose State University,
Department of Geography. [Cited: 01 07, 2012.]
<http://www.sjsu.edu/depts/geography/resource/swdocs/xl-guide/edit/celref.htm>.
5. tlcproducts. Trend Lines and Regression Analysis in Excel. www.YouTube.com. [Online]
[Cited: 01 07, 2012.] <http://www.youtube.com/watch?v=6rOlGbLeQxI>.