Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
QM 323 – ANALYTICS for BUSINESS DECISIONS
2 Designing Spreadsheets for Decision Making
2.1 Spreadsheet and Workbook design
Throughout the semester we will be building spreadsheet models of business decisions. Good spreadsheet design is the foundation for good decision modeling with spreadsheets.
A workbook is a group of related worksheets or spreadsheets. In this lecture, we learn how to plan and build good spreadsheets and workbooks for decision-making. You will practice design skills that you will need for your project base case workbook.
There are several key steps in creating a good Excel spreadsheet or workbook:
1. Plan the structure
2. Build the spreadsheet
3. Review
4. Communicate findings
Plan: Designing a Workbook or Spreadsheet
The first step in planning or designing a workbook is to define the objective. What is the goal of building the spreadsheet? What are the needs of the person or team using the spreadsheet?
It’s wasteful and time-consuming to redo work, so you should take as much time as necessary in the planning and design process to avoid problems later. It is usually best to start this process by turning off the computer and thinking about the objective and the choices. Think about the purpose of the spreadsheet, and design for that purpose. Set up the design so that a smart person who is unfamiliar with the model or problem can make sense of the spreadsheet with relatively little effort or explanation. One way to achieve this is to keep it simple. Try to make the design as clean and straightforward as possible.
Once you have defined the objective or purpose, you can begin to draw a sketch of the spreadsheet/workbook. An effective way to do this is to use an influence chart.
An influence chart is a simple diagram of a model. It shows what outcome variables the model will generate, and how these outputs are calculated from inputs. It helps organize and plan the model and summarize the key relationships.
While the specific shapes and colors used in creating influence charts may vary from one organization to another, it is important to use a consistent shapes and colors for all the influence charts you create. In this class we will use the following conventions (consistent with the Albright and Winston text).
1. Rectangles indicate given information (fixed inputs).
2. Ovals indicate decision variables.
3. Rounded rectangles indicate calculated quantities or intermediate variables.
4. Shapes with thin gray borders indicate the objective or output variable.
|
For your plan workbook: discuss with your team how you plan to share files and manage access to documents. We recommend using Dropbox or another file sharing program to store files in a common location. We recommend that you create a shared folder for your team, with subfolders for functional areas Do NOT try to use Google Sheets for your cross-functional workbook. While it is convenient from a sharing perspective, it does not have all the functionality of Excel. Also, it is important to ensure that all cell references within your cross-functional workbook are to locations within that workbook…that is, your workbook does not contain any formulas which refer to another workbook. If you submit a final cross-functional workbook containing cell references to sheets on a team member’s computer, the formulas will be corrupted and you will lose points on your final plan. |
Build: Organize the spreadsheet into modules
The next important message in building a workbook is that it should be organized into modules, or distinct pieces. Modules bring together groups of similar items.
Use the influence chart as a guide to help you separate the following spreadsheet elements into distinct areas of the spreadsheet:
. Parameters (numbers that don’t change but which affect the outcome)
. Decision variables
. Outcomes or objectives
. Detailed calculations
Using consistent and distinct formatting (e.g. by using colors and borders) for each of these modules will make the spreadsheet more readable and easier to work with.
Place the numerical values of key parameters in a SINGLE LOCATION and separate them from calculations.
It is especially important to enter parameters in a separate area of the spreadsheet, isolated from calculations and decision variables. For example, put parameters at the top, followed by decisions, outputs/objectives, and supporting calculations at the bottom. This will make it easier to make changes if the parameters need to be changed, for example, when doing sensitivity analysis. Also, it can be very difficult to edit or find mistakes in long Excel formulas, and it is always better to write formulas that refer to parameters located in an isolated spot on the sheet, instead of hard coding the parameters in the formula itself. Finally, it is much easier to document assumptions behind parameters if they are in a single location.
|
For your plan workbook: NEVER TYPE A KEY PARAMETER IN A FORMULA! Write the formula so that it references parameters in a separate area of the spreadsheet. |
Lastly, hand-entered parameters and calculations should not be repeated in two separate places in a spreadsheet. For example, if you entered the cost per unit on Sheet 1 and you need it in the profit calculation on Sheet 2, do not separately enter the cost on Sheet 2. Instead, the profit formula should reference the original location of the cost information on Sheet 1.
Document Data and Formulas
As stated before, users should be able to open the spreadsheet and understand it with a minimum of effort. To make this possible, you should make sure that all cells should have clear and informative labels. Include a copy of the influence chart on a tab in the workbook.
|
For your QM323 Plan Workbook: Make sure to include the influence chart of your NPV workbook model in the workbook you submit with your final plan. |
You will also need to provide sufficient documentation to explain, for example, the sources of the numbers used as parameters and the logic of formulas. This documentation is usually best done at the bottom of the sheet, or to the right of a cell. You can also use Excel’s comments tool, as follows.
Inserting Comments in a cell: Highlight the cell (or right-click) and select “Insert Comment.” This opens a comment window linked to the cell by an arrow. You can move the location of the comment by clicking on the border of the comment box and dragging the box to a new location.
It is very important to use formatting consistently across tabs. In other words, use “parallel structure,” or the same type of formatting (color, borders, text effects, etc.) for similar items on different tabs. For example, you might always put parameters in a yellow box at the top of the page. Or, you might always make intermediate calculations appear in red. The specific formatting doesn’t matter: what matters is its consistency.
Start Small
Don’t try to build a complex model all at once. Work backward from the final output or objective, and break the problem into modules and sub-modules. For example, if you have a model of your project’s NPV, ask yourselves what data do we need to calculate NPV? One component of cash flow will be operating income. To determine operating income you will want to create a module for revenues and another module for costs. Continuing with our example, revenues will be determined by the various elements of the bases model and costs will probably be broken into fixed and variable costs, and so on.
Complex models become more manageable when you isolate one part of the worksheet and plan/build/review it first, before moving on to another module.
For example, in a multi-year financial model, plan/build/review the first year before moving on to the next.
Designing for Communication
Make the spreadsheet easy for users to navigate and understand. Use formatting and positioning to place emphasis on key outputs and thereby make it easy to find them. Most importantly, use an index tab that links to model components.
To create a link within Excel, follow these steps:
1. Place cursor in desired cell
2. Click “Insert” ribbon
3. Click “Insert Hyperlinks”
4. Select “Place in this document”
5. Select appropriate tab
You can create an index tab that links to each module and/or sheet. On each sheet, include a “Back to Index” link that allows users easily to navigate back to the index.
|
For your QM323 Plan Workbook: Make sure to include an index tab linking to all of the key tabs in the workbook you submit with your final plan. |
Detailer Example
In class, you will start by creating a spreadsheet model of costs in year 4 only.
Note that we have entered parameters at the top in yellow, intermediate calculations in green in the middle, and the most important bottom-line numbers in red, with a border for emphasis. We have carefully labeled and documented each cell.
Review: Reviewing and testing a workbook
We cannot emphasize enough how important this step is. Although Excel is flexible and powerful, it is easy to make mistakes in spreadsheets. These mistakes can have significant consequences.
The first step is to do a reality check: do the results look plausible? Do the numbers make sense? Then, check that formulas are correct. You can do this by inspecting them visually, but also by using some of Excel’s built-in auditing tools. Display all formulas using the “show formulas” option on the Formula
Auditing part of the Excel ribbon.
The Trace Precedents/Dependents tool is also very powerful. Clicking “Trace Precedents” will show the cells that a formula depends upon. Clicking it again will show the precedents of those cells.
Precedents/dependents on other sheets will appear as a dashed line. Clicking on that line will take you to the other sheet. “Trace Dependents” works the same way, only in reverse.
|
The foundation for good decision modeling Good spreadsheet design is the foundation for good decision modeling with spreadsheets – our topic for the next several classes. For your project, you will construct base-case and sensitivity/simulation workbooks using these principles, and your project grade will partly depend on the quality of your spreadsheet design and construction, and the extent to which your worksheet is constructed using the principles outlined in this chapter. |