Bike Shop / Distributor Financial Model

A bottom-up financial model to create a 5-year forecast, find minimum equity required, and pricing feasibility.

, ,
, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Video Tutorial:

A bottom-up financial model like this focuses on unit economics. What this means is that the user can define various metrics about unit sales, growth of those sales, cost of those sales, and any other revenues or expenses directly related to the selling of units.

In this case, we are talking about selling bikes. The main assumptions that go into this are configurations for up to four product categories that each have five slots. Each slot is configured based on the start month, starting monthly sales, monthly percentage growth in sales over five years, and markup from cost of goods sold.

Purchasing and inventory management logic is included based on defined months in advance that inventory will be purchased for. It could be that you buy inventory 2 months in advance or 3 months or 6 months or what have you. The cash flow logic takes this into account by adding back all the COGS directly related to inventory and reduced by the purchase amount in the month of purchase.

Also, freight and shipping is a dynamic item that populates in the month of purchase and is based on up to six levels per the bike volume purchased in a given month.

All the assumptions lead into final reports such as the monthly and annual pro forma detail that drives down to EBITDA and cash flow as well as an annual Executive Summary, DCF Analysis (for owner / investor / project), and IRR of each.

The point of the model is to plug in assumptions that you think are attainable and see if your pricing and margins are feasible over time. This will allow the user to account for all operating expenses as well and on a granular level i.e. what it takes to run the place / keep the lights on (managers, sales, rent, utilities, etc…).

Finally, a terminal value will populate in the exit month if chosen and it is based on the trailing 12-month EBITDA per the exit month against a defined multiple. Any remaining debt that exists on that month automatically gets paid back.

Write a Review

500 character(s) remaining