Update: Added dynamic logic to show expected frequency units are rented out and returned as well as a cost tied to that on a per category per unit basis.
The basic underlying premise of this financial model is fairly simple. Units of any category are purchased (based on some purchasing schedule) and those units are rented out overtime at some utilization rate. This could be for any kind of tangible good or a broad range of tangible goods.
The purchasing schedule is dynamic and granular. There are up to 25 categories to enter average costs, units purchased, and the month of purchase. Up to 7 rounds of purchasing can happen for each category as well. This allows for maximizing strategic financial planning i.e. you can see if/when the operating cash flow allows for expansion of the rentable unit base.
Purchasing units will start revenues. That revenue is based on an average monthly daily price charged per category type, an average utilization rate per category, and an average working day count per month. Those drivers are multiplied against the available rentable units each month. Those assumptions can change over each of the 5 years.
To account for the fact that things deplete over time, a depletion percentage was added to show that not 100% of the purchased inventory will be able to be rented out forever. For example, if you buy 10 units of something today, in 4 years maybe only 8 units are left because of wear and tear. This percentage can be set separately for each category and if not applicable it can be zeroed out.
OpEx has a robust data entry schedule for ~100 different cost items that have their own start month and fixed cost per year. Other one-time startup costs and future CapEx (non-inventory) can be accounted for as well.
Since depreciation is a material aspect of any rental business, this non-cash expense item is automatically calculated based on the purchase schedule and the defined useful life of each category. Since we are separating out debt service into principal and interest as well as depreciation, it made sense to drive all the way down to earnings before tax, taxes, and Net Income after tax.
Net book value is displayed over time as well (cumulative purchases less accumulated depreciation).
Output Pro-forma summaries includes a monthly and annual detail, an Executive Summary with key financial line items, and a contribution/distribution schedule for the project, an investor pool (if applicable), and an owner pool. There are assumptions for terminal value based on a multiple of trailing 12-month revenue.
Based on a minimum equity requirement output, the user can define how much of that comes from investors vs. owners. This is after any debt funding has been entered i.e. a traditional bank loan.
15 Visualizations exist throughout the model to make the numbers more digestible.