Warning:  This thread has a lot of material AND there are many posts.  Each post will explain how the numbers came about.

PLEASE READ CAREFULLY.   This is a fairly in-depth analysis.  All assumed figures will be indicated with a (*).  All other numbers are taken directly from MSFT publicly reported financial statements.

I have used FY ending June 2006 as a reference to baseline some figures.  I will explain when needed.

Prior to FY End Jun 2007, the Game Division was by itself (with PC Games), and I think with consumer hardware and software.  There was also a separate Windows Mobile Division.

There was no Zune product in FY end Jun 2006.

Difference between Sony and MSFT analysis:

1.  Sony analysis used NET PROFIT assumptions

MSFT analysis uses GROSS MARGIN assumptions

Because MSFT mentions specific line item expenses (eg. Marketing, R&D), we can infer and calculate some numbers based on those statements.

2. Sony analysis didn’t include Revenue

MSFT analysis uses Revenue – hence it is inherently more difficult to match BOTH revenue and gross margin and net income.

In the MSFT analysis, I have introduced the “Variance” to account for any other “unaccounted” that can not fit both revenue and gross margin figures.

If we used Revenue for the Sony analysis, there would have been the “Variance” line item for both revenue and net profit as well.

NOTE:

This analysis is intended to show a financial model as a basis of analyzing the Games (EDD) Division.

It is more involved than the Sony Analysis and is really more like a bigger jigsaw puzzle to determine the different line items that MSFT did not specifically mention.

If anybody has some source that shows that I have made a BIG ERROR or INCORRECT ASSUMPTION, please let me know.

I DO NEED HELP IN SPOTTING ERRORS AND ERRONEOUS ASSUMPTIONS.  Because the numbers are very hard to “make right”.

I can tweak the model, so that future analysis may be more “accurate”.  Again, given the lack of information, we can really only analyze so far.

But hopefully, there is enough information to base intelligent past and future assumptions and comments – regarding sales and profitability.

COMMENTS AND POSTS:

When commenting or posting, I hope you can use some figures and numbers to back up your arguments.  That would be most appreciated.

This way, if there is a consensus that those numbers are “more correct”, then I can incorporate it to the model – to fine tune the analysis and future predictions.

I hope people read carefully and try to understand where the figures are coming from.

I do understand that not all people know how to read nor understand financial statements, figures and reporting.  However, I will try my best to explain if you don’t understand.

Unfortunately, there may still be concepts or figures you may not fully understand – which is why I will try to explain as simply as I can.

Thanks and enjoy.

FY End Jun 2007

Revenue = 6,083

Income = (1,892) LOSS

Based on MSFT reported figures:

Zune, Consumer, TV Revenue = 1,368(*)

I will refer to this business division as Zune+ for easier reference.

How did I get this number?

In their report, Zune+ revenue increased by $539 million or 65% from the previous FY end Jun 2006.  Using simple mathematics, that equates to a revenue of $829 million in the previous year.  Therefore, $539 + $829 = 1,368

Mobile & Embedded (or Mobile for short) Revenue = 631(*)

Using the same method as Zune+, as well as MSFT reported increase of $138 million or 28%, then $631 is the total revenue for this FY end Jun 2007.

Therefore, Xbox Platform and PC Games Revenue = 4,084(*)

Total Revenue 6,083 less 1,367 from Zune+ less 631 from Mobile = 4,084

Since we are VGChartz, this is the number we are interested in.

FY End Jun 2008

Revenue = 8,140

Income = 426 PROFIT

In their report, MSFT indicates that revenue for the Xbox Platform and PC games increased by $1.7 billion.

Therefore:

Xbox Platform and PC games Revenue = $5,784(*) million

From $4,084 (FY end Jun 07) + $1,700 increase

Zune+ Revenue will be estimated

Mobile Revenue will be estimated

Both should total $2,356(*) to make the total revenue $8,140

I will get back to these numbers later.

Cost of Revenue and Expenses

Cost of Revenue (or Cost of Goods Sold)

MSFT reported COR for FY end Jun 2008 to have decreased $683 million or 13%.

This reflects the $1.06 Billion RROD charge from FY End Jun 2007.

Again doing simple math, the COR for FY End Jun 2008, this figure should be $4,571(*).

Again based on simple math:

Total COR for FY End Jun 2007 = $5,254(*)

Check:  5,254 less 683 = 4,571; 683 is 13% of 5,254

However, we need to strip out the RROD charge so the numbers would be more comparable.

The COR for FY End Jun 2007 less the $1.06 Billion RROD Charge = $4,194(*)

This makes sense because the COR for FY end Jun 2008 should be higher than FY end Jun 2007 because Revenue is higher.

Sales & Marketing FY End Jun 2008

MSFT reported that Sales & Marketing expenses increased by $93 million or 8%.

Therefore:

Sales & Marketing FY End Jun 2008 = $1,256(*)

Sales & Marketing FY End Jun 2007 = $1,163(*)

R&D FY End Jun 2008

MSFT reported R&D expenses increased by $242 million or 18%

Therefore:

R&D FY End Jun 2008 = $1,586(*)

R&D FY End Jun 2007 = $1,344(*)

SUMMARY:

Now we have got a breakdown of some of the line items in a typical financial statement:

FY End Jun 2008

Revenue = 8,140

COR = 4,571(*)

Gross Margin = 3,569(*)

Note:  We will be using gross margin, instead of net profit for the games analysis.

Less:

Sales & Marketing = 1,256(*)

R&D = 1,586(*)

Headcount, Other Expenses and Variance (HOV) = 301(*)

To explain: HOV is just the number we need to plug in to make the numbers equal as per MSFT actual reported statements.  As per its definition, variance is just things we just cannot account for, without more information.

Net Profit = 426 million = actual reported figure

So for FY End Jun 2007 – we have figured out some numbers on the Revenue and Expense side to make the numbers fit.

Using the same methodology…

FY End Jun 2007

Revenue = 6,083

COR = 4,194(*)

Gross Margin = 1,889(*)

Less:

RROD (One Time Charge) = 1,060

Sales & Marketing = 1,163(*)

R&D = 1,344(*)

Headcount, Other Expenses and Variance (HOV) = 214(*)

Net LOSS = ($1,892) = actual reported figure

A quick check indicates that HOV makes some sense, because MSFT did report a 21% increase in headcount in FY end Jun 2008.  So HOV for FY end Jun 2008 should be higher than FY end Jun 2007 – which is the case here.

Xbox Platform Breakdowns

This is where most of the interest would be in VGChartz.

Though sadly, MSFT posts are so much less popular than Sony posts. L

Xbox Platform is as follows:

Xbox Console

Games

Accessories

Live

PC Games

Variance

I have to include PC games because when MSFT talks about revenues, it always lumps Xbox Platform and PC games.

For FY End Jun 2007

Xbox Platform Revenue = 4,084(*)

Where did I get this number?

Total Revenue:  6,083

Less:

Zune + Revenue:  1,368(*)

Mobile Revenue:  631(*)

Equals:

Xbox Platform Revenue = 4,084(*)

For FY End Jun 2008

MSFT mentioned that Xbox Platform and PC game revenue increased by $1.7 Billion or 41%

Those numbers are generally rounded up or down – that is, it won’t exactly be the same.

For all intents and purposes, I will use the $1.7 Billion as the figure I will work with.

Therefore

Xbox Platform Revenue = 5,784(*)

Then:

Zune+ and Mobile Revenue = 2,356(*)

(This number will be needed to calculate gross margins)

Total Revenue Check = 5,784(*) + 2,356(*) = 8,140 = ACTUAL MSFT REPORTED FIGURE

Xbox Platform Revenue Breakdown

To get some of the breakdown for my analysis, these are some of the figures I used to derive the other figures.

Software attach rate = 7.5 (as per NPD)

Accessory attach rate = 3.6 (as per NPD)

However, now we have to break it down for each fiscal year as well.

From my understanding, if I multiply the software attach rate to the number of total consoles – this would give me the TOTAL SOFTWARE sold lifetime.

As of FY end June 2008, the total consoles shipped by MSFT = 20.3 million

20.3 million consoles x 7.5 SW attach rate = 152.3 million software sold

However, we still need to get a yearly number to use for our yearly analysis.

To do that, we also have to do that for each year and take the difference.

As of FY end Jun 2007, the total consoles shipped by MSFT = 11.6 million

11.6 million consoles x 7.5 SW attach rate = 87 million

As of FY end Jun 2006 – it is 5.0 million consoles

5 million x 7.5 = 37.5 million

Now we can get the yearly software sales, much like what Sony reports.

FY End Jun 2008 = 65.3(*) million software units (152.3 less 87)

FY End Jun 2007 = 49.5(*) million software units (87 less 37.5)

Using the same methodology for Accessories:

FY End Jun 2008 = 31.3(*) million accessories

FY End Jun 2007 = 23.8(*) million accessories

So the following are the Xbox Platform Revenue for both years:

FY End Jun 2007

Console = 6.6 million x $300(*) = 1,980(*)

Games = 49.5(*) million x $25(*) = 1,238(*)

Accessories = 23.8(*) million x $25(*) = 594(*)

Live = 225(*) million

PC Games + Variance = 48(*) million

Total Xbox Platform Revenue = 4,084(*) = the same figure we have from the previous calculations.

FY End Jun 2008

Console = 8.7 x $300(*) = 2,610(*)

Games = 65.3(*) x $30(*) = 1,958(*)

Accessories = 31.3(*) x $25(*) = 783(*)

Live = 380(*)

PC Games + Variance = 53(*)

Total Xbox Platform Revenue = $5,784(*) = the same figure we have from previous calculations

Questions you may have:

1. Games and Accessories revenue per unit are just assumptions
2. I increased the Games revenue per unit in 2008 due to Halo
3. I kept console revenue constant – which is an average console revenue
4. Live revenue is just a guesstimate – but it makes some sense that it increases from the previous year – especially with all the new stuff plus movie downloads
5. PC games + variance – is just to make the numbers add up.

If you guys have any data that I can use, please let me know.  For example, if someone can show me that PC games bring in $100 million a year for MSFT, let me know so I can use it in my analysis.

So after the Revenue Analysis, we basically can calculate/speculate the Gross Margin or % so we can do some scenario analysis with different numbers.

The numbers that you will see are mostly educated guesstimates and assumptions to make the numbers add up that make any sense.

So please, if you do have any comments, just note that we can’t just change 1 figure or number, without affecting the whole calculations.

Each figure change has to make sense, not only for that line item, but also for all the other line items.

I can let you know what I mean, if someone makes a suggestion of a number change and show you the implications – whether it makes sense or not.

Gross Margin Breakdowns

FY End Jun 2007

Total Gross Margin should be $1,889(*) – see the beginning of the thread again

Console = 6.6 million x $45(*) gross margin per unit = 297(*)

Games = 49.5(*) x $12(*) gross margin per unit = 594(*)

Accessories = 23.8(*) x $6(*) gross margin per unit = 143(*)

Live = 55%(*) Gross margin x $225(*) revenue = 124(*)

PC Games = 60%(*) Gross margin x $48(*) revenue = 29(*)

Zune+ and Mobile = 35%(*) Gross Margin x $1,999(*) revenue = 700(*)

--- Zune+ 1,368(*) add Mobile 631(*) = 1,999(*) revenue (see previous analysis)

Subtotal Gross Margin = $1,887(*)

Add Variance of $2(*) (or basically 0% unaccounted for)

= $1,889(*) Total Gross Margin

FY End Jun 2008

Total Gross Margin should be $3,569(*) – see beginning of thread again

Console = 8.7 million x $75(*) gross margin = 653(*)

Games = 65.3(*) million x $20(*) gross margin = 1,305(*)

Accessories = 31.3(*) million x $9(*) gross margin = 282(*)

Live = 75%(*) gross margin x $380(*) revenue = 285(*)

PC Games = 62%(*) gross margin x $54(*) revenue = 33(*)

Zune+ and Mobile = 41%(*) gross margin x $2,356(*) revenue = 966(*)

Subtotal Gross Margin = $3,524(*)

Add Variance of $45(*) (or basically 1% unaccounted for)

= $3,569(*) Total Gross Margin

Some Explanations:

1. Console margins assumed to be $45(*) in FY end Jun 07 to $75(*) in FY end Jun 08 due to cost reductions
2. Games margins increased because of the assumption that Halo has greater margins than regular software
3. Accessories margins increased due to assumed cost reductions
4. Live Margins were increased due to increased downloads, especially of movies, which should essentially be almost cost free on a per unit basis.
5. PC games initial assumption of 60% gross margin – a somewhat semi-standard gross margin for software in general.  Margins increased slightly to 62% in the next year.
6. Zune+ and Mobile – assumed a 35% gross margin – due to mix of both hardware and software.  Increase grossed margins to 41% due to cost reductions on the Zune hardware.
7. All these numbers were done via some trial and error – but this initial result makes the most sense for ALL BUSINESS SEGMENTS in the EDD division.