Creating the Financial Model for your Company Part I
5 year plan parameters products spreadsheet financials income statement operating expenses cash flow statement
The 5 year plan.. Why on earth do we need it?
Hello. So in this article, we are going to be going through the process of creating the financial model for your company. For us at Sanctuary Game Studios, this was one of the steps we had to go through when determining the financials, and how much money we would need to pitch for to an investor.
So from here on out, I'm going to try to make it as simple as possible in two parts. Part I will be covering the Parameters and Product Sheet. Part II will be covering the Operating Expenses, Income Sheet, and Cash Flow Statement.
And most importantly, enjoy.
Why the 5 year plan, and what are the elements in it?
So what is the 5 year plan? The 5 year plan is what is going to happen for the next 5 years basically. When you are approaching someone to invest in your company, generally they would ask to see what will happen over the next few years. What products you will make, the research you have done and so on. Finally, this entire financial model is based off of the freemium plan. So in the plan we are making, we are going to go over a couple different parts:
- The parameters
- Your Product
- Your Operating Expenses
- The Income Statement
- The Cash Flow Statement
I use open office, but in general the things that are going be present in this are the same whether you are using Google Docs, or Microsoft Excel, or other software. So lets start with the Parameters.
The Parameters are basically the rules that are going to be applied throughout the entire document. This helps in two main ways. A) It helps make sure that the entire document is less messy. B) You can control the entire document with this page, after you create the equations that follow.
So what parameters do we need to set up?
- facebook Cost Per Click
- Youtube Cost Per Click
- Conversion to App Downloads
- Conversion to Monthly Active Users (MAU)
- Conversion of MAU to Whales
- Decline of MAU
- Purchasing Behaviors of Whales (IAP)
- Our Install Per App Revenue (IPA)
- The Low CPM estimate (CPM)
- The High CPM estimate (CPM)
- The Estimated Impressions Per User (EIU)
So why do we need to set these up?
We will be doing a monthly campaign for each of our products over the next 5 years. Now with that, we need to know how much it costs for a Youtube campaign, as well as a facebook advertising campaign. After that, we need to decide what is the conversion rate between the advertisements viewed and the apps downloaded.
From there, it's not that every person that downloads the game sticks around, so there is a conversion rate between Users to Monthly Active Users (MAU). From those MAU, a percentage of those become whales, who are the people who purchase. These whales have purchasing behaviours though, which are split into 5 parts:
- Single Purchase
- Double Purchase
- Triple Purchase
- Quadruple Purchase
- Five Plus Purchases
Research Research Research!
You are going to need to do research in your target audience for each game. We are in the business of making money, and not just making a game. So you need to discover what your market size is, and what behaviours they will follow. For instance, we make Casual and Social Games, and venture a little into other types as well. Our target audience is the North American and European player base, and we have a Total Addressable Market (TAM) of 199 million mobile gamers. Now some cool things about that is that you will find that Social Gamers tend to have a higher purchasing behaviour than other types. So in diving in the books, we can make a better estimate for how much we are going to make, and give our potential investor a less risky approach to Sanctuary Game Studios.
So lets go back to the parameters then.
facebook Cost per Click 0.12 Youtube Cost per Click 0.04
Our facebook and Youtube campaigns are set so that we have a monthly budget for the campaign, depending on our game. The cost for the campaign though is 12 cents a click and 4 cents a click respectively. This can change, but that's why we are keeping this in the parameters instead of changing it hundreds of times in the spreadsheet.
Conversion to app downloads 30.00%
For each of our advertising campaigns, we are setting it that 30% of the ads we get clicks on have people downloading the free app. If you know your market well enough, this can vary. You will find that this is one of the biggest indicators of how much money you will make in the long run, but you need to make sure that you remain as humble as possible, especially when you are approaching an investor.
The reason for that is they want to see that you need them. Chances are you will not get an investor to help you out if you are making money.
Conversion to monthly active user 55.00%
For the amount of people that download your game, not everybody will stick around. As far as user behaviors are concerned, you would find that the average amount of users that convert to MAU is around 55%. This is why it is important that we go back to your research to find out the conversion rate for your target audience.
Conversion of MAU to Whales 2.2%
On average, 2% of your MAU will become whales. Whales are basically the people that make purchases in your games, and you should not ignore them. These whales are divided up though into percentage groups based on their purchasing behaviours:
Single Purchase User Percentage 48.80% Double Purchase User Percentage 21.20% Three Purchase User Percentage 10.70% Quadruple Purchase User Percentage 6.10% Five plus Purchase User Percentage 13.20%
For the purpose of our financial plan, we are grouping up the 5+ group together instead of listing the averages of each set one by one. When you are setting up your financial sheet, I would recommend putting another set of parameters for the average purchase price for each of your games. For example, we've set XYZ game to have an average purchase price of $3. So when it comes to the Products sheet, we can just multiply the parameters together to find out how much money we will be making per product.
1 month later decline 55.00% 2 months later decline 7.80% 3 months later decline 3.80% 4 months later decline 2.20%
Notice that the whales tend to decline over a period of time. After a period of 4 months though, the people who are playing your game tend to stick around. This will be important in coming up with the half life of your product. So unless you are going to continue putting in money each month for advertisement, or if it goes viral or becomes a social crack, then you will find the total amount of users declining pretty quickly.
Install per App Revenue $0.20 Low CPM Estimate $2.00 High CPM Estimate $8.00
Now for a lot of the monetization carriers, you will find that some pay you per install. This is the Install per App Revenue (IPA), so for what we use we get $.20 per install. For the CPM though, we would be making at least $2 for every 1000 impressions. You will need to do your research to find out what is the best option for you. Some, like Revmob, will pay you more for the IPA versus the CPM, which is great if you are looking for money to help you out on your next project. If you are going for the long term investment though, you will need to find a carrier that can give you a higher CPM.
Estimated Impressions per User (EIU) 30
Finally you will need to decide on the number of impressions you would get per user, or the EIU. This is another major factor in finding out how much money you would be making, but it depends on a lot of factors. So for this, and to make things simplier, we have set it so that each user gets an average of 30 impressions per month.
You should now open up a new spreadsheet which you should title Products. For ourselves, we set our products in two categories. Fluffs and Flagships. Fluffs have a smaller marketing budget than flagships, which are the main games we want to be recognized by. The other thing is that our fluff games are there to help keep things afloat between the downtime of releasing the flagships.
So you will need to create the 5 year estimate of what is going to happen to your product. Underneath each year then, you will need to break that up into Month / Q1-Q4 / Year Total.
On the far left bar, we would be setting up the assumptions. The facebook Campaign and Cost per Click, the Youtube Campaign and Cost per Click, and the Unique Visitors per month total.
The equation to find how many of your unique visitors then becomes: (facebook Campaign/.12)+(Youtube Campaign/.4). So lets say we set the facebook campaign and Youtube campaign at 1,428.57 USD, it would be (1428.57/.12)+(1428.57/.4)=47,619 Unique Visitors. This is being rounded up though, as seen with the following equation:
Now I've set the parameter for the advertisement to app download to be at 30%, so basically =$Parameters.$B$5
In order to then find out how many of the unique visitors then download the app, the equation is =ROUND(H8*H11;0) or where we multiply the 47,619 visitors by 30%. That gives us 14,286 app downloads.
The conversion to monthly active users from the app downloads is 55%. So then we multiply the 14286 by 55% or =ROUND(H12*H13;0) to end up getting 7,857 MAU.
The conversion rate in our parameter is 2.2% for the MAU to Whales, so that then becomes 7857 * 2.2% or =ROUND(H14*H15;0) to end up with 173.
Out of the 173 though, they decline over a period of 4 months. 1st month is by 55%. 2nd month by 7.8%. 3rd month by 3.8%. 4th month by 2.2%. So each month, the previous month's amount of whales decline bit by bit before they end up at the 4th month.
So let's say we stick at 173 whales. The second month, that number becomes 78. But we have a new batch of whales that entered, so we have 173 whales + 78 to have a total amount be 251. Now the third month, the first month goes down to 72, then 69, then down to 67. So we have then, to make things easier, the residual whales at the end of the decline adding up, and in a line underneath the total amount of whales. For example:
Number of new whales 173 173 173 173 173 173 173 1 month later decline 55% 0 78 78 78 78 78 78 2 months later decline 7.8% 0 0 72 72 72 72 72 3 months later decline 3.8% 0 0 0 69 69 69 69 4 months later decline 2.2% 0 0 0 0 67 67 67 Residual whales after decline 0 0 0 0 0 67 134 Total Amount of Whales 173 251 323 392 459 526 593
So what comes next?
We need to add up the total amount of MAU. One thing that is missing here, that you will need to factor in, is the decline of MAU as well. But for the purpose of this example, we are putting in the MAU, adding it all up, and then putting in the recurring MAU the previous month total, then adding all that up together again.
Monthly Active Users Number of monthly active users 7857 7857 7857 Number of Recurring MAU 0 7857 15714 Total amount of MAU 7857 15714 23571
After that, we are going to find out how much money you are making off the whales. Remember when we split up the %'s of purchasing behaviour?
Single Purchase User Percentage 48.8% Number of Single Purchase Whales 84.0 Average Purchase Price $3.00 Total $252.00 Double Purchase User Percentage 21.20% Number of Double Purchase Whales 37.00 Average Purchase Price $6.00 Total $222.00 Three Purchase User Percentage 10.70% Number of Triple Purchase Whales 19.00 Average Purchase Price $9.00 Total $171.00 Quadruple Purchase User Percentage 6.10% Number of 4 Purchase Whales 11.00 Average Purchase Price $12.00 Total $132.00 Five plus Purchase User Percentage 13.2% Number of Five plus Purchase Whales 23.0 Average Purchase Price $15.00 Total $345.00 Total Monthly Estimate for In App Purchases $1,122.00
After that comes the advertising section:
Number of app downloads 14286 Install per App Revenue $0.20 Total $2,857.20
Here we are taking the amount of app downloads that happened in the month, and since we have an IPA of 20 cents per, we multiply that to get 2,857.20 USD. But then comes the other part with the CPM.
Total Amount of Users 14286 Estimated Impressions per User (EIU) 30 CPM Estimate $2.00 Total $857.16
Remember that the CPM is for every 1000 impressions. So then we multiply the total amount of users, which is also added in with the last month's MAU by 30, which is the EIU. We take that number, and multiply that by the CPM estimate, for which it is 2 USD. And then we divide it all by 1000 to end up with 857.16 USD.
So in equation format, it's: (TAU*EIU*CPM)/1000 = Total
Finally, we add up the IPA, the CPM, and the IAP for a total amount of $4,836.36.
Now remember to repeat this process throughout the 5 years, so then you can get a total amount at the end. If you are following this model, and repeating the 50/50 share in the advertising campaign between facebook and Youtube, as well as setting a yearly budget of 20,000 USD, then by the end of the year you should be making $56,155.86. But remember to factor in the decline of the MAU to make the estimate as accurate as possible.
What is coming in Part II
So far we went over two main parts of your 5 year plan. The first part was setting up the parameters, and the second part going over the product estimates. It is very important that you do your research, so don't forget that when finding out what the user behaviors are for your target audience, as well as their purchasing behaviors.
In the next article though, we are going to cover the Operating Expenses, and then the Income Statement and Cash Flow Statement for your 5 year plan. The title should be "Creating the Financial Model for your Company Part II" so keep an eye out for that.
If you have any questions though, feel free to ask.
Article Update Log
30 May 2014: Initial release