Online Store Financial Model: Free Excel Template
The BusinessPlanHub ecommerce financial model is a free Excel download that turns traffic, conversion and repeat purchases into a 60-month income statement, balance sheet and cash flow for an online store.
How does an online store financial model work?
An online store model turns visitors into orders, and orders into revenue and cash. Everything downstream depends on four inputs: traffic, conversion rate, orders per customer and average order value.
The Assumptions tab holds those inputs. The Financials tab builds an income statement, a balance sheet and a statement of cash flows from them, month by month, for 60 months.
Here is the chain, in the order the file calculates it.
| Step | Inputs you set | What it produces |
|---|---|---|
| Visitors arrive | Traffic, days selling, new leads per day | Monthly leads |
| Visitors buy | Conversion rate | New customers |
| Customers come back | Returning customer rate | Returning customers, total customers |
| Customers order | Average monthly orders per customer | Total orders |
| Orders earn | Average order value | Revenue |
| Orders cost | Product cost, payment processing | Gross profit |
Change one input and the whole chain moves. That is the point.
Which ecommerce assumptions will a lender or investor question first?
Expect questions on conversion rate, repeat rate and customer acquisition cost. Each one has a large effect on the result and each one is easy to inflate.
Conversion rate sits at the top of the chain. A small change compounds through every month. If you have no sales history, say where the figure comes from, whether that is a similar store, a pilot or a paid test.
Repeat rate is next. The file asks for a returning customer rate and an average monthly orders per customer. A lender will ask whether you have the data to justify them. If your store is new, start low and let the rate rise only as your own results come in.
Acquisition cost follows. Paid ads work from a monthly budget and an average cost per click. Read how a reviewer reads it: “If I double orders, does ad spend double too?” If your answer is no, explain why.
For what the people reading your numbers look for, see what investors look for in a business plan and what SBA lenders look for.
What are the most common mistakes in an ecommerce forecast?
Most mistakes make the store look more profitable than it is. Check your draft against these.
- Repeat customers from month one. A store with no history has no returning customers yet. Start the returning rate at or near zero and build it up.
- Ad spend that stays flat while orders grow. Paid traffic usually has to scale with volume. Link your budget to the orders you want.
- Gross order value treated as net. Returns and refunds come out of what you keep. The file has no separate refunds line, so reduce average order value or add a row.
- Product cost left as a guess. Product cost is per order here. Include shipping to the customer and packaging if you pay for them, or your margin is overstated.
- No cash for stock. Inventory is paid for before it sells. The file has inventory and days payable inputs, so use them. A store can show a profit and still run out of cash.
- Placeholders left in. Example numbers that survive into a shared copy undermine everything around them.
How do I fill in the template?
Work left to right across the tabs, and finish a tab before you open the next.
- Overview. Start on this tab, then move to Assumptions.
- Assumptions, acquisition. The file has ten acquisition channels. Fill in the ones you will really use and leave the rest at zero.
- Assumptions, orders. Set conversion rate, returning customer rate, orders per customer and average order value.
- Assumptions, costs. Enter product cost, payment processing, G&A, sales and marketing, professional fees, and the 25-row people plan.
- Assumptions, funding. Add starting cash, fixed assets, inventory and any equity rounds, plus your income tax rate.
- Financials. Read the three statements. Does the cash balance ever go negative? If it does, that is your funding gap.
Where does this model stop?
It models one business engine: online orders. It does not model marketplace fees, wholesale accounts or subscription billing. If you sell a branded product with a subscribe-and-save option, use the CPG financial model, which runs the same engine with a subscription line. If you take a cut of other sellers’ sales, use the marketplace model.
Want someone to check your numbers?
A free 30-minute consultation is the quickest way to test them. Book a free consultation and we will go through your assumptions with you.
When the model is done, it feeds the financial section of a full plan. Our business plan template and the retail and ecommerce sample plans show where it goes. Sample plans use invented figures, so treat them as structure, not as results.
Sample business plans that fit this model
These are 6 of 8 sample plans for businesses that earn money the way this model does. Every sample plan is a fictional example, so use it for structure, not for figures.
- Amazon FBA Business Plan
- E Commerce Retailer Business Plan
- E Commerce Start Up Business Plan
- E-Commerce Internet Business Plan
- Ecommerce Fabric Store Business Plan
- eCommerce Office Supplies Business Plan
Browse more: Retail & eCommerce business plans
Ecommerce financial model: common questions
Can I use this ecommerce financial model in Google Sheets?
Yes. Open the .xlsx in Excel, or import it into Google Sheets with File > Import. Check the Financials tab afterwards to confirm every formula carried over.
Does the template include returns, refunds and payment fees?
It has a payment processing line, but no dedicated refunds line. Either lower your average order value to a net figure or add a row yourself, and say which you chose.
How is this different from the CPG model?
The two run the same engine. This one is framed for stores selling many products from one catalogue, while the CPG model adds a subscription revenue line for a single product brand.
Are the numbers in the file real benchmarks?
No. They are placeholders. Put in your own traffic, conversion, order value and product cost before you rely on any output.