E-Commerce Business Financial Model Excel Template by Henry Sheykin - Financial Models https://finmodelslab.com/ Last version published: 08/05/2019
COVER PAGE
The cover page is the name of your business project, company or Food Truck name. This name is put in a yellow cell and appears on every file page afterward.
2
TIME SERIES INPUTS Financial year end in the model - input the last month of the financial year. If you don't need to assume for the financial years, simply type in Dec. First Financial Year in the model - input the very first financial year you want to start from. If you have entered Dec in the Financial year end in the model, then, your calendar year equals the financial year. Model Currency - insert the currency sign or type in USD/EUR or any other abbreviation Denomination - use denomination if you want
3
Dashboard
4
Dashboard Start with the launch date and, corporate tax to see the correct calculations. Also, you have to enter the market or product name, launch date, and sales volume. Of course, you should input the average sales price per market/product to build the revenue forecast. Average PPC, % is a cost you will spend on PPC ads. Debt assumption - here you have the option to enter 3 debt. Inventory assumption - here you must input production time, a percentage of safety stock. And the last your input at this sheet is AR days, Pmnt upfront, and the share of Pmnt.
5
Seasonality Setup
To build the correct monthly output you need to enter the seasonality factor each month across the year.
6
Costs: Wages
7
The wages tab allows to easily calculate salaries for staff categories. Use 1 line per employee/category, and simply type in the hire date, fire date, and annual salary. Next, use the staff numbers section to type in FTE count for each year individually. An increase of FTEs means that you are going to hire additional FTE's. In case of decrease FTEs means you fired some FTEs. New FTEs will be hired/fired on Jan of the relevant year. If there are raises or changes in salaries, simply type in the annual Salary raise per position in the appropriate columns. The model will automatically calculate this cost going forward until the fire date. Finally, Monthly bonus as a % of monthly salary can be applied to every line as well as personal tax can be applied
Costs: COGS Expenses
The model is built to be easy to use to enter expenses. Simply enter in the list of expenses you want to forecast in the rows COGS expense (i.e. "cost of goods sold"). And type in the percentage of revenue to be spent or the cost per single unit sold
8
Costs: Variable Expenses
Enter in the list of variable expenses you want to forecast in the rows Variable expense. And type in the percentage of revenue to be spent. The average PPC percentage is grey and not allowed to change. This is the expense calculated by the model based on the PPC assumption you have entered on the Dashboard.
9
Costs: Fixed Expenses
10
Enter in the list of fixed expenses (i.e. ""running the business"") you want to forecast in the rows Fixed expense. You have multiple options of when to start to accrue the expense and when to end accruing. You may define periodicity of the accrue for each expense. It could be One-time, daily, weekly, bi-weekly, monthly, quarterly and etc.. This gives you the
Assets
11
You may purchase some assets on a specific date and depreciate it across typed in years using this tab.
Cap Table
Cap table require some manual manipulation to make accurate. The structure enables you to make the necessary modifications. The Cap Table model takes you through multiple rounds of financings. Showing how issued shares to new investors and cost per share impact the investment received. After each round, you will see the ownership, % changes. This is how dilution works.
12
Summary
The Summary sheet is a Summary of the key financial reports of the
13
Top Revenue
Here you can track how much money each category generates.
14
Top Expenses
Here you can track the biggest 4 expense categories ranked as a sum of 3 years and the "other" which is the rest of the expenses.
15
Valuation
The model can do valuation of distribution of current cash flow to Investors Discounted Cash Flow, and valuation based Perpetuity Growth method.
16
Break Even Analysis
Here you can track when your company is supposed to move to a whole new level - when its overall revenues become significantly bigger than expenses.
17
Burn chart
Burn tab is calculated as average cash balance divided by monthly average operating expenses to give you for reporting and planning. Burn ratio shows how much time operating expenses will burn an average cash balance.
18
Financial Charts
The key financial indexes are shown there. This tab focus on displaying information rather than creating forecasts. It summarizes information that is calculated elsewhere in the model.
19
Thank you very much for your interest in my tools! If you enjoyed using my model, please let people know about your experience! Give 5-star feedback here & share this page across social networks If you have any suggestions or ideas of how to make this model more useful, don't hesitate, just send me a PM - Henry Sheykin