In the previous post we explained the steps to be taken to get a full graph with six automotive companies over six years. Every next post a company will be added to the 3D-graph, but as we did’t create a 3D-graph yet, this will be done now. For no specific reason I picked Mitsubishi Motors (remember: not the whole Mitsubishi Chaebol) as the first in line. The annual reports are avialable at this link: Mitsubishi https://www.mitsubishi-motors.com/en/investors/library/anual.html?intcid2=investors-library-anual
The bookyear is “broken” and ends at March 31. This means the greater part of the bookyear is in the previous calendar-year and therefore we will consider the annual reports to be about the calendar-year before. So 2018 will be treated as 2017 and so on until 2013 representing 2012 – to align the numbers as much as possible with the book-years of other companies.
The fastest way to get to the balance and overview of profit and loss is to search for the word “consolidated”. In most annual reports it points to the financial sector in a few clicks. Below an example is shown for (parts of) the Profit & Loss statement and the Balance sheet. The Revenue-number is called “Net Sales”.
Another example shows a collage from the Balance-sheet for the same book-year.
Here the difference between Assets and Liabilities (Equity) is referred to as “Total Net Assets”. Of course the values for Net profit and Total Assets were also extracted from those tables, but are not visible in the examples above. Although the amounts are in JPY (Yen), it turns out that only the reports named 2018 and 2017 were purely in yen, but the older ones have dollar-columns for the reported year. This saves a lot of time. To obtain the right JPY-to-USD rates, I went to:
The link is an example of September 30, 2017. This is the middle of the bookyear ending at March 31 2018 and is the best estimate for Revenue and Profit. The “Average: 1 USD = 112.5505 JPY” at this date was used, but for the balance I took the end of the bookyear. Then the average was 106,0115. The same principle was applied to the values for the bookyear ending at ending at March 31 2017.
Picture from Petr Elvis at Pixabay https://pixabay.com/en/users/ElvisCZ-1106877/
All relevant values (and then some) were entered in an Excel spreadsheet and checks were performed to be sure the right values were in. (E.g. Equity + Total Liabilities should be equal to Total Assets. Sometimes there was a rounding error of 1 and as it was about millions of yen the rounding error was 1 million yen or about ten-thousand USD, but that’s ok I guess). The conversions to USD were also done in the Excel and provided millions of USD – a much smaller amount of course – but the dollar-values in the older reports were in thousands of USD. Finally, after a little reshuffling and re-modeling, I got this input-file (the first line is the parameter-line) when I saved the Excel-sheet as an MS-DOS .csv:
The additional semicolons don’t harm and neither do empty lines after the last entry.
(There are several options to move graph: clicking the right mouse-button, moving the mouse up and down will zoom the graph in and out. Clicking left and moving the mouse at the same time will tilt the graph in different directions (or move the observer’s viewpoint around a fixed graph – it’s relative of course). Double clicking in the graph translates it and moves the centre at the same time. As a result the way the graph tilts will change. Just try it. If you don’t know how to get the normal position back, just refresh the graph.)
This time I don’t comment on the graph (yet). Have a look and draw your own conclusions.