$45.00 USD
The biggest challenge here was getting the inventory batching right and to come in at the right months and planning how the assumption table should best be structured to make it happen. The final inputs end up being a table of 10 purchasing periods. In each period you enter the months ahead of time you want to make the purchase and a number of months the purchase is for.
Based on your forecasted unit sales (that are built in the revenue assumptions above this) you will then have a monthly cash outflow for inventory that happens over time. This was segregated from the COGS that pulls directly from the unit sales per month, which ended up making the EBITDA much different than the actual monthly cash flows. That is fine and they should be different as long as the end cash flows don't double count the cost of goods sold, which this logic doesn't.
More logic that has been modeled out here includes the ability to have financing start at a given month and in-turn the leveraged and non-leverage capital requirement. For example, if you are financing x amount, it means you will have to come up with less cash upfront or from investors. This generally makes your IRR higher.
Note that the IRR is not assuming any terminal value and just valuing the projected future cash flows from operations. The inventory timing is a pretty big factor here along with your planned monthly unit sales growth.
Finally, I did add a licensing agreement feature (up to 3 deals) if you plan on allowing others to use your intellectual property in return for a % of their sales. This may not be applicable and if not simply enter 0's in the relevant boxes.
As always, I have added plenty of visuals in here so you can really see what the 5-year plan looks like in terms of annual revenues / EBITDA, cash flow, unit sales, licensing sales, re-purchase sales vs. base sales and so-on.
In general, a financial model that is for broad use (any business that sells some kind of product) should be simple enough to fit everyone's general needs but still have enough dynamic assumptions to build all kinds of different scenarios. That is why I have shot for here.
One way you can build entirely separate cases is by simply using the template with a few different names.
Based on your forecasted unit sales (that are built in the revenue assumptions above this) you will then have a monthly cash outflow for inventory that happens over time. This was segregated from the COGS that pulls directly from the unit sales per month, which ended up making the EBITDA much different than the actual monthly cash flows. That is fine and they should be different as long as the end cash flows don't double count the cost of goods sold, which this logic doesn't.
More logic that has been modeled out here includes the ability to have financing start at a given month and in-turn the leveraged and non-leverage capital requirement. For example, if you are financing x amount, it means you will have to come up with less cash upfront or from investors. This generally makes your IRR higher.
Note that the IRR is not assuming any terminal value and just valuing the projected future cash flows from operations. The inventory timing is a pretty big factor here along with your planned monthly unit sales growth.
Finally, I did add a licensing agreement feature (up to 3 deals) if you plan on allowing others to use your intellectual property in return for a % of their sales. This may not be applicable and if not simply enter 0's in the relevant boxes.
As always, I have added plenty of visuals in here so you can really see what the 5-year plan looks like in terms of annual revenues / EBITDA, cash flow, unit sales, licensing sales, re-purchase sales vs. base sales and so-on.
In general, a financial model that is for broad use (any business that sells some kind of product) should be simple enough to fit everyone's general needs but still have enough dynamic assumptions to build all kinds of different scenarios. That is why I have shot for here.
One way you can build entirely separate cases is by simply using the template with a few different names.
Also, check out this volume discount pricing calculator.
