Oct 1, 2017

5-Year Financial Model: Manufacturing with Customer Re-Purchase Logic

This model includes two logic strings I have not included before. The main benefit is more accurate cash flow planning and easily changing assumptions for the % of customers that re-purchase your product and for how many months in the future they will keep re-purchasing.



Pricing
*The model will be e-mailed to you as soon as the purchase is made.

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 financing x amount, it means you will have to come up with less cash up-front 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.

No comments:

Post a Comment