4. Structure Model in Excel
In this lesson, we structure our model in Excel using the influence diagram from the previous lesson.
Structuring model in Excel
- Excel workbook consists of Cover sheet, Influence diagram, Control Panel and Model
- Models should be easy to navigate and easily understood by first-time users
- Passing this test will ensure minimum time is spent getting up to speed on the model
- It will also ensure that errors and the misreading of calculations are less likely to occur
- The control panel allows a user to change inputs and view outputs on the same screen
- Each input parameter will be a named cell to make our formulas more readable
- A new user should be able to enter inputs and view outcomes from the control panel
- Each year has a cell in the top row of the model sheet
- In the first column we enter the parameters that will affect our annual cashflows
CTRL + F3: Open name manager
ALT + N: Create a new name
SHIFT + F11: Create a new sheet
ALT + O , H , R: Re-name sheet
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
ALT + E , S , F: Paste formulas
CTRL + B: Bold text
CTRL + Spacebar: Select full column
ALT + O , C , A: Autofit column
With our influence diagram now created, it's time to move to Excel and begin building our model. Off camera, I have created a cover sheet with a problem, start date, and my name. On the next tab, I have included the influence diagram in case we need to refer back at any stage when building the model. And on the last tab called Control Panel, I've included the parameters and the outcomes for our model. In the parameters section, I've split the entries into reference plant and commercial plant. When you have over a dozen parameters in a model like this, it can help to split them into multiple groups particularly for future users of the model. We'll start by entering in the values for each of these parameters and to save some time, I'll do this off camera. To make our formulas more readable I'd like to name each parameter cell. So I'll press Ctrl + F3 to bring up the name manager and Alt + N, to create a new name. This name will be REF_PLANT_COST and then I'll press OK. I'll create another new name for the next line which will be REF_PLANT_START_YEAR. This time, I'll select the cell below. Off camera, I'll enter new names for the rest of our parameters. When we now scroll to our parameters you can see that there is a new name for each cell. In the outcome section of the control panel, I have included project cash flows, return on investment and the final decision. Every time I update the model with new parameter values, these are the three outputs I'd like to view. Now let's move on to the model itself. This can be placed below the control panel or on a new sheet. As you've seen in the previous course, I'd like to keep the model separate from the control panel and I'll do the same here. So I'll create a new sheet with Shift + F11 and then Alt + O, H, R to change the name and I'll simply call it Model. Across the top, I'll add a row of years starting with 2014. And then each subsequent year will simply be the previous year plus one. And I'll copy this across for a dozen or so years. Here I've entered the years as numbers broader than dates. If you want to use the date format, just remember you'll have to convert these dates back into numbers when we are using them in our formulas. Let's start by adding our reference plant model entries. And the first of these will be reference plant cost. 26 00:03:02.13 --> 00:03:10.18 Next will be the commercial plant.
And finally, operating cash flows. Under the commercial plant, I'll simply have commercial plant cost and under operating cash flows where I'll create a new row, I'll have revenue, production cost and of course annual cash flows. And I'll bold this as our final line. I'll not AutoFit this column with Alt + O, C, A. I'll add some formatting off camera and now we're ready to build our model in the next lesson.