Inputs Section:
User Inputs Worksheet
Above is the User Inputs worksheet to our 3 way model example. It contains two tables: General Assumptions, and Period Assumptions.
At the end of this section you will know what the various end user inputs are, how to organize them in tables, and how to easily name their values so we can use them transparently in formulas.
End User Inputs
I have never seen a model that didn't have end user inputs. These are often called assumptions. Assumptions allow end users to ask "What if" questions like: What if salaries increase? What if revenue declines? What if we paid off our debt early? Whatever values our user wants to change goes here.
General Assumptions Table
This is another Properties table. Each row holds a different model property that end users can change. Like the other properties tables, we name each of these values to match the first column. We use the names in formulas rather that cell references to improve model readability and, thus, transparency. To name the values, select the first two columns of data (not the headers) and use keyboard shortcut Ctrl-Shift-F3 (Tab: Formulas > Button: Create from Selection) and check only Left column. This table contains:
Accounts Payable is the average number days it takes to clear all outstanding payables.
Accounts Receivable is the average number of days it takes to collect receivables.
Depreciation is the rate at which assets are depreciated in this model.
Interest Rate is the rate at which interest is accrued on debt
Rent and Overhead is the annual amount for these expenses (in tens of thousands).
Salaries and Benefits is the annual amount paid to staff (in tens of thousands).
Tax Rate is the the tax rate on taxable revenue.
Using Styles
The Value column is gray. This is because I applied the Input Style to it. I use the Input style to show where end users can make entries and to unlock input cells. For more on this click Styles and Protection.
Item Tables
Some user inputs are related to data sets. At right is the Period Assumptions table for our 3 way model. Each period has five attributes the end user can set. Each period has its own row and each attribute is in its own column. This particular table starts with period six because that is what we set Forecast Period Start to. Prior to that, these values come from actuals (imported data).
Tables are designed to grow. If we need more periods, we just add more rows to this table. Easy.
