**Update: you can still download the model if you want and see if there are any pieces you can use, but I am working on a new version that is a little more cash-oriented. Right now, it isn’t explicitly modeling out annual subscriptions but rather averaging a monthly revenue level, and I think that should be fixed.**

Mark Suster of GRP just wrote a post on the importance of financial models, and in an effort to be helpful to new entrepreneurs finding their way around Excel, I thought I would post a business model Excel template for anyone to download and customize (*bottom of post*). I completely agree with Suster. The one time I told myself “*I don’t need a financial model yet because it will all be made up bullshit*,” I completely embarrassed myself a few months later and swore never ever to make that mistake again (*seriously, it still hurts years later*).

Why must you create a financial model? Beyond the obvious of needing to speak your investor’s language, here are a few reasons:

- it helps you better understand your assumptions and goals
- it helps you better understand your business’ pressure points
- it forces you to better understand your scaling cost structures
- it forces you to examine the realism of milestones and cash needs
- it gives you a working document to track and structure/focus for your business analytics (like a product, it should be a continually iterative process and tool)
- it gets everyone on the same page as to what the business is shooting for and sets expectations as to what needs to be accomplished and how long it might take. EVEN IF IT IS GUESSWORK, you still need to make sure your team and investors are on the same page when it comes to assumptions, goals and timing!
- hopefully this one isn’t necessary, but it also creates a document everyone can sign off on, which can reduce finger-pointing later if memories become revisionist should things turn sour

Focus your efforts on a model that feels ambitious but doable, but don’t forget to create a conservative “oh shit” version, and if it has to be a sales document, you may need a “$best case ka-ching$” version as well.

I find that with every startup/product I model out, a lot of customization goes into the user acquisition/growth model, the revenue model, and to a slightly lesser extent, the infrastructure/hosting scaling model. I like building user growth from the bottom up, focusing on active users (someone who uses your product at least once a month). I don’t much like simplistic “viral multiples” or top-down approaches like market share penetration.

This particular model came out of an exercise for a **freemium** Web application startup that wanted to monetize through premium subscriptions. The model takes a “cohort” approach (*more information on cohort analysis over at Fred Wilson’s blog*) and applies churn and conversion assumptions to each month’s new users over time. It also allows you to model out the growth of your team by role and employee type (full time vs contractor). I have zeroed out most of the assumptions and the staffing plan so that you can start with a clean slate.

Every business is different and you’ll no doubt end up ripping some stuff out and adding some stuff in, but I hope the attached model is a useful starting point for some of you out there.

Here are just a few of the things you’ll need to research/guess if you are just starting out:

- comparables on user growth rates, virality, and churn
- customer acquisition / sales methods and costs
- price points and conversion rates for customers, given your business model and sector
- payment processing rates and fraud/chargeback levels
- contractor and salary rates (both “startup discounted” and market) for your hires
- customer support method and costs , and how it scales with customers
- true travel costs (often underestimated)
- expected concurrency rates and visit frequency
- download size per visit
- architecture scaling: owned vs managed hosting vs cloud and how servers and costs scale

Here are a few key things you want to examine out of your model:

- are there any variables where a slight variation makes a massive difference?
- are your fundraising milestones well matched to your product and financial milestones?
- when do you hit monthly breakeven?
- how many users do you need to have acquired / kept to get to breakeven?
- how much money have you spent in total to get to breakeven?
- are you adequately scaling costs to meet the demands of a large user base in later years?
- can you believe any of it! You can make a model spit out whatever numbers you want, so cross-check, second-guess, and don’t buy too deep into your own assumptions and guesses!

I’ve tried to pepper the model with notes to make it a little easier to understand. Please let me know your comments on the model, whether they be improvements, corrections, or just a note on if it helped.

This is an excellent post- and financial model- for a freemium service. It dovetails well with a lot of the high-level presentations on the topic of “freemium” by people like Chris Anderson. The detail you go into here though is really helpful for companies considering this model to attain revenue.

We summarized Chris Anderson’s discussion on Freemium biz models at this year’s startup weekend- you can find it at:

http://blog.recurly.com/2009/10/chris-anderson-on-why-freemium-models-work/

Freemium models also typically require subscription billing- which is solely what our company Recurly does. We can help you quickly take the pricing models you create here and turn them into subscription plans you can bill your customers with. Find out more at our site at http://recurly.com

This is an excellent post- and financial model- for a freemium service. It dovetails well with a lot of the high-level presentations on the topic of “freemium” by people like Chris Anderson. The detail you go into here though is really helpful for companies considering this model to attain revenue.

We summarized Chris Anderson's discussion on Freemium biz models at this year's startup weekend- you can find it at:

http://blog.recurly.com/2009/10/chris-anderson-…

Freemium models also typically require subscription billing- which is solely what our company Recurly does. We can help you quickly take the pricing models you create here and turn them into subscription plans you can bill your customers with. Find out more at our site at http://recurly.com

Great tool Giff, you could do an entire series of posts around each set of assumptions.

Great tool Giff, you could do an entire series of posts around each set of assumptions.

Kudos to you Giff- an EXCELLENT model- THANKS.

@justinpirie

Kudos to you Giff- an EXCELLENT model- THANKS.

@justinpirie

Great article!

Business modeling forces the business model designer or analyzer to identify key variables, make and verify important assumptions, test for different scenarios and by that understand the complexities of the business model and how different attributes and factors relate to each other.

When designing models I recommend the following process:

1. Identify the purpose with the business modeling and the outputs required.

2. Identify the input variables and major drivers of input variables that determine the outputs.

3. Develop formulas (logical arguments) for how outputs are derived from inputs.

4. Gather data and make assumptions.

5. Build the spreadsheet model.

6. Test the spreadsheet model.

7. Enter gathered data.

8. Test alternative scenarios.

9. Analyze the results.

10. Adjust the business model.

For more details about each step, see The Business Model Database: http://www.tbmdb.com

Great article!

Business modeling forces the business model designer or analyzer to identify key variables, make and verify important assumptions, test for different scenarios and by that understand the complexities of the business model and how different attributes and factors relate to each other.

When designing models I recommend the following process:

1. Identify the purpose with the business modeling and the outputs required.

2. Identify the input variables and major drivers of input variables that determine the outputs.

3. Develop formulas (logical arguments) for how outputs are derived from inputs.

4. Gather data and make assumptions.

5. Build the spreadsheet model.

6. Test the spreadsheet model.

7. Enter gathered data.

8. Test alternative scenarios.

9. Analyze the results.

10. Adjust the business model.

For more details about each step, see The Business Model Database: http://www.tbmdb.com

Mr. Constable and others,

I have a couple of questions about the mechanics in this spreadsheet.

1. In the Model tab the “User Churn” formula is this: =IF(ISNUMBER(OFFSET(AR$10,0,-$C14)*VLOOKUP($C14,ChurnTable,2,0)),(OFFSET(AR$10,0,-$C14)*VLOOKUP($C14,ChurnTable,2,0)),0)

What is this formula doing?

2. In the same model tab there is”premium subscribers” formula is:

=IF(ISNUMBER(OFFSET(AR$10,0,-$C66)*VLOOKUP($C66,ChurnTable,3,0)*VLOOKUP($C66,ConversionTable,2,0)),OFFSET(AR$10,0,-$C66)*VLOOKUP($C66,ChurnTable,3,0)*VLOOKUP($C66,ConversionTable,2,0))*1

The same question as before. What is this doing?

Mr. Constable and others,

I have a couple of questions about the mechanics in this spreadsheet.

1. In the Model tab the “User Churn” formula is this: =IF(ISNUMBER(OFFSET(AR$10,0,-$C14)*VLOOKUP($C14,ChurnTable,2,0)),(OFFSET(AR$10,0,-$C14)*VLOOKUP($C14,ChurnTable,2,0)),0)

What is this formula doing?

2. In the same model tab there is”premium subscribers” formula is:

=IF(ISNUMBER(OFFSET(AR$10,0,-$C66)*VLOOKUP($C66,ChurnTable,3,0)*VLOOKUP($C66,ConversionTable,2,0)),OFFSET(AR$10,0,-$C66)*VLOOKUP($C66,ChurnTable,3,0)*VLOOKUP($C66,ConversionTable,2,0))*1

The same question as before. What is this doing?

Hello Claudius,

Basically the offset function in excel allows you to change where the formula is really pointing to. In the case of user churn, what it wants to do is multiply the number of new users from X months ago by the number of users lost that month. It points up to row ten, and then offsets X number of months to the left to find the right number of new users. Then it does a VLOOKUP to the “churntable” array on the assumptions tab (which starts at C17) to pull the right loss % for the right month. The ISNUMBER function is just error checking.

It seems a bit complicated a first, but go into Excel help and look up the offset and vlookup functions and it will make more sense. “Churntable” is a named range of cells, so if you are new to excel you’ll want to look that up as well.

Hello Claudius,

Basically the offset function in excel allows you to change where the formula is really pointing to. In the case of user churn, what it wants to do is multiply the number of new users from X months ago by the number of users lost that month. It points up to row ten, and then offsets X number of months to the left to find the right number of new users. Then it does a VLOOKUP to the “churntable” array on the assumptions tab (which starts at C17) to pull the right loss % for the right month. The ISNUMBER function is just error checking.

It seems a bit complicated a first, but go into Excel help and look up the offset and vlookup functions and it will make more sense. “Churntable” is a named range of cells, so if you are new to excel you'll want to look that up as well.

Pingback: Keeping It Simple: Unit Economics — giffconstable.com

Hi Giff,

Quick question on the model: On the “Model” tab, I don’t understand the logic on row 163 (PayProc Fees). Here’s your math:

(total paying subs x avg monthly rev per sub) x payproc % fees x (% monthly subs + % annual subs / 12)

Everything makes sense to me except the last part (% monthly subs + % annual subs / 12). What’s the logic behind that calc? Am I missing something in the minutiae of payment processing land?

Thanks!

Hi Giff,

Quick question on the model: On the “Model” tab, I don't understand the logic on row 163 (PayProc Fees). Here's your math:

(total paying subs x avg monthly rev per sub) x payproc % fees x (% monthly subs + % annual subs / 12)

Everything makes sense to me except the last part (% monthly subs + % annual subs / 12). What's the logic behind that calc? Am I missing something in the minutiae of payment processing land?

Thanks!

hmm, I need to look at this again to see if you’ve spotted a bug. The model needs to apply a transaction processing fee only when it’s literally processed. Thus annual and monthly subscribers need to be appropriately modeled out.

Been a while since I looked at this, so I’ll take a look this weekend and update the model if it needs to be fixed. Good catch if so!

hmm, I need to look at this again to see if you've spotted a bug. The model needs to apply a transaction processing fee only when it's literally processed. Thus annual and monthly subscribers need to be appropriately modeled out.

Been a while since I looked at this, so I'll take a look this weekend and update the model if it needs to be fixed. Good catch if so!

Thanks Giff for a great xls model.nnI’d be very interested in data/thoughts around retention rates which obviously is one of the core metrics for any consumer service and varies significantly forn- utility-based services (like Evernote) ton- social networking (FB, MySpace <- mentioned ~60% 30-day retention rate) ton- entertainment fads (chatroulette?)nnThanksnnNiels

Thanks Giff for a great xls model.

I’d be very interested in data/thoughts around retention rates which obviously is one of the core metrics for any consumer service and varies significantly for

– utility-based services (like Evernote) to

– social networking (FB, MySpace <- mentioned ~60% 30-day retention rate) to

– entertainment fads (chatroulette?)

Thanks

Niels

Thanks Giff for a great xls model.

I'd be very interested in data/thoughts around retention rates which obviously is one of the core metrics for any consumer service and varies significantly for

– utility-based services (like Evernote) to

– social networking (FB, MySpace <- mentioned ~60% 30-day retention rate) to

– entertainment fads (chatroulette?)

Thanks

Niels

Did you ever update this?

Pingback: Four Steps to Building the Perfect Financial Model | 24 Ways To Start

What you posted is what I call information philanthropy. Thank you very much.

My question;

– Do you have any updates on this or say if you ever had a chance to take a look at this after 2 years?

Also, good luck with your venture.

Pingback: How good is this freemium financial modeling template? - Quora

Thanks for this great template to start with .. nn

thank you for the info…this is really helpful!

Thank you Giff!

This is exactly what I was looking for. It saved me a lot of hours. Even though I have 10k+ hours of excellence in financial modelling, I couldn’t have done it better myself. Web based application services is new to me.

Please, let me know if you have any updates. I am in the process of writing a new business plan.

Best,

Peter

This is hugely valuable to entrepreneurs. Many many thanks for sharing!

Giff, thanks for the sharing this amazing work.

My question is regarding to churn and convertion tables which we say there are different patterns for games and applications. So, the one on this document is based setup for which one?

Regards

Joni

Thanks! this is awesome!

Thanks a lot. Please say if there is any update

Hi Giff, this is is great model! Do you have any updates of the model? Trying to model a new venture I’m thinking of and have minimal clue about how to model subscription based websites.

Thanks for these Excel sheets. They are very helpful.

Thank you for the give away, the model is very good !

This is fantastic! Thank you!

Thanks! This is great!