Sometimes when we enter a formula, we need to repeat the same formula for many different cells. In the spreadsheet we can use the Copy and Paste command. The cell locations in the formula are pasted relative to the position we Copy them from. The different ways to do it (there are more, but stick to the simple ones):

1. Right click on a cell and select Copy from the context menu, then go to the cell you want to paste it and right click, select Paste.

2. Go to the cell you want to copy and use the shortcut Ctrl + C for Copy. Then go to the cell you want to paste it and use the shortcut Ctrl + V for Paste.
3. If you have a lot of duplicate formulas you can also perform what is referred to as a FILL DOWN. (discussed next).

A B C 1 5 3 =A1+B1 2 8 2 =A2+B2 3 4 6 =A3+B3 4 3 8 =? + ?

Cells information is copied from its relative position. In other words in the original cell (C1) the equation was (A1+B1). When we paste the function it will look to the two cells to the left. So the equation pasted into (C2) would be (A2+B2). And the equation pasted into (C3) would be (A3+B3).

Often we have several cells that need the same formula (in relationship) to the location it is to be typed into. There is a short cut that is called Fill Down. There are a number of ways to perform this operation.

1. select the cell that has the original formula
2. hold the shift key down and click on the last cell (in the series that needs the formula)
3. a) under the Home menu go to the blue downward facing arrow and select fill down (you can also select fill right, fill left or fill up)
   b) alternatively, use the shortcut Ctrl + D (copy down) or Ctrl + R (copy right)

Another alternative that uses the mouse is to only select the cell(s) we want to copy down, right or any other direction, and then select the little black box on the side of the cell. While holding it clicked, drag the cell content in the direction you want to fill.

A B C 1 5 3 =A1+B1 2 8 2 fill down 3 4 6 fill down 4 3 8 fill down

Cells information is copied from its relative position. In other words in the original cell (C1) the equation was (A1+B1). When we paste the function it will look to the two cells to the left. So the equation pasted into (C2) would be (A2+B2). And the equation pasted into (C3) would be (A3+B3). And the equation pasted into (C4) would be(A4+B4).

As you copy a formula, the cell references in the formula also move a corresponding number of cells across and down. Such cell references are called relative cell references.

There are times when you do not want Excel to exhibit this behavior because you may want to either keep the colum, or the row, or both parts of a cell reference fixed in one of your fomulas. Such cell references are called absolute cell references.

To prevent Excel from changing the reference while copying the cells, insert a "$" before the portion of the reference which you want to freeze. You can also click into the cell code at the specific cell reference and press F4. Every time you press F4, it will change the $ signs in the following order: A1, $A$1, A$1, $A1, A1, and so on...

Examples:

Reference Style	Meaning
$A$1	Both the column and row reference are fixed. Neither will be incremented or changed during a copy or fill operation.
$A1	Only the column reference is fixed. It will not change during a fill or copy, but the row will change.
A$1	Only the row reference is fixed. It will not change during a fill or copy, but the column will change.

Learning this code will dramatically decrease the time you require to build worksheets.

Data Tables can help us visualise a result but assuming different values for an input variable (one dimension) or for two input variables (two dimensions) and seeing how this result changes under different values for the input (this is also called doing a form of what-if analysis or sensitivity analysis). What if I took different input values: how would my result change? Let us first address the 1-dimensional data table.

To create a data table we need two things: (1) a calculation producing a result (e.g. profit, NPV, IRR, etc) that depends on input variables and (2) a range of values that our input variable can assume.

You have a calculation for the profit made by a venture where you estimate demand and need to install a fixed capacity and set a fixed price ahead of time. On its own you can run scenario's of adjusting the inputs (marked in blue) to find out what capacity gets you the highest profit or what profits you can expect given various demand scenarios. Profit is our output cell (we could have also chosen revenues). Your profit is calculated in cell D12. To set up a 1-dimensional data table, create a list of values for the input parameter you want to analyse (here demand shall range from 50k to 175k). Put a reference to your result (=D12) into the cell above the first blank column next to your input range (here this is cell D14 where we put formula =D12).

Pre-requisites are complete, now you are ready to use Excel's data table functionality! Select the whole area from the blank cell at the top left-hand (C14) to the bottom row of Demand and two columns wide (D20), i.e. C14:D20. Now, on the ribbon, go to 'Data', then select 'What-if Analysis' and choose 'Data Table' (In previous versions of Excel you had to select 'Data', 'Table').

Because our input range is listed in a column, select cell='$C$5', your demand variable, as 'column input cell'. This will parse through the values of the left hand column, "hypothetically" plug them into the demand cell (C5) and will record the outcomes of this "what-if analysis" for the output we specified on top (D14), i.e. profit, one-by-one next to the inputs. Hence, this generates a table of profits based on different demand scenarios.

You can also create one dimensional data tables that have several output variables listed next to each other and run all those outputs against one range of inputs. It is still considered a one dimensional data table. Secondly, you can also choose to create a data table where the input range is listed in a row and your output variables are arranged in a column. Just make sure that you then choose 'row input' in the Excel data table tool.

Finally, you may choose to plot the impact of your input variable on your output variable in a line chart. This shows the sensitivity between demand and profit quite nicely and also illustrates the capacity constraint that we encounter at the capacity of 150,000 units. Demand higher than 150k will not lead to higher profits for us.

 

Two dimensional data tables are very similar to one dimensional ones so I will only focus on the differences to the above write-up here. The two main differences are:

1. We can analyse the impact of different values for TWO input variable on an output variable at the same time
2. We are limited to ONE output variable only (but you can obviously create numerouse 2-dimensional data tables to analyse different outputs or different input/output constellations if you like)

Just as previously, you can find out what capacity gets you the highest profit or what profits you can expect given various demand scenarios. Profit is our output cell and this time we can find optimal profit at BOTH different demand scenarios and different plant capacities simultaneously!

Your profit is still calculated in cell D12. This time you create a list of values for the one input parameter in a column and for the other input parameter in a row. Put a reference to your result (=D12) into the cell above the input column next to the input row (C23, see above illustration). Now, select the entire area of inputs and blank cells INCLUDING your input row and input column values and the output cell (C23:H29). As illustrated above, on the ribbon, go to 'Data', then select 'What-if Analysis' and choose 'Data Table' (In previous versions of Excel you had to select 'Data', 'Table').

Now comes the hard part: What do we put as row input cell and what do we put as column input cell?

Look at your table set up: The row has the values for different plant capacities, so the capacity variable (C6) should be your row input. The column has the values for different demand scenarios, so the demand variable (C5) should be your row input. HINT: what to put as 'row input variable' is determined by what values you have written in the first table row; what to put as 'column input variable' is determined by what values you have written in the first table column.

Wow! A two dimensional sensitivity table allows us to look at many possible outcomes under all kinds of combinations of input variables.

In our example we can see that while installing 150k of capacity we may make up to £1.5M in profit, there's also a chance of losing £500k. If we installed slightly less capacity (say 125k or 100k we'd reduce the upside potential but also reduce the risk of losing money.

Finally, you may choose to plot the impact of your two input variables on your output variable but this time I recommend using a 3-D surface chart. This shows the sensitivity between demand, capacity and profit quite nicely and also illustrates the capacity constraint that we encounter at the different installed capacity levels. Demand higher than this constraint will not lead to higher profits for us; however, lower demand leads to large losses if our installed capacity was chosen too optimistically.

 

Running sensitivity analysis and scenarios has improved business modelling significantly in the past 20 years; however, this is not where we should have stopped. Sensitivity analysis as you know by now can only show the effect of two key performance drivers (KPDs) on one key performance indicator (KPI). But what if various KPDs are linked, correlated, move at the same time? Then you may alternatively say: Scenario analysis can alter many variables and come up with e.g. a base, downside and an upside case scenario. Well, that only means you have created a new case that fixes all the variables. You only look at three out of many possible future outcomes. Who tells us that precisely one of your scenarios will actually materialize?

Simulation Enhanced Modelling is the logical next step. We shouldn't only look at average inputs and expect to arrive at average outputs. We shouldn't only look at one or two drivers that can be uncertain because in most cases many other factors will be uncertain at the same time. We should try to get a feel for the underlying uncertainties that exist in our particular business understand how they are related and impact our business and START TO MODEL THE UNCERTAINTIES. You can still do this in Microsoft Excel and use add-ins like xlSim or Crystal Ball just to name a few simulation software packages . The movement goes away from "number in" - "number out" calculation to "shape in" - "shape out" calculation via Monte Carlo simulation.

I would like to refer you to a good book the "Flaw of Averages: Why We Underestimate Risk in the Face of Uncertainty" by Sam Savage from Stanford. There may be constraints that we don't see if we don't make variables random, we don't think about flexibility if we only build one robust case, we basically get complacent with that one scenario that we underwrite. The book displays many good examples and cases where calculating with averages or expected values can lead to tremendous mistakes. This is the stuff we currently teach in our Management Science courses at the University of Cambridge because we want you to rely on more than a flawed DCF calculation, gut feel and a bit of luck to be successful in business.