Build a Financial Model for Your B2C Subscription Startup

Learn how to build a full financial model for an e-commerce startup (subscription-based business) in Excel in this case study-style tutorial. Also included is a downloadable template of the model.
📌 Don't miss the new series: The ULTIMATE Subscription Box Financial Model | Part 1: • The ULTIMATE Subscript...
✅ Download the Excel template: www.ericandrewsstartups.com/o...
🚀 If you want to master the finance skills & frameworks to successfully scale technology startups, secure your spot in my "Finance for Startups" program, today: www.ericandrewsstartups.com/f...
Communities:
💼 Linkedin: / eric-andrews-1624b656
☑️ Twitter/X: / eric__andrews
🌟 Instagram: / ericandrews_startups
🔥 Discord: bit.ly/discord_eric
💻 Newsletter: bit.ly/joinericnewsletter
🎥 Clips: / @ericandrewsclips7818
Related Startup Videos:
🚩 • 3 Ways to Increase LTV...
🚩 • How to Calculate Unit ...
🚩 • Boost Your eCommerce L...
🚩 Playlist: • Finance for eCommerce
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
In this video, we look at how to build a financial model from scratch for a subscription-based coffee delivery startup in Excel (would also apply to a SaaS company or any subscription business model). This is the type of model that venture capital funds would want to understand before investing in a company.
One key point in building startup financial models is that we need to build the simplest and most flexible model we can (so that we can easily update it), while still building a model robust enough to model out the reality of our revenue, expenses, etc. This is difficult but you'll see that if we organize our assumptions well (with good color coding and formula writing), we can create something simple that we can use as a tool to adjust as our forecasts inevitably change.
Through building this financial model, we're able to examine the profit margins, breakeven point, burn rate, and the cash runway this eCommerce startup has until it needs to raise more venture funding. This type of model would serve as a good internal budgeting model as well as an external model to sound out to potential investors during fundraising.
Sections:
0:30 Discussion around company business model, current fundraising status, and why we need to build the model
1:06 Customer acquisition model (tab) setup & formatting
2:00 Which metric to start the model with and build off of (marketing)
2:55 Customer acquisition costs (CAC) and calculating orders from advertising budget
5:20 How to forecast new and recurring orders for a subscription business
6:28 Churn rates and how we build them into our subscription startup model
8:48 Starting and formatting the full monthly income statement model
9:36 Assumptions for revenue
11:37 Assumptions for the cost of goods sold (COGS)
14:49 Assumptions for a quick & simple payroll model (and general operating expenses)
19:55 Assumptions for other operating expenses
20:19 Why learning to use Excel without the mouse is important & how to improve
23:24 Revenue section & formulas of the income statement model
26:55 COGS & gross profit section of the income statement
27:28 Discussion (excel tip) on locked cell references and how to utilize them to improve formulas
29:19 Operating expenses (including payroll model) section of the income statement
35:43 Operating income and finalizing the income statement
36:44 Adjusting assumptions for the business model
37:40 Creating the overall quarterly rollup summary income statement (tab)
40:36 Discussion of what "runway" is in venture capital and how to use this model to manage cash burn
41:58 Double checking the model
42:55 Case study style discussion of business model, cash positions, and analysis of future prospects
My name is Eric and I've worked for seven years in Silicon Valley in finance. This is the type of financial model that I build for eCommerce startups, but would really work for any small businesses. I decided to publish it on KZbin because I believe it is the type of knowledge we should democratize, and that I would have greatly benefitted from as I started my career.
► Subscribe for more finance videos: bit.ly/EricAndrewsSubscribe
#financialmodel #startups #ecommerce #subscriptionbusiness

[@dhivyarao6943]

Hi Eric, I am a graduate student who is trying to learn various business and financial models. I came across your channel a few days back and I can't tell you how much of a treasure it is. Thank you for helping me learn what financial modeling looks like in real-time scenarios. This is so useful for students and freshers who aspire to get real-time experience in modelling.More power to you ! Looking forward for many more amazing contents.

[@eric_andrews]

Hi Dhivya - it really means a lot to me to hear your story and that these videos have been really valuable for you. I'll keep trying to democratize this knowledge for all people! Thanks for everything.

[@jaymarcase9737]

You’re the man...simple, concise, and not bland.

[@eric_andrews]

I really appreciate that Jay. I think this stuff is interesting and it sounds like you do too! Thanks for checking it out

[@shamanthpereira6880]

Eric you are brilliant! Thank you sharing this and making it easy for other to create simple models !!

[@allisonp2198]

Thank you Eric from the bottom of my heart! I needed a good format in a short period of time to lay out a financial model. I finally ran across your example and it got me well on my way. I look forward to watching your more detailed model videos soon. Your work is well-planned, concise, and clear. Thank you so much!

[@eric_andrews]

Hey really appreciate that feedback so glad my content has been valuable for you. Cheers

[@danielm3977]

Just love it, very practical, No B.s. Thank you Eric.!Pure value. Subscribed!

[@eric_andrews]

That's awesome! Thanks for the subscribe and really glad you found it valuable daniel

[@flywheelIterator]

Thanks Eric! You uncovered the magic of calculating if business is good or bad to invest!

[@eric_andrews]

My pleasure!

[@stephenyoung3917]

Hi Eric ! This video was amazing. One thousand thanks to your excellent work. Well done !

[@duncanmahood2878]

Thanks Eric this is really helpful! I've been trying to find this kind of financial modeling info for my side-gig forever. Great explanations 👍🏻

[@eric_andrews]

My pleasure! Glad it was useful and let me know if you need anything else.

[@kylegrabowski8473]

Awesome model Eric! Super helpful case study. This is way better than looking at someone's already-built model and trying to figure out how it fits together, or how to start. Thanks!

[@eric_andrews]

Hey Kyle - really appreciate that feedback. I'm glad the "starting from an empty sheet" helped you understand the order / mechanics of these types of models. Cheers.

[@Ole10451]

Simple and usable. Thank you very much Eric!

[@eric_andrews]

My pleasure Olefile! Happy it was valuable for you

[@samsquamsh78]

Great videos Eric, I've subscribed. Great job and hope to see more content from you!

[@eric_andrews]

Thank you!!! Much more to come this year, I appreciate the support and glad it was valuable.

[@silkyagarwal1634]

Thanks Eric for wonderfully explaining the model from scratch. It is very helpful

[@eric_andrews]

Very welcome Silky, thanks for checking it out!

[@sophiagrande8966]

I enjoyed your other video on building a 3 stmt fin model, and purchasing the bundle of classes now. Very useful stuff that can be used everyday, thank you!

[@eric_andrews]

Sophia ! Thanks so much for your support! So glad you found my content valuable, its my pleasure to help 😀😀

[@marynakhomenko1369]

Thank you for such a clear and valuable example!

[@eric_andrews]

Thanks for the feedback! Really happy you found it valuable Maryna!

[@osgooddesign8882]

Awesome video. I learned a ton that actually will apply well to my small woodworking business

[@eric_andrews]

I'm really happy it was helpful and applies to your business as well. Thanks for the note!

[@msjamali52]

This is good Eric. Straight to the point, very well explained.

[@eric_andrews]

My pleasure

[@AbdulAzeez-hk4zk]

Thank you so much Mr. Eric.. It is very useful content presented in a very simplified way.

[@eric_andrews]

My pleasure. Really glad it helped you Abdul.

[@bocobox]

Excellent 👍 this is the stuff I am looking for. Rathe than excel function. Keep it up and looking forward to next one.

[@eric_andrews]

I really appreciate you letting me know! I'm working on some more business analysis & modeling style videos right now, and will be releasing them soon. Thanks for the comment 👍😀

[@joeleung383]

Great video, extremely helpful!!

[@eric_andrews]

Happy to help Joe

[@mdashiquzzaman4358]

Very helpful.....indeed

[@dougsm7158]

Great presentation and easy to understand, been trying to build a professional financial model this has made my life a lot easier.

[@eric_andrews]

I'm really glad to hear that, thanks for letting me know. Good luck

[@pavankumarpurohit5874]

This was really helpfull and important

[@eric_andrews]

Pavankumar - really appreciate that. Thanks for the comment.

[@kevinvo9771]

thank you Eric, I'm a tech guy but can understand your content easily! It's really helpful for me as a non financial person

[@eric_andrews]

Awesome to hear it, the more we all understand about what is going on the better we can all build together 😎

[@suhailahmadh9057]

Thank u very much brother ERIC

[@eric_andrews]

You are very welcome ! 🙌

[@Mr-nj8wg]

This was so helpful and a great learning experience. I learned how to formulate a financial forcast as well as a new skill in Microsoft excel👍 if i was able to give more than one like id be spamming

[@eric_andrews]

I'm so happy this was valuable for you! Thanks for letting me know!! I really appreciate the like 🙂🙂

[@AnkurJyotiSharma]

Eric your contents are a treasure . It has helped me immensely . Do you mind explaining us how to add 2-3 revenue streams data into one file . For example , in addition to subscription , if the company earns from a marketplace model or add from revenue in their app , how would they add those in their model ?

[@eric_andrews]

I'm happy to hear that. In those scenarios, you would just create a separate section & forecast for each type of revenue & COGS. In your consolidated model, you would probably keep each type of revenue & COGS in it's own account, but just consolidate them all into the same model. Here is an example of me doing that in one of my other videos: kzbin.info/www/bejne/i3PWfGNtpNZkg6ssi=Sk8sk_sPbVBsLzhW

[@dvabhil]

Subscribed

[@eric_andrews]

🙏🙏🙏

[@ravikapoor9123]

hey eric, please give the exact downloadable teemplate because in the income statement assumptions, there are little variations as compared your tutorial.

[@vinayaknayak9270]

Hey Eric, very informative video. Thanks for sharing this. I have a question, why didn’t you account for CLTV in this sheet? Or is CLTV a function of CAC which you have explained in your other video?

[@eric_andrews]

Hey Vinayak - yep, I cover the calculation and the LTV vs CAC relationship in two other specific videos. Here they are: Customer retention & LTV for all business models: kzbin.info/www/bejne/haimcoeAnZmfhMk LTV for subscription businesses: kzbin.info/www/bejne/m3nMaWprhtqJmaM

[@GrayWolff]

This was amazing. No BS nothing fancy. Thank you Eric :) Also, are you planning on helping us with financial models on merger & acquisitions, advanced valuations, Leveraged Buyout LBO Modeling?

[@eric_andrews]

Hey Muhammad - I'm so happy to hear that! Yes I plan to release many videos around different subjects of more advanced financial modeling this year, although my real passion is tech / venture rather than PE 🙂. This week I'm going to release a lesson on building a full 3 statement financial model (income statement, balance sheet, and cash flow). Make sure you are subscribed I think it will be helpful!

[@GrayWolff]

@@eric_andrews looking forward to watching it.

[@eric_andrews]

@@GrayWolff Thanks! Published it a few days ago! kzbin.info/www/bejne/rp27dY2vj56UsM0

[@fabiocorradini1125]

Hi Eric, I really liked the way you explain this financial model. Would I have a chance to use this model for a restaurant business? Or it wouldn't fit?

[@eric_andrews]

Hi Fabio - I think you could use this model as a starting point, although you would probably need to change the revenue & cost model for how a restaurant works. Customers probably return to restaurants at a certain return rate each month, so I think you could use these concepts to build a business plan. Thanks for the comment!

[@NYCGirl39]

Very helpful video Eric. One follow up question: how do you account for organic / word of mouth marketing, especially initially / at launch? If the initial customer acquisition is based on viral spread or word of mouth rather than $ spent, would you maintain that as a separate line item in the customer acquisition tab and use the same churn, spend, etc assumptions as paid acquisition customers?

[@eric_andrews]

Ya, it's a good question. Most startups actually drive 40% ish percent of their revenue from organic sales (obviously completely depends on the company / situation), but that's what I've seen across some different companies (could be from social media following, PR, SEO, email, etc). I would build a separate line item for that on your acquisition page, with its own assumptions. You could also just model your CAC going down over time as a BLENDED CAC (rather than Paid CAC), meaning a mix of paid and organic new clients, to show the proportion of organic going up thus driving marketing costs down. Either way would be fine. Great question.

[@peterojo432]

Many thanks Eric for the good job you are doing. In this model, how do we treat Setup (one-off) cost?

[@eric_andrews]

Hey Peter, I would just take your "new customers" and make a separate revenue line with a one time cost where you just calculate new customers * one time setup cost ($) for each month. That would probably work.

[@soulmansaul]

I know this is based on a subscription based model... but what about a standard e-commerce store where we buy the products up front? Also I'd like to factor in a drop-shipping based model too. 1) How do we factor in inventory purchases for a standard retail / e-commerce store environment? - If I pay shipping on importing those inventory purchases, is that an expense or a cost of good? 2) Do you have a formula for inventory management and planning? How much should I purchase to increase sales? 3) Where do we look to increase financing + adding loan liabilities? 4) Why is there no sales tax / VAT? How can we factor this in? Thanks again for the great videos… excellent help.

[@eric_andrews]

Hey Saul - will do my best to help. In your situation you could do one of two options - (A) just put your shipping and inventory expenses into your P&L, and then record the revenue later on when you sell it, or (B) build a balance sheet and cash flow statement because the business would be investing up-front cash to buy and hold holding significant assets (inventory) for longer periods of time and then selling them off. For that reason, there would be a big different between your P&L model and your actual cash position, hopefully that makes sense. I would do A if it was just my own small business and was trying to figure out the math, but B would be more so outside people could understand it. 1 - that's all cost of goods sold. anything that touches the product (other than marketing) is cost of goods sold 2 - there is no formula, you just want to make sure that you are forecasting your sales (units sold) out far enough that you have time to manufacture and import your product. So, it might take 6 months to get product into the country, so you will want to see how many units you plan to sell over the next 6 months to make sure you have enough inventory. that's basically the main value of a financial model, to anticipate your needs so you can prepare 3 - that's all on the balance sheet. you probably need to build a 3 statement financial model. usually businesses that build up large amounts of inventory use revolving lines of credit or credit cards. you'll make sure you are forecasting both the value of your inventory and your outstanding debt each month to make sure you are managing your liquidity well. 4 - many businesses have no sales tax or VAT. it depends on the type of product & country. I would include those things in revenue. hope that helps

[@vassantaidmont3650]

Hi, great video. Could you provide info on how you dragged the formulas over the several months?--- beginner´s level here :)

[@eric_andrews]

Happy to help. Yes, you can either do with with the keyboard, copy the cell with the formula you want (control + C) and highlight the cells you want to paste the formula and then past (control + V). You can also highlight the area and go to the bottom right highlighted cell and when your cursor makes that tiny black cross click and hold down and then drag the formula. Maybe check out some of my Excel videos as well, here's a good starting point: kzbin.info/www/bejne/m5e7aqhrnbqIj5o

[@bellbellbell00]

Thanks for the really helpful video Eric. I'm building an ecommerce business that has both one time purchase and subscription models, would you then build in an additional set of assumptions in the customer acquisition model with say a lower CAC but higher churn rate and then combine the two in the monthly model?

[@eric_andrews]

Hey David, yeah that's exactly what I would do. I would just build them as separate product lines with separate CPAs / marketing budgets and separate revenue streams. One thing to note though is many companies use a one time sample product to try to get people to convert into the subscription, so you might have some sort of conversion from one time -> subscription customers. Again I don't know the specifics of your model but something to think about.

[@angelmaravilla4708]

Hay David!! I hope you've had a massive success with your project!! I'd like to ask you a quick doubt: How do you make the revenue recognition with your two different sales types?? I mean, with "suscriptions products" you use the MRR (monthly recurring revenue), but with your "one time sales" products; do you incorporate those sales in your MRR too, or how do you record and track those sales?? Hope you can give some advice 🙌

[@cbfnm]

This was insanely helpful you have no idea! I so look forward to your new videos. I have a question though, regarding CAC, it's so difficult to estimate this value before launching your business. Since it's kind of the backbone of building this model , can you replace it with something else?

[@eric_andrews]

Great! No, I would not advise replacing this metric. If you haven't launched the business yet, I would do the math to try to estimate your customer lifetime value (I have a video => kzbin.info/www/bejne/m3nMaWprhtqJmaM). Once you know what that is, you will know what your maximum CAC can be in order to run the business profitably. You can put in some CAC assumptions that are lower than your LTV. Once you know what your maximum CAC is, you know the target your business must hit or do better than. CAC and LTV are the backbone of any business, whether you've launched them or not. Cheers.

[@cbfnm]

@@eric_andrews I actually just finished building the whole thing and woow I'm impressed! Will definitely check the video. Thanks again and best of luck.

[@eric_andrews]

@@cbfnm awesome!! 👍👍👍👍👍

[@deedai6019]

how about if I have 2 or 3 price options for Subscription

[@vinodhakarthik8485]

Eric, amazing stuff. I just subscribed to your channel. I am returning to workforce after 5 years of break. Very nervous to attend interview as i feel outdated. your videos boost confidence. Thank you. Would you have a sample for building finance model for supporting the marketing and sales teams who are responsible for generating the majority of revenue via ads.

[@eric_andrews]

It's my pleasure Vinodha. Don't worry, you can do this! Review my video on "customer acquisition cost calcuation", that will help you start understanding how finance can support marketing and also give better forecasts. You can use the market ad spend budget, divide it by CPA (to get units sold), and then use units sold to calculate revenue. That's the best way to calculate revenue !! kzbin.info/www/bejne/boimmaCHqrmYhJI

[@vinodhakarthik8485]

@@eric_andrews Thank you Eric

[@eric_andrews]

@@vinodhakarthik8485 happy to help

[@baptistelapuyade4876]

Hello Eric, the download link for the template doesn´t work anymore. Thanks!

[@emmanuelolasehinwa7156]

Thank you Eric, with this video tutorial l really learn from you on how to make complex forecast models simple. I work on program finance and budget for an IP R&D coy in Silicon valley. I need help on how to model long term IP rev recognition. Please l need help in this area. Please can l send you my contact so you can put me through?

[@zeenatamber3038]

HI ERIC, plz also share templates with us when you upload a vedio for us for teaching us financial models

[@eric_andrews]

Hey Zeenat - you can download templates to all my financial models in the descriptions below the videos!

[@Ps-ng2jy]

Hi ERIC,I see your model ,it is very much appreciated and I learn more new things through this model and I also see your basic model, appreciated, appreciated,now can you upload something other basic model, Warm regards Pushpendra

[@eric_andrews]

Hi Pushpendra, here are some of my other modeling videos you can take a look at! => Building a 3 statement financial model kzbin.info/www/bejne/rp27dY2vj56UsM0 => E-commerce financial model kzbin.info/www/bejne/mKTQqpShrbSDgrs

[@Ps-ng2jy]

Hi ERIC,This is pushpendra here,I have very basic knowledge about Excel,so that if you upload some besic model on 3 financial statement then it will be helpful for me. Thanks and Warm regards Pushpendra

[@deeptisharma4272]

Hi Eric ..loved it . Thank u so much for sharing such great work . Just wanted ask one thing .. what about the balance n cash flow statment or is it provided in other video by u .. if yes, please share the link.

[@eric_andrews]

Hey Deepti - thanks for the note, happy it was helpful. Yes I have another video on building the balance sheet & cash flow here => kzbin.info/www/bejne/rp27dY2vj56UsM0

[@deeptisharma4272]

@@eric_andrews hey thank you so much replying back . Alredy been through this video .. I thought u had sperate continue video for this ecommerce fin model on bal sheet n CF. Thank you .. alwys enjoy ur channel . Subscribed ❤️

[@eric_andrews]

@@deeptisharma4272 Hey - great to hear, thank you!!! Yes, the reason I didn't build the the BS & CF for the ecomm model was that most early stage startups have basically no assets and no debt so they just use an income statement with "burn" at the bottom and a cash balance to manage things. Later on when they start to buy more things or have a more complex business the balance sheet and cash flow become more necessary, but most businesses (in my experience) doing under around ~$2MM USD / month in revenue don't need the full 3 statement model, just the simplified version I covered in this video. Hope that helps!

[@deeptisharma4272]

@@eric_andrews yes thanks alot 😊❤️

[@gujames1985]

Question: Aren't COGS intended to include all COGS (even for refunded transactions)? Aren't those cost sunk after the transaction occurs? That said this is super helpful. I always pull these up for my team

[@eric_andrews]

Yep, totally. Didn't really cover that in this video but the extra COGS would just hit normally and you'd have the revenue reversed out at the top as "refunds", depends on the business model in some cases can be even 5% of revenue+. One thing I recommend is offering buy one get one free (or other bundles) or discounts where you say "no refunds", can help a lot.

[@abhinavpandey3022]

Make a forecast for software startup with no history sales revenue, but it had Incurred fixed and variable expenses

[@eric_andrews]

You haven't looked around my channel yet I can see...you're in for a surprise 😎

[@AnkurJyotiSharma]

Eric how to calculate the EBITDA and valuations from these details ? Can you help us with the same

[@eric_andrews]

EBITDA is just your operating profit + depreciation added back to it. In this video we didn't have any depreciation, so the EBITDA would be the same as your operating income. Valuations depend a lot on the business factors (% of revenue that is recurring, gross profit margins, growth rate, etc), but generally they are based on a multiple of profit (EBITDA or net income). The multiples depend on the business. A lot of businesses are valued in the 10-30X EBITDA range. I dive deeper into the multiples-valuation method in this video: kzbin.info/www/bejne/pHXEXpugqb12aa8si=40Gm3bVTVSPwR9hq

[@RainingCharmandersTV]

Customer service would go up as you scale - so would fulfillment usually

[@eric_andrews]

Good points Sappy - glad you are thinking about this in a real context. These models are just meant to be frameworks people can use to learn from and adapt, just like you're doing! Hope it was helpful!

[@Yan34452]

Hi eric thank you for this . But i have to say it , the link to the spreadsheet is labeled as dangerous , i guess it is the ssl of the website

[@eric_andrews]

Thanks for letting me know, I will look into that. Hopefully, it still worked for you.

[@armandc1524]

What does benefits really mean in the "personal cost" i don't get it. Why 25% and why should we consider it? If someone can help me. Much appreciated.

[@eric_andrews]

Healthcare insurance & payroll taxes basically.

[@MrCrystello]

Good spreadsheet, terrible business!

[@eric_andrews]

Maybe as a mature business, but in the venture world we are building brand new companies that lose money in order to scale a hyper-speed, this is easily a $200 million valuation business at the end of year 4! Probably at a 10X revenue multiple which would be reasonable, and maybe even conservative