We have again selected Coca Cola for this example, and we will work through the “Return on assets, Return on Equity” calculator.
Once you turn on Self-Assessment mode, you will see:
You can already see the step-by-step guidance in the Self-Assessment tab: it tells you exactly what entries are required from the financial statements to analyze Coca Cola.
· Your goal is follow the step-by-step instructions by filling in the entries into the red cells. Every time you correctly fill in a red cell, it becomes green. Once all entries are green, you have successfully completed the construction, and can move on to interpreting the results.
· Once you have some experience, you can reduce the amount of help you get from the Self-Assessment menu by selecting the strength of the hint:
· You can test yourself by repeating the exercise with no hints.
· This exercise does the analysis over three years; this allows us to interpret the results at the end of the construction.
We will use “Strong Hints” in this write-up. The first calculator field is “Consolidated Net Income.” Coca Cola uses the same terminology in its income statement. So the hint tells you to drag and drop the number 8634 into the calculator cell across from Consolidated Net Income for the construction date 31 Dec 2011. Once you do that, you see that cell turn green, and the next instruction:
It is asking you to repeat the same construction for 2010 and 2009.
The next calculator field is “Net Income Attributable to NCI.” NCI stands for Non Controlling Interests; this is subtracted from the net income to obtain Net Income Attributable to Shareholders.
The self-assessment hint will tell you that on Coca Cola’s income statement, this is called “Less: Net income attributable to noncontrolling interests” and is reported just below the Consolidated Net Income. Drag and drop these values for the three years, and you should have:
The next calculator field is “Average Total Assets” and this information is in the balance sheet, as noted in the hint. It is an average, and therefore will require us to drop 2 values into the same calculator cell and then take their average.
So switch to the balance sheet, and drop the Total assets of Coca Cola on Dec 31, 2011 into the calculator cell next to Average Total Assets:
Now you have to be careful in reading the hint: you have to drop the 2010 total assets into the 2011 column. Two values have to be dropped into the same cell. After you do that, you will see a different hint:
Look at the Cell Editor: it has two values. The formula shows the first value only. Click the m button, as noted by the hint, to average the two entries. After you click m, you should see:
Look at the cell formula to verify that the average has been calculated.
One complication with balance sheets is that they typically have two years of data while income statements have three years of data. So to calculate the Average Total Assets for December 2010, you will have to use information from the 2011 10-K, and for December 2009, you will need information from the 2010 10-K. You can select the filing dates and statements in the same viewer or use a second viewer; there are four available (one is behind the Self-Assessment tab; one is behind the Cell Editor).
After Average Total Assets, we need to repeat the same exercise with Average Shareholders Equity:
After you finish calculating those averages, you will see a chart window popup:
You can now plot the different fields and interpret how you think Coca Cola has performed. In fact, as you are dragging and dropping the numbers, you should pay attention to the magnitudes. For example, in the construction of Average Total Assets, you may have noticed a big jump in Coca Cola’s assets in 2010.
Once you have completed a construction with strong hints, see if you can repeat it with medium hints and with no hints for the same company. Then, pick a second company, and start with no hints and see how far you can go; you can always get more help from the Self-Assessment menu. As you do this, you will quickly become familiar with ratio construction.
