E-commerce Financial Model

By taking into account key assumptions as traffic from different sources, conversion rates, and the average purchase value for different product categories, and the combination of fixed and variable costs, the model generates the three financial statements as well as the relevant metrics for an e-commerce business.

The model also allows the user to develop scenarios based on the variation of key metrics, namely conversion rates, average order, and operational costs variation. After modeling the scenarios, the user can make a comparison of different scenarios side-by-side.

The template is monthly in nature, and it is a 100% unlocked Excel file with fully transparent formulas that can be further tailored to suit your particular needs.

The user only needs to input information into the cells formatted in the dark blue font in the Assumptions sheet, any other formats are formulas or labels.

For demonstration purposes, the template is provided with information from a hypothetical business model for an online drug store. The data must be erased/adjusted to fit the user's needs.

The maximum horizon for this model is six (6) years;

Model Structure:



Intermediate Sheets (calculation sheets):

• Revenue Model: The revenue is projected in this sheet using an easy to follow logic (Traffic --> conversion rates --->, Avg order value ---> revenue);

• Staff: Calculation of Wages & salaries based on the hiring plan set in the assumptions sheets,

• Debt Model: The user can model up three loans and a revolver facility to cover any cash shortfall (the inputs for the debt are all inputted in assumptions sheets)

• Capex: In this sheet, based on the inputs set in the assumptions sheets, the investment and depreciation are calculated on a monthly basis;

• Working Capital: Calculation of the working capital requirement by using the collection, payment, and duration of storage period assumptions.

Outputs:

• Executive Summary: Summary of the main KPIs, main drivers and Dynamic Charts

• Cases Overview: In this sheet, the user can analyze all the scenarios side-by-side (the user must run the macro to update the scenarios)

• Yearly Financial Statements: Yearly P&L, Balance sheet, and cash flow

• Monthly Financial Statements: Monthly P&L, Balance sheet, and cash flow

• Cases Overview: In this sheet, the user can analyze all the scenarios side-by-side (the user must run the macro to update the scenarios)