E-commerce Cosmetics Firm - Financial Model in Excel

Description
E-commerce Cosmetics Firm - Financial Model in Excel
More and more products are sold online. This is also true when it comes to cosmetics as a part of the direct-to-consumer movement. In this model, you can see how you can model e-commerce for consumer goods. This is an annual model. By altering the cells in blue you can adjust the model to your needs.
A few information about the model
1. Transactions. In sheet Sales and Margin, we start by modeling the number of transactions based on the number of visits and conversion rate.
2. Sales & Margin. In the sheet Sales & Margin, we model revenues, gross margin, cost of acquisition, retention, logistics costs.
3. Capex. Capex is calculated in sheet Capex, based on the number of customers we have to service. We have2 main types of Capex for Content: Acquisition Capex and Retention Capex. In a separate sheet (Depreciation) we model the Depreciation by cohorts and Net Value of Assets.
4. Head Office costs. These costs are calculated in the sheet HQ.
5. Profit and Loss statement. Based on data from previous sheets in the sheet P&L we generate the Profit and Loss statement.
6. Working Capital. Working Capital is calculated in the sheet Work Cap, based on sales and conversion periods assumed for inventory, receivables, and payables.
7. Debt. We assume that the growth will be partially financed by Debt. In this sheet, we calculate the Debt at the end of the year based on assumed loans that we take and repayment of the loans. We also calculate here the financial costs and profits.
8. Cash Flow. Finally, in the sheet CF, we calculate the cash flow based on data from previous sheets

You can adjust the model to your needs by altering the cells in blue, where you can find the main assumptions. The whole model will recalculate after you change the assumptions to fit your needs.