Financial Models

This financial model was built for a Jewelry retail store, however it could be used for any brick and mortar retail store that wants a high degree of granularity in its sales forecast by product type. The template includes a 5 year monthly and annual pro forma, DCF Analysis, as well as 13 visualizations (much of which are 3D charts and charts that describe the forecast on a per category basis. The most commons uses of this template would be for something like a jewelry store or a bike shop.

One of the nice global features of this Excel template is a sensitivity input applied to the sales and price. With this the user can easily set a baseline for all expected sales/price and then adjust it up or down from that base by a defined percentage. The main driver of sales is the revenue assumptions tab. There are up to eight categories and a total of 55 sub-categories for each of those. As you change the text on them, the text through the entire model, including visualizations, will update. For each of the 55 sub-categories, there is max strategic input planning. By this, I mean the user can enter a start month, the total monthly sales in each of the 5 years of this forecast, up to 3 pricing tiers, and the percentage of total sales for each tier. By adding 3 pricing tiers for each of the 55 sub-categories, there is a lot of ground that can be covered in terms of how granular you can get with the products you want to forecast for. Technically this allows for 165 total categories, which are differentiated by sales volume and price. I also put an ‘on/off’ switch for each one if you want to quickly zero out a given sub-category. Each sub-category has its own gross margins for each pricing tier as a result of defining the cost of goods sold therein. For operating costs, there are two main schedules. The first is fixed costs (up to 42 slots and the user defines the monthly cost in each year for each slot as well as the start month) and the second is variable costs (up to three slots defined as a percentage of total revenue). The second could be things like a franchise fee, ad fees, sales tax, or what have you. There is also a schedule for future capex and one-time startup costs. After all the assumptions have been entered, the user will see a series of automatically generated summaries. They include: monthly / annual pro forma that drives down to EBITDA and cash flow before/after leverage, break-even analysis, DCF Analysis, IRR/ROI, visualizations, and Executive Summary.