Using the financial statements for a mystery company provided on the assignment spreadsheet below, calculate all of the ratios for all five years. When you use excel and link the equations in the first year, you can drag them across and excel will calculate all the other years for you. This is a great time saver! See the help video if you don’t know how to do this. .
Common size financial statements will help you in your time-series analysis and will give you a better idea of what is going on in the company. Go to the Income Statement and the Balance Sheet tabs and create a common-size statement out to the side of the original ones. (I have included a blank format for you). Then complete a financial statement analysis listing a few points of interest. You should also mention some of your findings in your
. (A financial statement analysis is basically just taking a look at the financial statements to get an overall picture of the company. For instance, by looking at the amount of revenues generated, you can get an idea about the size of a company and by looking at multiple years, you can learn if the revenues are growing or declining. Both are important bits of information about the company.)
On the same spreadsheet, complete an analysis for each ratio category similar to the one on page 97 of the text book . A ratio analysis includes a trend analysis, an industry comparison and a sentence summarizing your overall view of the company’s financial performance in that category. Please be thorough, this is an analysis.
Summary (Part 4)
This is where you summarize all of the information you have attained so far about the company. Using the information you have gained from the financial statement analysis, the common-sized financial statements, and the ratio analysis; state your impression of the overall financial condition of the company and your recommendations of what needs to be accomplished going forward. (I am NOT asking you whether or not you would invest in this company, I’m asking if you were the manager of this company, what you would do.)
Use your critical thinking skills here. Back up your statements with data (facts). Thoroughly define the problems. Be aware of bias and fallacious reasoning (Don’t just tell me a bunch of things that might or might not be true, I’m looking for facts based on your observations of the firm’s financial statements and ratios). Consider and evaluate multiple alternatives and their consequences. Draw a well reasoned conclusion.
td>
Statement
MYSTERY COMPANY INCOME STATEMENT | Common Size Income Statement | ABOUT THE INCOME STATEMENT | ||||||||||||||||||||||||||||||
Consolidated Statement of Income – USD ($) shares in Millions, $ in Millions | 12 Months Ended | Fiscal year ends in December. USD in millions except per share data. | Sparklines | |||||||||||||||||||||||||||||
2014 | 201 | 5 | 2016 | 2017 | 2018 | 2015 | Optional | The Income Statement sumarizes a company’s revenues and expenses, | ||||||||||||||||||||||||
Revenue ( | Sales | 88, | 9 | 107,006 | 135,987 | 177,866 | 232,887 | Based on the equation: Revenues – Expenses = Net Income | ||||||||||||||||||||||||
Cost of revenue ( | COGS | 73,518 | 85,061 | 10 | 5,884 | 1 | 37 | 173,183 | ||||||||||||||||||||||||
Gross profit | 15,470 | 21,945 | 30,103 | 40,683 | 59,704 | The Income Statement is also known as a Profit and Loss Statement | ||||||||||||||||||||||||||
Sales, General and administrative | 7,001 | 9,665 | 13,743 | 18,150 | ||||||||||||||||||||||||||||
Other operating expenses | 9,408 | 12,711 | 16,252 | 22,834 | 29,133 | Most income statements cover a 1-year period | ||||||||||||||||||||||||||
Total operating expenses | 15,292 | 19,712 | 25,917 | 36,577 | 47,283 | |||||||||||||||||||||||||||
Operating income (EBIT) | 178 | 2,233 | 4,186 | 4,106 | 12,421 | All Income Statements have 3 parts: (they may be named differently, but will always give the same information) | ||||||||||||||||||||||||||
Interest Expense | 210 | 459 | 484 | 848 | 1,417 | First Part = | Gross Profits | |||||||||||||||||||||||||
Other income | (79) | (206) | 190 | 548 | 257 | The first part always subtracts the COGS from the total revenue | ||||||||||||||||||||||||||
Income before income taxes | (111) | 1,568 | 3,892 | 3,806 | 11,261 | Second Part = Operating Results, also called Statement of Operations, | ||||||||||||||||||||||||||
Provision for income taxes | 167 | 950 | 1,425 | 769 | 1,197 | or EBIT (Earnings before interest and taxes.) | ||||||||||||||||||||||||||
(22) | (96) | (4) | The second part always subtracts the operating expenses from the gross profit | |||||||||||||||||||||||||||||
Net income | (241) | 596 | 2,371 | 3,033 | 10,073 | Third Part = Net Profits, also called Statement of Income | ||||||||||||||||||||||||||
Earnings per share | which usually includes | EPS | ||||||||||||||||||||||||||||||
Basic | -0.52 | 1.28 | 5.01 | 6.32 | 20.68 | Sparklines are a helpful tool in determining trends. They are very easy to do. | The third part always subtracts out interest and taxes | |||||||||||||||||||||||||
Diluted | 1.25 | 4.9 | 6.15 | 20.14 | Click in the cell where you want the sparkline. | |||||||||||||||||||||||||||
Weighted average shares outstanding | Click “Insert” at the top of the spreadsheet | |||||||||||||||||||||||||||||||
462 | 467 | 474 | 480 | 487 | Find “Sparklines” on the toolbar and click “Line” | |||||||||||||||||||||||||||
477 | 493 | 500 | A pop-up box will appear | The Common-Size Income Statement expresses the Income Statement as a percentage of Total Revenue (Sales). | ||||||||||||||||||||||||||||
EBITDA | 4845 | 8308 | 12492 | 16132 | 28019 | Click in the Date Range box and then highlight the five years of data on your | (Divide everything by Total Sales) | |||||||||||||||||||||||||
spreadsheet and click enter | ||||||||||||||||||||||||||||||||
Market Price of Mystery Company’s Stock | 310 | 676 | 750 | 1169 | 1502 | Once you do this for the first line of the Income Statement, you can drag the formula | Earnings Available to Shareholders (Text page 55 or under section titled “Income Statement” in Chapter 3.1) | |||||||||||||||||||||||||
Use this for your Market | Ratios | down the column to do a sparkline for each item on the Income Statement. | Earnings Available to Shareholders = Net Income less Preferred Stock Dividends (if the company has Preferred Stock) | |||||||||||||||||||||||||||||
If the company does not have preferred stock: Earnings Available to Shareholders = Net Income | ||||||||||||||||||||||||||||||||
Income Statement Analysis | ||||||||||||||||||||||||||||||||
Take a look at the Income Statement and the Common-Size Income Statement you have just calculated. | ||||||||||||||||||||||||||||||||
List some of the points of interest below and then talk about a few of them in your ratio analysis and summary. | ||||||||||||||||||||||||||||||||
You should be able to use some of this information to back up your time series analysis statements. (Company’s performance over the five year period). | ||||||||||||||||||||||||||||||||
Points of Interest: List your observations from the financial statement here! I listed a couple to get you started. | ||||||||||||||||||||||||||||||||
The Common-Size Income Statement computes the Gross, Operating and | Net Margin | |||||||||||||||||||||||||||||||
This firm’s revenues show a very strong upward trend – this is very good for the company. |
MYSTERY COMPANY BALANCE SHEET | Common-Size Balance Sheet | ABOUT THE BALANCE SHEET | |||||||||
Consolidated Balance Sheet – USD ($) $ in Millions | The balance sheet reports a firm’s total assets, and how these assets are financed, through a mix of debt and equity. | ||||||||||
Cash and cash equivalents | 14,557 | 15,890 | 19,334 | 20,522 | 31,750 | Remember the Accounting Equation: Assets = Liabilities + Shareholder’s Equity | |||||
Short-term investments | 2,859 | 3,918 | 6,647 | 10,464 | 9,500 | ||||||
Total cash | 17,416 | 19,808 | 25,981 | 30,986 | 41,250 | That mix of debt and equity used to finance a firm’s assets is called the firm’s CAPITAL STRUCTURE – You will see this term later! | |||||
Receivables | 5,612 | 6,423 | 8,339 | 13,164 | 16,677 | A standard balance shee has three parts: assets, liabilities, and equity. The assets and liabilities are divided into short-term and long-term. | |||||
Inventories | 8,299 | 10,243 | 11,461 | 16,047 | 17,174 | The balance sheet is the only financial statement which applies to a single point in time. | |||||
Total current assets | 31,327 | 36,474 | 45,781 | 60,197 | 75,101 | ||||||
Property, plant and equipment | 22,730 | 30,053 | 42,441 | 68,573 | 95,770 | There is a very important distinction between Short-Term and Long-Term | |||||
Accumulated Depreciation | (5,763) | (8,215) | (13,327) | (19,707) | (33,973) | Short-term assets and liabilities refer to a firm’s operations | |||||
Property, plant and equipment, net | 16,967 | 21,838 | 29,114 | 48,866 | 61,797 | Short-term assets and liabilities are expected to be converted to cash or paid off within 1 year | |||||
Goodwill | 3,319 | 3,759 | 3,784 | 13,350 | 14,548 | Short-term assets should more than offset short-term liabilities, which represents a firm’s liquidity | |||||
Intangible assets | 764 | 762 | 854 | 3,371 | 4,110 | ||||||
Other long-term assets | 2,128 | 2,611 | 3,869 | 5,526 | 7,092 | Long-term assets and liabilities last longer than a year and are considered fixed | |||||
Total non-current assets | 23,178 | 28,970 | 37,621 | 71,113 | 87,547 | Stockholder’s equity is assumed to have an infinite life – very long-term | |||||
TOTAL ASSETS | 54,505 | 65,444 | 83,402 | 131,310 | 162,648 | A firm’s short and long-term assets combined should more than offset a firm’s long-term liabilities, | |||||
Accounts payable | 16,459 | 20,397 | 25,309 | 34,616 | 38,192 | which represents the solvency of a firm | |||||
Accrued liabilities | 9,807 | 10,384 | 13,739 | 18,170 | 23,663 | ||||||
Deferred revenues | 1,823 | 3,118 | 4,768 | 5,097 | 6,536 | Contrary to what your textbook states, the liquidity of a firm does NOT refer to the overall financial position (solvency) of a firm! | |||||
Total current liabilities | 28,089 | 33,899 | 43,816 | 57,883 | 68,391 | While a lower liquidity could possibly be a precursor or early sign of financial distress, it does not mean the company is headed | |||||
Long-term debt | 8,265 | 8,235 | 7,694 | 24,743 | 23,495 | for banckruptcy. You would need more information to determine that. | |||||
Capital leases | 4,224 | 5,948 | 7,519 | 13,183 | 16,292 | You would have to look at the strength of the firm’s long-term assets to determine the solvency of the firm. | |||||
Deferred taxes liabilities | 1,531 | 2,016 | 1,787 | 1,994 | 2,386 | Many firm’s go through periods of low liquidity and are more than able to rebound from this. | |||||
Other long-term liabilities | 1,655 | 1,962 | 3,301 | 5,798 | 8,535 | Remember: Liquidity is only about short-term obligations, and Solvency is about the long-term obligations | |||||
Total non-current liabilities | 15,675 | 18,161 | 20,301 | 45,718 | 50,708 | ||||||
Total liabilities | 43,764 | 52,060 | 64,117 | 103,601 | 119,099 | Liquidity Ratios | Quick Ratio | Solvency Ratios: Debt Ratio, Debt-to-Equity, | Times Interest Earned | ||
Common stock | |||||||||||
Additional paid-in capital | 11,135 | 13,394 | 17,186 | 21,389 | 26,791 | Healthy firm’s are normally both solvent and possess adequate liquidity. However, it is very possible for a healthy company | |||||
Retained earnings | 1,949 | 2,545 | 4,916 | 8,636 | 19,625 | to have low liquidity. It is also possible for a firm with a very healthy liquidity to go bankrupt because it could not cover its | |||||
Treasury stock | (1,837) | long-term debt. | |||||||||
Accumulated other comprehensive income | (511) | (723) | (985) | (484) | (1,035) | ||||||
Total stockholders’ equity | 10,741 | 13,384 | 19,285 | 27,709 | 43,549 | Long-term assets represent the value of a firm’s property, equipment and other capital assets and investments, minus depreciation | |||||
TOTAL LIABILITIES AND SHAREHOLDERS EQUITY | what the firm owns – used for the production of goods or services | ||||||||||
Short-term assets are a firm’s cash, short-term investments, receivables, and inventories, | |||||||||||
a firm’s revenue from operations – used to facilitate day-to-day operational expenses and short-term investments. | |||||||||||
Balance Sheet Analysis | |||||||||||
List your observations from the Balance Sheet Analysis here: | The textbook’s definitions of Income Statement and Balance Sheet are a little confusing. | ||||||||||
Income Statement = Financial summary of a firms operating results | |||||||||||
Balance Sheet = Summary of firm’s financial position | |||||||||||
Just remember that the Income Statement summarizes the firm’s income | |||||||||||
and the Balance Sheet balances the firm’s Assets with its Debt and Equity | |||||||||||
The Common-Size Balance Sheet expresses the Balance Sheet as a percentage of | Total Assets | ||||||||||
Specifically, the asset part of the Balance Sheet is expressed in terms of Total Assets | |||||||||||
and the Liabilities and Equity portion is expressed in terms of Total Liabilities and Shareholder’s Equity | |||||||||||
But, since Total Assets = Total Liabilities and Shareholder’s Equity, it’s easier to just say it is a percentage of Total Assets. | |||||||||||
(Divide everything by Total Assets or Total Liabilities and Shareholder’s Equity) |
Part 1 – Ratio Calculations | ||||||
Ratio Formulas (To save you a little bit of time) | Ratios for Mystery Company | Industry | ||||
Numerators | Denominators | Averages | ||||
Profitability Ratios | ||||||
Gross Profit Margin | 27.4% | |||||
Operating Profit Margin | Operating Profits | 6.0% | ||||
Net Profit Margin | Earnings Available to SH | 2.9% | ||||
Earnings per Share (EPS) | # of shares outstanding | 3.92 | ||||
Return on Assets ( | ROA | 6.1% | ||||
Return on Equity ( | ROE | Common Stock Equity | 16.9% | |||
Current Ratio | Current Assets | Current Liabilities | 0.98 | |||
Current Assets- | Inventory | 0.32 | ||||
Debt Ratios | ||||||
Debt to Total Assets | Total Liabilities (Debt) | 64.0% | ||||
Equity Multiplier (FLM) | Shareholder Equity | 2.80 | ||||
EBIT (Operating Income) | 8.10 | |||||
Activity Ratios | ||||||
Average Collection Period | Accounts Receivable | (Sales / | 365 | 4.78 | ||
Accounts Payable | 8.07 | |||||
Days AP | AP Turnover | 45.24 | ||||
Inventory Turnover | 7.07 | |||||
Days Inventory | 51.64 | |||||
Total Asset Turnover | 2.05 | |||||
Market Ratios | ||||||
Price/Earnings (P/E) | Market Price per Share | 16.48 | ||||
Market/Book (M/B) | (Book Value/Outstanding Shares) | 3.21 | ||||
Modified DuPont Analysis | ||||||
From above | =D32 | |||||
Total Asset Turnover (TAT) | =D15 | |||||
Net Profit Margin * TAT | =D38 * D39 | |||||
=D26 | ||||||
ROA * FLM | =D40*D41 | |||||
Part 2 – Common-Size Statements | Go to “Income Statement” tab and “Balance Sheet” tab to complete these. | |||||
Part 3 – Ratio Analysis | ||||||
Liquidity: | ||||||
Each Ratio Analysis should include a time series analysis, a comparison to the industry, and an overall assessment for the category. | ||||||
Activity: | ||||||
Leverage and Coverage: | ||||||
Profitability: | ||||||
Market: | ||||||
DuPont System Analysis: | ||||||
Part 4 – Sum it All Up | ||||||
We provide professional writing services to help you score straight A’s by submitting custom written assignments that mirror your guidelines.
Get result-oriented writing and never worry about grades anymore. We follow the highest quality standards to make sure that you get perfect assignments.
Our writers have experience in dealing with papers of every educational level. You can surely rely on the expertise of our qualified professionals.
Your deadline is our threshold for success and we take it very seriously. We make sure you receive your papers before your predefined time.
Someone from our customer support team is always here to respond to your questions. So, hit us up if you have got any ambiguity or concern.
Sit back and relax while we help you out with writing your papers. We have an ultimate policy for keeping your personal and order-related details a secret.
We assure you that your document will be thoroughly checked for plagiarism and grammatical errors as we use highly authentic and licit sources.
Still reluctant about placing an order? Our 100% Moneyback Guarantee backs you up on rare occasions where you aren’t satisfied with the writing.
You don’t have to wait for an update for hours; you can track the progress of your order any time you want. We share the status after each step.
Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.
Although you can leverage our expertise for any writing task, we have a knack for creating flawless papers for the following document types.
From brainstorming your paper's outline to perfecting its grammar, we perform every step carefully to make your paper worthy of A grade.
Hire your preferred writer anytime. Simply specify if you want your preferred expert to write your paper and we’ll make that happen.
Get an elaborate and authentic grammar check report with your work to have the grammar goodness sealed in your document.
You can purchase this feature if you want our writers to sum up your paper in the form of a concise and well-articulated summary.
You don’t have to worry about plagiarism anymore. Get a plagiarism report to certify the uniqueness of your work.
Join us for the best experience while seeking writing assistance in your college life. A good grade is all you need to boost up your academic excellence and we are all about it.
We create perfect papers according to the guidelines.
We seamlessly edit out errors from your papers.
We thoroughly read your final draft to identify errors.
Work with ultimate peace of mind because we ensure that your academic work is our responsibility and your grades are a top concern for us!
Dedication. Quality. Commitment. Punctuality
Here is what we have achieved so far. These numbers are evidence that we go the extra mile to make your college journey successful.
We have the most intuitive and minimalistic process so that you can easily place an order. Just follow a few steps to unlock success.
We understand your guidelines first before delivering any writing service. You can discuss your writing needs and we will have them evaluated by our dedicated team.
We write your papers in a standardized way. We complete your work in such a way that it turns out to be a perfect description of your guidelines.
We promise you excellent grades and academic excellence that you always longed for. Our writers stay in touch with you via email.