Introducing 3-Statement Models

Question 1

Define

Financial Models

Answer 1

A tool built in a spreadsheet that’s used to forecast a business’s financial performance into the future and make business decisions.

Question 2

List:

The (3) Key Structure for Model Building

Answer 2

1. Inputs (Assumptions) must be clearly identified and should only ever be entered once.
2. Processing (Calculations) should be transparent, can be broken down into simple steps, and easy to follow.
3. Outputs (Graphs & Charts) are quickly accessible and easily updated/exported.

Question 3

List:

The (5) Modeling Base Practices

Answer 3

1. Clarify the problem at hand and define the end users and purpose of the model.
2. Simplify the model by identifying the minimum number of inputs and outputs.
3. Plan how inputs and outputs will be laid out. All inputs should be kept in one place.
4. Integrity - consider using Excel tools such as data validation and conditional formatting.
5. Model Testing by using test data to ensure the model works as expected.

Question 4

List:

The (4) Forecasting Methods

Answer 4

1. Top-Down Analysis
2. Bottom-Up Analysis
3. Regression Analysis
4. Year-Over-Year Analysis

Question 5

List:

The (7) Steps of Financial Forecasting

Answer 5

1. Gather and input historical data
2. Gather and input assumption and drivers
3. Forecast revenue down to EBITDA
4. Forecast working captial
5. Forecast capital assets (PP&E, CapEx, depreciation, etc.)
6. Forecasting Capital Structure
7. Complete the Cash Flow Statement

Question 6

Define:

Top-Down Analysis

Answer 6

A method for forecasting where we start with total addressable market (TAM) and work down from there based on market share and segments until arriving at revenue.

Question 7

Define:

Bottom-up Analysis

Answer 7

A method for forecasting where we start with the most basic drivers of the business (units) and build up the analysis all the way to revenue.

Question 8

Define:

Regression Analysis

Answer 8

A method for forecasting where we start to analyze the relationship between revenue and other factors using the regression analysis in Excel.

Question 9

Define:

Year-Over-Year Analysis

Answer 9

A method for forecasting where we calculate the year-over-year change in revenue. This is the most basic form of forecasting.

Question 10

List Formula:

Forecasting Receivables (AR)

Answer 10

AR Days * 365 / Sales

Question 11

List Formula:

Forecasting Payables (AP)

Answer 11

AP Days * 365 / COS (or COGS)

Question 12

List Formula:

Forecasting Inventory

Answer 12

Inventory Days * 365 / COS (or COGS)