Advanced yet simple to use Excel Spreadsheet for Business Model calculations

Hi BM experts!

 

I am thinking of creating an advanced yet simple to use (Excel) spreadsheet to do Business Model calculations. Something as easy to use as the BMG iPad app, but then in a spreadsheet you can use on your desktop. And with more advanced features and customizable by you if needed.

 

I have looked at the great spreadsheets people have already created and shared on Business Model Hub. These all are very advanced (and complex) and serve a specific need. I have not yet seen a "don't make me think" kind of spreadsheet that would be as forward to use as the BMC and the iPad app.

 

Would you be interested in a spreadsheet like that to do your business model calculations?

And would you be willing to pay a small amount to use the spreadsheet?

 

I have a lot of experience creating business case spreadsheets and financial benchmark models, also to be used by people who do not have the amount of expertise I (and you) have.

 

Thank you for your reply! (both positive and negative!)

 

Best regards,

Diderik

Tags: Excel, calculations, spreadsheet, tools

Views: 19064

▶ Reply to This

Replies to This Discussion

Permalink Reply by Andrew Young on August 7, 2011 at 10:29am
Diderik am interested to know/see what you mean by "advanced yet simple"

We all assume our own skills and at the same time have no comparison

Can you clarify some more please?

I purchase all sorts of things and give feedback, yet get no response and end up tossing them.

Concept sounds great ... got some pics?
Permalink Reply by d.vanwingerden on August 7, 2011 at 2:29pm
Hi Andrew,

Thank you very much for your reply! I will explain and answer your questions.

This is the format of the spreadsheet I have in mind:

* Sheet 1: Business Model Canvas
- A rudimentary canvas
- Of course no slick 'sticky note' type of representation, but a clearly recognizable canvas you can use to enter your business model

* Sheet 2: Revenue
- For each of the revenue streams you entered in sheet 1 you enter the pricing type (one-time, subscription monthly, quarterly, etc.), price and quantity
- Maybe add as advanced features: projection for 1, 2 or 3 years and detailing sales per month instead of whole year

* Sheet 3: Costs
- For each of the cost structure items you entered in sheet 1 you enter cost category (fixed, variable, one-time, etc.) and the amount
- Maybe add as advanced features: projection for 1, 2 or 3 years (or mark year 2 and 3 as 'same as year 1'), breakdown of cost items (e.g. break down marketing into online advertisement, sales reps, etc.)

* Sheet 4: Overview
- Total of all revenue and costs, resulting in the profits
- For advanced features: yearly projection and overview of costs breakdown

* Sheet 5: Charts
- Pie charts, line charts etc. for visual representation of the resulting model figures.

The key to the working of the spreadsheet would be that you can start off with only a very small amount of basic figures and already get some estimate of the outcome. And then as your business model and estimations become more detailed (or you become more advanced in using BMG/BMC) you can still keep using the spreadsheet for these advanced calculations.

Critical Success Factor: open the spreadsheet and you can start using it, without any special course or training. This means a very intuitive interface, which will be the hardest part to do.

What do you mean by: "I purchase all sorts of things and give feedback, yet get no response and end up tossing them." ?

My idea is to develop this spreadsheet in close communication with you and other users ("co creation"). This to prevent me spending a week or more in developing a nice spreadsheet (to me at least), with nobody interested in it.

I am looking forward to your comments and ideas!

regards,
Diderik
Permalink Reply by Martin Rapavy on August 13, 2011 at 9:38am

Hi Diderik,,

I'd be also very interested in such spreadsheet. Your idea of starting with small pieces and then building the details around it is exaclty what I'm after.

 

Great idea:)

Martin

Permalink Reply by d.vanwingerden on August 14, 2011 at 10:47am

Hi Martin,

 

Thank you for your reply! I will invest some time this week in building a first version of the spreadsheet. Would you be willing to review and help me in creating a nice and usable sheet?

 

regards,

Diderik

 

Permalink Reply by Martin Rapavy on August 14, 2011 at 3:43pm
Yes, of course I'll help you with what I can:)
Permalink Reply by d.vanwingerden on August 15, 2011 at 4:18pm

Hi Martin,

 

I have created a first, *very* rudimentary version of the Business Model Calculator, just to get a feel of where it would be going. See attached file. It basically is a redo of the iPad toolbox, just to get the think process going. I have added the 'Online Training Website' example from the toolbox.

 

Do you think this is a direction of the spreadsheet you would like to see developed further?

 

Todo (for me!):

  • Add dynamic lay-out for the Customer Segments sheet, depending on how many segments are entered in the canvas.
  • Add other revenue stream types on the Revenue Streams sheet, now only Subscription is implemented
  • Add dynamic formulas and lay-out for the Revenue Streams sheet, depending on how many entered in the canvas
  • Add other cost types on the Cost Structure sheet, now only Other Costs is implemented
  • Add dynamic formulas and lay-out for the Cost Structure sheet, depending on how many entered in the canvas
  • Add dynamic formulas and lay-out for the Overview sheet
  • Create Graph sheet and add some interesting graphs (suggestions welcome)

Considerations:

  1. Which revenue stream types are most interesting/used? There are so many! Maybe offering all options is too complex, and one type would be sufficient, with price and time period?
  2. Is the indication of value proposition and customer segments useful for revenue streams in the calculations? Things would be much simple to just say: "You have revenue stream X, the price is Y and the amount sold is Z." And then being able to indicate if this is yearly, monthly, etc. What do you think?
  3. Are the cost types with all the breakdowns useful? Things would be much simpler to say: fixed costs with amount X and variable costs with amount Y and quantity Z. What do you think?

Best regards,

Diderik

Attachments:
Permalink Reply by Martin Rapavy on August 15, 2011 at 8:18pm

Hi Diderik,,

 

Great start!

Re Considerations:

  1. I believe that you could make revenue types more generic. Exactly like you say in second note: Revenue type can be represented by: Price, Quantity, Is it a recurring payment or one time only, If recurring - at what periodicity.
  2. I think it is good to have value proposition and customer segments there as a reminder which segment is participating in a certain revenue stream (but again it can be more than one segment there)
  3. I'm okay with detailed breakdown of costs structure. It can be a good start to developing more detailed business plan. But I think that costs could also be recurring just as revenues.

Other than that, it's already a pretty good and helpful spreadsheet. The chart/graph sheet would be also very cool and here are some chart ideas from me:

  • When will I be profitable? (revenues-costs plotted on time)
  • Revenues/Costs structure (which revenue streams/costs contribute (and how) to my business model? pie chart)

Regards

Martin

Permalink Reply by d.vanwingerden on August 16, 2011 at 11:24pm

Hi Martin,

 

Thanks for your help on the considerations!

 

I have created a new version with the Customer Segments and Revenue Streams now fully implemented. See attachment.

 

They use conditional formatting, so you see cells to complete popping up depending on the number of customer segments and revenue streams you entered in the canvas.

 

Some remarks:

  1. The spreadsheet now supports 5 customer segments and 5 revenue streams. Do you think this is enough? I can add support for more, but with too many the Revenue Streams sheet may become a bit unreadable since I need to reserve space for all (which becomes a lot of whitespace if you don't use as many, I hope I explain this well).
  2. You have to start from the top, so enter a customer segment in the canvas in the cell just below the title "Customer Segments". You cannot skip a few rows in the canvas, e.g. for some spacing to make the canvas look better balanced. The same goes for the revenue streams. If you don't do this, it will look weird in the following sheets (conditional formatting).
  3. For Revenue Stream Type you can select 'None' (default) and 'Sale', which displays a generic type as we discussed. I think this is in most cases all one might need.

 

Next step for me is the Cost Structure sheet, which will be a challenge! And then of course the Overview sheet and the Graph sheet. And then bringing all the numbers in a summarized way back to the Canvas sheet.

 

I am looking forward to your comments!

 

regards,

Diderik

 

Attachments:
Permalink Reply by Adrian Rivers on August 18, 2011 at 6:43pm

Diderik,

 

I would like to test the use of this tool as part of my exlporatin of Models for a new business venture.

 

I will, of course, give you any feedback and upload any a version with any changes to the template that I introduce.

Thanks for what, at first sight, seems like a really helpful and elegant tool.

 

Regards,

 

Adrian

Permalink Reply by Martin Rapavy on August 18, 2011 at 8:44pm

Hi Diderik,,

Thanks for the update,, I'll use it in days to come for my new project and will give you any feedback I'll have. So far it is looking great:)

 

Martin

Permalink Reply by Karl Burrow on August 14, 2011 at 1:14pm

This  may work ...but do keep in mind that there is interplay with all the 9 building block of the canvas.. but  deep focus on revenue and cost structure.  Other key points to consider is allowing hypothesis testing and producing "what if " scenarios based business assumptions or predictions.

Would like to see it myself, though I am using the ipad version and see marginal success.

 

Karl

 

 

 

 

Permalink Reply by d.vanwingerden on August 14, 2011 at 4:50pm

Hi Karl,

 

Thank you for your reply. I understand you would very much like some advanced features as hypothesis testing and scenario's. Which you would probably be able to set up simultaneously and compare.

 

What do you mean by "ipad version and see marginal success"?

Success in what by whom?

 

regards,

Diderik

RSS

© 2013   Created by Alex Osterwalder.

Badges  |  Report an Issue  |  Terms of Service