This is a 5-year financial model to plan out the expected startup costs, operations, and potential exit of a vending machine fleet. The contents could be snacks, cold / hot drinks, or literally anything. There are drivers to account for how fast or slow re-stocking must happen as well as potential waste / spoilage.
The model lets you plan out up to three types of machines. They may vary by their cost to purchase, cost of deployment, count deployed per month, and more. The user will be able to define the size of each vending machine type and the seasonality therein. All major levers are accounted for when trying to get a reasonable forecast that is purely bottom-up.
Revenue Drivers Include:
– Height / Width / Depth for total slot count
– Max refills per month (to define maximum sales capacity possible)
– Average price per item
– Average waste percentage
– Average cost of goods sold percentage (COGS)
– Seasonality (percentage of max sales reached can be defined per month and per year for each type of vending machine)
There is a helper tab that will make it easier for the user to figure out what the weighted average price / COGS / waste might be so that your inputs are smarter.
The model automatically calculated depreciation expense based on a defined useful life and the deployment schedule. COGS are assumed to be purchased in the same month that items sell. There are variable costs that include the refill cost per machine per type and a percentage of revenue paid to vendors where the vending machines are set up if applicable.
General output reports include:
– DCF Analysis / IRR based on contributions / distributions of the project as a whole / Investor / Founder
– Monthly / Annual P&L Detail (down to EBITDA / EBT / Net Income / cash flow)
– Annual Executive Summary
Funding sources include investors, founders, and/or traditional debt. Automatic logic is introduced so that if an exit value is defined the debt will be repaid on exit month. Exit valuation is based on the trailing 12-month revenue against a defined multiple per the exit month selected.
Advanced metrics include average monthly and annual EBITDA per vending machine.