5. Lay Out Model in Excel

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

It's important to lay out your model in an organised and easy-to-read structure, particularly if sharing the model among a team

Summary

  1. Adding a Cover Sheet (00:04)

    When structuring a model in Excel, you should follow the principle of making the model user-friendly enough that someone could open the model without having seen it before and use if effectively.

    One helpful aspect in this regard is a cover sheet. This goes at the start of the workbook, and includes simple details about the client and the problem statement.

  2. Inserting the Influence Diagram (00:55)

    Next, we insert the influence diagram we created previously. Having this diagram in the Excel workbook makes it easy to switch between the diagram and the model as we build the model.

  3. Adding the Model (01:43)

    Most models allow users to adjust input values with a control panel. They then show the relevant output values on the same sheet. Calculations are generally inserted on a separate sheet.

    However for the first iteration of our model we combine all these features on a single sheet. We structure this sheet like the influence diagram, with inputs and parameters on the left, intermediate values in the middle, and output values on the right.

Transcript

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.

Excel Excel for Business Analytics Learning Plan
Modeling for Business
Build Your First Model