Sign in or start a free trial to avail of this feature.
5. Lay Out Model in Excel
It's important to lay out your model in an organised and easy-to-read structure, particularly if sharing the model among a team
Lay out model in Excel
- 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
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
Now that we have our influence diagram completed, it's time to start building our model in Excel. When structuring a model in Excel, the question you should ask yourself is, if somebody totally unfamiliar with the model opened it up for the first time, could they use it effectively? This is because, at some point in the future, it's likely that someone unfamiliar with the model will have to use it, or if you need to return to the model in a couple of months, it would be ideal to immediately get up to speed as quickly as possible.
I'll start by adding a cover sheet to the workbook. So, Shift F11 to create a new sheet, and Alt O, H, R to change its name. And I'll simply write, Cover Sheet.
And off camera I'll add some details about the problem statement, the start date, and a few other details associated with the problem. On the next sheet, I'll leave the data set, and then I'll create a new sheet with Shift F11 called, Influence Diagram. So, Alt, O, H, R, Influence_Diagram.
This sheet will contain the influence diagram we created in PowerPoint in the previous lesson. When we're building a model, it's nice to have the influence diagram close at hand, so we can switch between the diagram and the model, as we're building.
So, I'll return to PowerPoint, I'll select the image, and group, and then copy with Ctrl C, return to Excel, and paste with Ctrl V.
The last sheet I'll add will be the model. So I press Shift F11 for a new worksheet. Alt O, H, R to change the name. And I'll call it Sample Single Flight Model.
In the top left hand corner, I'll write a legend that specifies three data types. Inputs or hard-coded data, formulas, and data from other sheets. Lastly, we'll lay out the model itself. Typically every model has a control panel, which allows you to change the inputs, and view the output values all in the same screen. For later iterations of our model, the calculations will be separated from the control panel, but for this first iteration, we can structure the model as visualized in the influence diagram. With inputs and parameters on the left, intermediate variables in the middle, and outputs on the right hand side. I have made one or two changes to the influence diagram layout. On the left hand side, I've split the parameters into fixed assumptions and projections, and on the right hand side, I've included additional revenue and additional cost as outputs instead of intermediate variables to improve readability. Our last task before actually building the model is to include some sample data for the inputs on the left hand side. I'll do this off camera, and now we're ready to build our model.