Modeloptic - Quick Start Guide

Basics
This is where you define:
  • The Company name and Model name (which will also be used as the file name)
  • The forecast frequency (monthly, quarterly, or annually)
  • Whether or not to include historicals (and if so, what years to include). If you only want to include one year of historicals, set the start year and end year to the same year.
  • Whether or not to include projections (and if so, what years to include)
Company Structure
The next step is to define the structure of the company. There are three primary organizing units to the income statement:
  • Divisions, shown in blue. Each division will be forecast on a separate tab, with the name of the tab corresponding to the name given for the division. All divisions will then be linked up into a Consolidated tab.
  • Sections, shown in orange. These include the primary sections typically shown on an income statement (Income, Cost of Goods Sold, etc).
  • Sub-Divisions, shown in green. These correspond to the individual line items within each section that you'd like to appear on the income statement.
Note that while not technically part of the income statement, capital expenditures are included here for simplicity.

Note also that while there is a separate section for Depreciation & Amortization, these expenses must also be included in either Cost of Goods Sold or Operating Expenses (as they would according to standard accounting rules). The reason for specifying them again in the Depreciation & Amortization section is so they'll be correctly linked to the correspond balance sheet item(s)

Similar to the income statement, you also define the line items that exist on the balance sheet (if you wish to include it).
Historical Data
If you have chosen to include historical data, you can then feed in your data via an excel file. As long as your data is in consecutive columns, the system will be able to read it in.

Once you upload the file (or files) that contain your data, you then tell it what periods each file or tab covers, and where in the workbook the relevant data is. The system will then grab your data from the places indicated, and will feed it into the model. You can also re-download files that have been uploaded here if needed.

"Metric" data is any historical data that you'd like to reference in your projections. For example, you may want to upload historical unit sales volumes, and then forecast unit sales as increasing over the prior period, which you can do in the projections section.
Income Statement Projections
Forecasts can be made either at the Section level (e.g. apply a growth rate to all revenue items in a Division), or at the Sub-Division level.

If you choose to specify by Sub-Division, the most detailed forecast option is the "Build" option. Here, you'll add as many build "instructions" as you'd like, each of which corresponds to a line in excel.



Above is an example of what a build instruction line looks like. It has several options:
  • A) Instruction Type. "Build" is a general instruction, "Label" will add an underlined label in the labels column but otherwise be blank, "Space" will add a blank row, and "Total" should be used once per sub-division as the final instruction, which represents the total for the given sub-division which will flow through to the rest of the model.
  • B) Line Name. The name you enter here will be written into the labels column in excel, and can also be used as a reference name within formulas.
  • C) Historical Method. Options include:
    • a
    • b
  • D) Projection Method. Options include:
    • a
    • b
  • E) Format Options. This sets the formats for the line.
  • F) Add New Build Instruction. Adds a new line.


Within build instruction lines, creating formulas works very similar to excel. You can use excel functions in the formulas, like SUM, IF, ROUND, etc. References to other lines are the only part that's different. [EXPLAIN REFERENCES WITH EXAMPLES]
Balance Sheet Projections
If you want to include a fully-linked three statement model, you'll then need to forecast the balance sheet. Most options should be fairly self explanatory. The "build" forecast option works the same way as the income statement does.

One complication is in forecasting PP&E, Intangible Assets, or Goodwill. If you select one of these types, you will be given options to link to the Depreciation & Amortization and/or Capital Expenditure line items that correspond to the defined balance sheet item.
DCF (Discounted Cash Flow Analysis)
If desired, this will add a DCF tab to the model, which will reference the projected financials. Input assumptions related to WACC and terminal value may be altered as desired.
Transactions
Currently, the only option available is a buy-out. If you choose to add this, you must then define the purchase price and the sources and uses for the transaction. The system will then create a "S&U" tab, apply the specified actions to the last historical period to produce a pro-forma balance sheet, and then will begin the forecast period using the pro-forma balance sheet and capital structure. Note that you must add the relevant new equity or debt accounts to the balance sheet prior to completing the sources and uses, as those will ask for references to the newly created items.
Outputs
Choose whichever outputs you would like, and this will be calculated at the bottom of the "Model" tab and linked to in a "Summary" tab.
Section Labels
If you'd like to use different names for the standard line items, you can alter them here (e.g. you can rename "Gross Profit" to "Contribution Margin" if desired)
Model Formatting
Here you can change formatting options like the header colors, display units and number of decimal places to show on numbers, excel column widths, and font.
Saving and Generating Models
At the very bottom of the page are buttons to either Save your inputs as they stand (and not generate the model), or to Save & Generate the model from the given inputs, which will then download the generated excel model. If you choose the generate option, the system will check to make sure all of your inputs are valid first, and will give you pointers if anything happens to be invalid.
Creating, Cloning, and Deleting Models
If you return to the model selection screen (either by logging in, clicking on the Modeloptic logo from a model editing page, or going to https://www.modeloptic.com/models), you will see options for creating a new model, deleting an existing model, or cloning an existing model.
Modeling Philosophy
There are many ways to structure and format financial models. Modeloptic takes the following viewpoints:
  • Assumptions should be explicit rather than implicit
  • Models should be built to optimize for clarity and readability
  • Assumptions should be input in the context in which they're used, rather than all in one place
  • Inputs and key assumptions are in blue font, formulas are in black font, and links to other tabs are in green font
  • Expenses and cash outflows are shown as negative numbers
  • Every piece of data should only have one place where it is input or calculated (ie there should be a "single source of truth" for all data). If data needs to be used elsewhere, it should be linked to
  • Subtotals should come below the items that are being totaled and have a top border
Next Steps
If you have any questions, comments, problems, feedback, or just want to chat, please reach out any time to: luke.harris@modeloptic.com and I will get back to you shortly. I'd love to hear from you!