Aug 26, 2017

Jewelry / Retail Store Startup Financial Model

This is going to be the most pristine financial model I have done yet. The assumptions as it stands are for a jewelry store startup but you can easily change the wording in all the categories to represent any kind of retail business. It is a great way to build up a 5-year business case from scratch to ramping up full revenues.




Pricing

*Once payment is received, the model will be e-mailed to you.

A sensitivity logic structure is in place so you build your base case and then on the financial overview tab two drop-downs let you choose to have the model run off of base, low, or high. The low and high magnitude can be changed and the two primary global variables that change are unit sales and pricing of each tier.

The reason why it works for any retail business is because the revenue logic is built so that you have main product categories and then sub-categories within that. From there you have corresponding cost of goods sold for each sub-category. So, that means any retail business could plug in their assumptions and it will do a good job at giving the relevant financial picture / story that is trying to be conveyed based on what you have assumed.

The granularity of assumptions:
  • By year average monthly unit sales for all sub-categories.
  • Three pricing tiers for each sub-category.
  • Cogs section for each sub-category.
  • The month a given sub-category starts.
  • Ability to turn any sub-category on or off.
Beyond that, you will see your basic recurring expenses assumptions page that allows for a slew of running costs to be entered by year as well as the first month they will be active. There is also a variable cost section (equals a direct % of revenue). Default fields include franchise fees and/or sales tax if applicable and one other spot for other variable costs.

I also built a section to put one-time expenses. This would be if you plan to expand your jewelry or retail shop with a new showroom area, new equipment, or some other cost that happens after the actual startup period. It won't flow to EBITDA but will flow to the monthly net cash flow.

The cash requirements area was done to include all startup costs, any negative cash flow months, and the first 3 months of COGS i.e. the cost to buy inventory for the first 3 months and that number auto-calculates from whatever the cost of goods sold is for the first 3 months you are active no matter what month you plan on starting.

I have included a business overview page with high level financial information such as revenues, COGS, running costs, EBITDA, debt service, one-time expenses, and net cash flow/running cash position. On this page there are also a lot of nice summary charts and I went for the 3D look on all charts/visuals across the model. There were some fancy looking cost summaries I was able to do because of that effect.

Some other tabs include a break-even page that tells you how much annual revenue you need to break even on the operations each year based on planned variable and fixed costs.

There is also a returns page that compares leverage vs. non-leverage i.e. if you plan on all cash financing or a bank loan or some combination. On that is IRR, ROI, annualized ROI%/$, and months to break even leveraged/non-leveraged.

Finally, I also added a discounted cash flow valuation. It takes the net cash flows and gives them a present value based on inputted discount rate and exit multiple (based on revenue or EBITDA).

No comments:

Post a Comment