Basic Financial Model

 

Embedded above is a basic working financial model for Sophie’s Bicycle Shop. You can download into Excel by clicking the link at the bottom of the embedded spreadsheet.

For a typical modeling assignment, start by reviewing the historical financials, then forecast the future, and finally build some different scenarios such as opening a new store, buying a competitor etc. (I haven’t thought all these out, so I’m open to suggestions).

I’m purposely left out some sub-calculations to keep the model simple, we will hopefully cover at a later time.

Income statement

This is pretty straight forward. We’ll cover depreciation, taxes, and interest in later posts.
View more posts about the income statement.

Notes

There are several methods to calculate depreciation, but we’ll keep it simple at a even depreciation.
Interest Expense is to service the debt the company borrowed to start.
Interest Income is the interest earned with the cash that is sitting in the bank.
Taxes were calculated at a simple 35%. We’ll get into Net Operating Loss Carryforward in a much later post.

Balance Sheet

The balance sheet is a snapshot of the status of the company usually at the end of a certain period. In the case of our business, it’s the end of the year. But the same can be done on a monthly and quarterly basis.
In future posts, we’ll go over some areas in more detail including Accounts Recievable/Payable, Inventory, Debt, and Plant, Property & Equipment, and Depreciation.

Notes

The business was started with a $1,000,000 investment and a $500,000 loan with an interest rate of 8%.

Cash Flow Statement

Cash Flow shows the cash inflow and outflows over a certain period of time.
In the model, the cash flow is the calculation to determin the ending cash balance.
It simply pulls number from the income statement and the balance sheet to calculate the cash change and ultimately the ending cash balance.
This is the most cruicial part of the the financial model because it’s the check to make sure everything works. It is also the easiest to mess up.

  • yousuf

    great