Place your ratio calculations in the MS Excel table template for Mastercard for 2017 -2019.? ? Provide the formula used to calc
Directions
· Place your ratio calculations in the MS Excel table template for Mastercard for 2017 -2019.
· Provide the formula used to calculate each ratio in the column to the right of your calculations. For example, for calculating the Gross Margin for a company, you would enter Gross Profit / Sales (gross profit divided by sales).
· When calculating values, you should use the SUM function in MS Excel and reference the values directly from the accounting statements provided. Attached you will find an example for PayPal and the calculations for Gross Margin and Operating Margin using MS Excel SUM function.
Ratios
| 2017 | 2018 | 2019 | Formulas | |
| PROFITABILITY RATIOS | ||||
| MC GROSS MARGIN | ||||
| MC OPERATION MARGIN | ||||
| MC NETPROFIT MARGIN | ||||
| LIQUIDITY RATIO | ||||
| MC CURRENT RATIO | ||||
| MC CASH RATIO | ||||
| LEVERAGE RATIOS | ||||
| MC DEBT TO EQUITY | ||||
| OPERATION RETURNS | ||||
| MC ROE | ||||
| MC ROA | ||||
MA Income Statement
| MA_IncomeStatement_Annual_As_Originally_Reported | 2017 | 2018 | 2019 |
| Gross Profit | 9,810,000,000 | 11,736,000,000 | 13,346,000,000 |
| Total Revenue | 12,497,000,000 | 14,950,000,000 | 16,883,000,000 |
| Business Revenue | 15,492,000,000 | 18,483,000,000 | 20,856,000,000 |
| Sales Return and Allowances/Discounts | -5,848,000,000 | -6,881,000,000 | -8,097,000,000 |
| Other Revenue | 2,853,000,000 | 3,348,000,000 | 4,124,000,000 |
| Cost of Revenue | -2,687,000,000 | -3,214,000,000 | -3,537,000,000 |
| Staff Cost, Cost of Revenue | -2,687,000,000 | -3,214,000,000 | -3,537,000,000 |
| Operating Income/Expenses | -3,067,000,000 | -3,362,000,000 | -3,650,000,000 |
| Selling, General and Administrative Expenses | -2,631,000,000 | -2,903,000,000 | -3,128,000,000 |
| General and Administrative Expenses | -1,378,000,000 | -1,619,000,000 | -1,747,000,000 |
| Legal, Accounting, Auditing and Consulting Fees | -355,000,000 | -377,000,000 | -447,000,000 |
| Selling and Marketing Expenses | -898,000,000 | -907,000,000 | -934,000,000 |
| Staff Costs | 0 | 0 | 0 |
| Depreciation, Amortization and Depletion | -436,000,000 | -459,000,000 | -522,000,000 |
| Depreciation and Amortization | -436,000,000 | -459,000,000 | -522,000,000 |
| Provision Expense/Write-Back | |||
| Provision for Doubtful Accounts | |||
| Total Operating Profit/Loss | 6,743,000,000 | 8,374,000,000 | 9,696,000,000 |
| Non-Operating Income/Expenses, Total | -221,000,000 | -1,170,000,000 | 35,000,000 |
| Total Net Finance Income/Expense | -154,000,000 | -186,000,000 | -224,000,000 |
| Net Interest Income/Expense | -154,000,000 | -186,000,000 | -224,000,000 |
| Interest Expense Net of Capitalized Interest | -154,000,000 | -186,000,000 | -224,000,000 |
| Interest Income | |||
| Net Investment Income | -50,000,000 | 158,000,000 | 232,000,000 |
| Gain/Loss on Investments and Other Financial Instruments | 167,000,000 | ||
| Gain/Loss on Foreign Exchange | -106,000,000 | 36,000,000 | -32,000,000 |
| Other Investment Income | 56,000,000 | 122,000,000 | 97,000,000 |
| Irregular Income/Expenses | -15,000,000 | -1,128,000,000 | 0 |
| Litigation Income/Expense | -15,000,000 | -1,128,000,000 | 0 |
| Other Income/Expense, Non-Operating | -2,000,000 | -14,000,000 | 27,000,000 |
| Pretax Income | 6,522,000,000 | 7,204,000,000 | 9,731,000,000 |
| Provision for Income Tax | -2,607,000,000 | -1,345,000,000 | -1,613,000,000 |
| Net Income from Continuing Operations | 3,915,000,000 | 5,859,000,000 | 8,118,000,000 |
| Net Income after Extraordinary Items and Discontinued Operations | 3,915,000,000 | 5,859,000,000 | 8,118,000,000 |
| Net Income after Non-Controlling/Minority Interests | 3,915,000,000 | 5,859,000,000 | 8,118,000,000 |
| Net Income Available to Common Stockholders | 3,915,000,000 | 5,859,000,000 | 8,118,000,000 |
| Diluted Net Income Available to Common Stockholders | 3,915,000,000 | 5,859,000,000 | 8,118,000,000 |
| Non-Controlling/Minority Interests | |||
| Reported Total Revenue | 12,497,000,000 | 14,950,000,000 | 16,883,000,000 |
| Reported Operating Expense | -5,875,000,000 | -7,668,000,000 | -7,219,000,000 |
| Reported Total Operating Profit/Loss | 6,622,000,000 | 7,282,000,000 | 9,664,000,000 |
| Reported Normalized Income | 4,906,000,000 | 6,792,000,000 | 7,937,000,000 |
| Reported Effective Tax Rate | 0 | 0 | 0 |
| Reported Normalized Operating Profit | |||
| Basic EPS | 3.67 | 5.63 | 7.98 |
| Diluted EPS | 3.65 | 5.60 | 7.94 |
| Basic WASO | 1,067,000,016 | 1,041,000,015 | 1,017,000,015 |
| Diluted WASO | 1,072,000,016 | 1,047,000,015 | 1,022,000,015 |
| Fiscal year ends in Dec 31 | USD |
MA Balance Sheet
| MA_BalanceSheet_Annual_As_Originally_Reported | 2017 | 2018 | 2019 |
| Total Assets | 21,329,000,000 | 24,860,000,000 | 29,236,000,000 |
| Total Current Assets | 13,797,000,000 | 16,171,000,000 | 16,902,000,000 |
| Cash, Cash Equivalents and Short Term Investments | 7,782,000,000 | 8,378,000,000 | 7,676,000,000 |
| Cash and Cash Equivalents | 5,933,000,000 | 6,682,000,000 | 6,988,000,000 |
| Short Term Investments | 1,849,000,000 | 1,696,000,000 | 688,000,000 |
| Available-for-Sale Securities, Current | 1,849,000,000 | 1,696,000,000 | 688,000,000 |
| Held-to-Maturity Securities, Current | |||
| Cash Restricted or Pledged, Current | 1,631,000,000 | 1,633,000,000 | 1,954,000,000 |
| Trade and Other Receivables, Current | 3,344,000,000 | 4,728,000,000 | 5,509,000,000 |
| Trade/Accounts Receivable, Current | 3,344,000,000 | 4,728,000,000 | 5,509,000,000 |
| Taxes Receivable, Current | |||
| Other Receivables, Current | |||
| Prepayments and Deposits, Current | 77,000,000 | 51,000,000 | 105,000,000 |
| Other Current Assets | 963,000,000 | 1,381,000,000 | 1,658,000,000 |
| Deferred Tax Assets, Current | |||
| Deferred Costs/Assets, Current | |||
| Total Non-Current Assets | 7,532,000,000 | 8,689,000,000 | 12,334,000,000 |
| Net Property, Plant and Equipment | 829,000,000 | 921,000,000 | 1,828,000,000 |
| Gross Property, Plant and Equipment | 1,543,000,000 | 1,768,000,000 | 2,928,000,000 |
| Properties | 621,000,000 | 696,000,000 | 808,000,000 |
| Buildings and Improvements | 455,000,000 | 481,000,000 | 505,000,000 |
| Leasehold and Improvements | 166,000,000 | 215,000,000 | 303,000,000 |
| Machinery, Furniture and Equipment | 81,000,000 | 85,000,000 | 92,000,000 |
| Furniture, Fixtures and Office Equipment | 81,000,000 | 85,000,000 | 92,000,000 |
| Other Equipment | 0 | 0 | 0 |
| Leased Property, Plant and Equipment | 810,000,000 | ||
| Other Property, Plant and Equipment | 841,000,000 | 987,000,000 | 1,218,000,000 |
| Accumulated Depreciation and Impairment | -714,000,000 | -847,000,000 | -1,100,000,000 |
| Accumulated Depreciation | -714,000,000 | -847,000,000 | -1,100,000,000 |
| Net Intangible Assets | 4,155,000,000 | 3,895,000,000 | 5,438,000,000 |
| Gross Goodwill and Other Intangible Assets | 5,312,000,000 | 5,070,000,000 | 6,734,000,000 |
| Goodwill | 3,035,000,000 | 2,904,000,000 | 4,021,000,000 |
| Intangibles other than Goodwill | 2,277,000,000 | 2,166,000,000 | 2,713,000,000 |
| Software and Technology | 1,572,000,000 | 1,514,000,000 | 1,884,000,000 |
| Customer Relationships | 648,000,000 | 606,000,000 | 785,000,000 |
| Other Intangible Assets | 27,000,000 | 46,000,000 | 44,000,000 |
| Trademarks and Patents | 30,000,000 | ||
| Accumulated Amortization and Impairment | -1,157,000,000 | -1,175,000,000 | -1,296,000,000 |
| Accumulated Amortization of Intangible Assets | -1,157,000,000 | -1,175,000,000 | -1,296,000,000 |
| Accumulated Amortization of Intangibles other than Goodwill | -1,157,000,000 | -1,175,000,000 | -1,296,000,000 |
| Accumulated Amortization of Software and Technology | -888,000,000 | -898,000,000 | -988,000,000 |
| Accumulated Amortization of Customer Relationships | -214,000,000 | -232,000,000 | -264,000,000 |
| Accumulated Amortization of Other Intangible Assets | -26,000,000 | -45,000,000 | -44,000,000 |
| Accumulated Amortization of Trademarks and Patents | -29,000,000 | ||
| Total Long Term Investments | 249,000,000 | 337,000,000 | 914,000,000 |
| Investment in Financial Assets, Non-Current | 249,000,000 | 337,000,000 | 914,000,000 |
| Equity Securities/Shares, Non-Current | 249,000,000 | 337,000,000 | 914,000,000 |
| Financial Investment Securities, Available-for-Sale & Held-to-Maturity, Non-Current | |||
| Available-for-Sale Securities, Non-Current | |||
| Held-to-Maturity Securities, Non-Current | |||
| Trade and Other Receivables, Non-Current | 178,000,000 | 298,000,000 | 460,000,000 |
| Taxes Receivable, Non-Current | 178,000,000 | 298,000,000 | 460,000,000 |
| Deferred Tax Assets, Non-Current | 250,000,000 | 570,000,000 | 543,000,000 |
| Other Non-Current Assets | 1,519,000,000 | 2,668,000,000 | 3,151,000,000 |
| Prepayments and Deposits, Non-Current | 352,000,000 | 0 | |
| Prepayments, Non-Current | 352,000,000 | 0 | |
| Deferred Costs/Assets, Non-Current | |||
| Total Liabilities | 15,832,000,000 | 19,442,000,000 | 23,319,000,000 |
| Total Current Liabilities | 8,793,000,000 | 11,593,000,000 | 11,904,000,000 |
| Payables and Accrued Expenses, Current | 6,916,000,000 | 9,064,000,000 | 9,606,000,000 |
| Trade and Other Payables, Current | 2,470,000,000 | 2,884,000,000 | 3,535,000,000 |
| Trade/Accounts Payable, Current | 933,000,000 | 537,000,000 | 489,000,000 |
| Taxes Payable, Current | 194,000,000 | 158,000,000 | 332,000,000 |
| Other Payable, Current | 1,343,000,000 | 2,189,000,000 | 2,714,000,000 |
| Amount Due to Related Parties/Shareholders, Current | |||
| Accrued Expenses, Current | 4,446,000,000 | 6,180,000,000 | 6,071,000,000 |
| Financial Liabilities, Current | 500,000,000 | 0 | |
| Current Debt and Capital Lease Obligation | 500,000,000 | 0 | |
| Current Portion of Long Term Debt and Capital Lease | 500,000,000 | 0 | |
| Current Portion of Long Term Debt | 500,000,000 | 0 | |
| Other Current Portion of LT Debt | 500,000,000 | 0 | |
| Other Current Liabilities | 1,877,000,000 | 2,029,000,000 | 2,298,000,000 |
| Provisions, Current | |||
| Provision for Employee Entitlements, Current | |||
| Total Non-Current Liabilities | 7,039,000,000 | 7,849,000,000 | 11,415,000,000 |
| Financial Liabilities, Non-Current | 5,424,000,000 | 5,834,000,000 | 8,527,000,000 |
| Long Term Debt and Capital Lease Obligation | 5,424,000,000 | 5,834,000,000 | 8,527,000,000 |
| Long Term Debt | 5,424,000,000 | 5,834,000,000 | 8,527,000,000 |
| Notes Payables, Non-Current | 5,834,000,000 | 8,527,000,000 | |
| Tax Liabilities, Non-Current | 106,000,000 | 67,000,000 | 85,000,000 |
| Deferred Tax Liabilities, Non-Current | 106,000,000 | 67,000,000 | 85,000,000 |
| Other Non-Current Liabilities | 1,438,000,000 | 1,877,000,000 | 2,729,000,000 |
| Preferred Securities Outside Stock Equity | 71,000,000 | 71,000,000 | 74,000,000 |
| Total Equity | 5,497,000,000 | 5,418,000,000 | 5,917,000,000 |
| Equity Attributable to Parent Stockholders | 5,468,000,000 | 5,395,000,000 | 5,893,000,000 |
| Paid in Capital | -16,399,000,000 | -21,170,000,000 | -27,418,000,000 |
| Capital Stock | 4,365,000,000 | 4,580,000,000 | 4,787,000,000 |
| Common Stock | 0 | 0 | |
| Additional Paid in Capital/Share Premium | 4,365,000,000 | 4,580,000,000 | 4,787,000,000 |
| Treasury Stock | -20,764,000,000 | -25,750,000,000 | -32,205,000,000 |
| Retained Earnings/Accumulated Deficit | 22,364,000,000 | 27,283,000,000 | 33,984,000,000 |
| Reserves/Accumulated Comprehensive Income/Losses | -497,000,000 | -718,000,000 | -673,000,000 |
| Cumulative Foreign Exchange Translation Reserves/Accum. Comp. Inc | |||
| Available for Sale Financial Investments Reserves/Accum. Comp. Inc | |||
| Pension and Other Post-Retirement Benefit Plans Reserves/Accum. Comp. Inc | |||
| Non-Controlling/Minority Interests | 29,000,000 | 23,000,000 | 24,000,000 |
| Common Shares Issued | 1,396,000,000 | 1,399,000,000 | 1,402,414,514 |
| Common Shares Outstanding | 1,054,000,000 | 1,031,000,000 | 1,007,414,514 |
| Common Shares Treasury | 342,000,000 | 368,000,000 | 395,000,000 |
| Common Stock, with Par Value | |||
| Fiscal year ends in Dec 31 | USD |
MA Cash Flow
| MA_CashFlow_Annual_As_Originally_Reported | 2017 | 2018 | 2019 |
| Cash Flow from Operating Activities, Indirect | 5,555,000,000 | 6,223,000,000 | 8,183,000,000 |
| Net Cash Flow from Continuing Operating Activities, Indirect | 5,555,000,000 | 6,223,000,000 | 8,183,000,000 |
| Cash Generated from Operating Activities | 5,555,000,000 | 6,223,000,000 | 8,183,000,000 |
| Income/Loss before Non-Cash Adjustment | 3,915,000,000 | 5,859,000,000 | 8,118,000,000 |
| Total Adjustments for Non-Cash Items | 1,926,000,000 | 1,677,000,000 | 1,763,000,000 |
| Depreciation, Amortization and Depletion, Non-Cash Adjustment | 437,000,000 | 459,000,000 | 522,000,000 |
| Depreciation and Amortization, Non-Cash Adjustment | 437,000,000 | 459,000,000 | 522,000,000 |
| Stock-Based Compensation, Non-Cash Adjustment | 176,000,000 | 196,000,000 | 250,000,000 |
| Taxes, Non-Cash Adjustment | 86,000,000 | -244,000,000 | -7,000,000 |
| Net Investment Income/Loss, Non-Cash Adjustment | -167,000,000 | ||
| Gain/Loss on Financial Instruments, Non-Cash Adjustment | -167,000,000 | ||
| Other Non-Cash Items | 1,227,000,000 | 1,266,000,000 | 1,165,000,000 |
| Excess Tax Benefit from Stock-Based Compensation, Non-Cash Adjustment | |||
| Changes in Operating Capital | -286,000,000 | -1,313,000,000 | -1,698,000,000 |
| Change in Trade and Other Receivables | -726,000,000 | -1,515,000,000 | -892,000,000 |
| Change in Trade/Accounts Receivable | -726,000,000 | -1,395,000,000 | -690,000,000 |
| Change in Tax Receivables | -120,000,000 | -202,000,000 | |
| Change in Prepayments and Deposits | -1,402,000,000 | -1,769,000,000 | -1,661,000,000 |
| Change in Cash Restricted or Pledged | -6,000,000 | 290,000,000 | |
| Change in Payables and Accrued Expenses | 1,258,000,000 | 2,258,000,000 | 430,000,000 |
| Change in Trade and Other Payables | 684,000,000 | 950,000,000 | 435,000,000 |
| Change in Trade/Accounts Payable | 290,000,000 | 101,000,000 | -42,000,000 |
| Change in Other Payables | 394,000,000 | 849,000,000 | 477,000,000 |
| Change in Taxes Payable | 0 | 0 | 0 |
| Change in Accrued Expenses | 574,000,000 | 1,308,000,000 | -5,000,000 |
| Change in Other Operating Capital | 584,000,000 | -281,000,000 | 135,000,000 |
| Change in Other Current Liabilities | |||
| Cash Flow from Investing Activities | -1,779,000,000 | -506,000,000 | -1,640,000,000 |
| Cash Flow from Continuing Investing Activities | -1,779,000,000 | -506,000,000 | -1,640,000,000 |
| Capital Expenditure, Reported | -123,000,000 | -174,000,000 | -306,000,000 |
| Purchase/Sale and Disposal of Property, Plant and Equipment, Net | -300,000,000 | -330,000,000 | -422,000,000 |
| Purchase of Property, Plant and Equipment | -300,000,000 | -330,000,000 | -422,000,000 |
| Purchase/Sale of Business, Net | -1,175,000,000 | 0 | -1,440,000,000 |
| Purchase/Acquisition of Business | -1,175,000,000 | 0 | -1,440,000,000 |
| Purchase/Sale of Investments, Net | -182,000,000 | 12,000,000 | 532,000,000 |
| Purchase of Investments | -2,006,000,000 | -1,900,000,000 | -1,325,000,000 |
| Sale of Investments | 1,824,000,000 | 1,912,000,000 | 1,857,000,000 |
| Other Investing Cash Flow | 1,000,000 | -14,000,000 | -4,000,000 |
| Purchase/Sale of Equity Investments | 0 | 0 | 0 |
| Purchase of Joint Venture/Associate | 0 | 0 | 0 |
| Change in Restricted Cash and Cash Equivalents | |||
| Purchase/Sale of Intangibles, Net | |||
| Purchase of Intangibles | |||
| Cash Flow from Financing Activities | -4,764,000,000 | -4,966,000,000 | -5,867,000,000 |
| Cash Flow from Continuing Financing Activities | -4,764,000,000 | -4,966,000,000 | -5,867,000,000 |
| Issuance of/Payments for Common Stock, Net | -3,762,000,000 | -4,933,000,000 | -6,497,000,000 |
| Payments for Common Stock | -3,762,000,000 | -4,933,000,000 | -6,497,000,000 |
| Issuance of/Repayments for Debt, Net | -64,000,000 | 991,000,000 | 2,224,000,000 |
| Issuance of/Repayments for Long Term Debt, Net | -64,000,000 | 991,000,000 | 2,224,000,000 |
| Proceeds from Issuance of Long Term Debt | 991,000,000 | 2,724,000,000 | |
| Repayments for Long Term Debt | -64,000,000 | 0 | -500,000,000 |
| Net Movement in Non-Controlling/Minority Interest | |||
| Cash Dividends and Interest Paid | -942,000,000 | -1,044,000,000 | -1,345,000,000 |
| Cash Dividends Paid | -942,000,000 | -1,044,000,000 | -1,345,000,000 |
| Common Stock Dividends Paid | -942,000,000 | -1,044,000,000 | -1,345,000,000 |
| Proceeds from Issuance/Exercising of Stock Options/Warrants | 57,000,000 | 104,000,000 | 126,000,000 |
| Other Financing Cash Flow | -53,000,000 | -84,000,000 | -375,000,000 |
| Excess Tax Benefit from Share-Based Compensation, Financing Activities | 0 | ||
| Cash and Cash Equivalents, End of Period | 5,933,000,000 | 8,337,000,000 | 8,969,000,000 |
| Change in Cash | -988,000,000 | 751,000,000 | 676,000,000 |
| Effect of Exchange Rate Changes | 200,000,000 | -6,000,000 | -44,000,000 |
| Cash and Cash Equivalents, Beginning of Period | 6,721,000,000 | 7,592,000,000 | 8,337,000,000 |
| Change in Cash As Reported, Supplemental | -788,000,000 | 745,000,000 | 632,000,000 |
| Fiscal year ends in Dec 31 | USD |
,
Unit 3: Individual Assignment – Mastercard Ratios
Introduction
As you work towards your final team project, there are sections of the project that will require individual work. Using the feedback from the PayPal ratios assignment in Unit 2, individually (not as a team) you will use the attached MS Excel file to create your ratios for your final project. Using this file gather and report your ratio and financial performance data for Mastercard for 2017 – 2019. Within the Mastercard Financial Ratios MS Excel file you will find four sheets; ratios, income statement, balance sheet, cash flow. Using the accounting statements, you will use MS Excel to calculate the ratios and enter into the ratios sheet.
· STUDENTS Mastercard Financial Ratios Download STUDENTS Mastercard Financial Ratios
This is an Individual Assignment
Directions
· Place your ratio calculations in the MS Excel table template for Mastercard for 2017 -2019.
· Provide the formula used to calculate each ratio in the column to the right of your calculations. For example, for calculating the Gross Margin for a company, you would enter Gross Profit / Sales (gross profit divided by sales).
· When calculating values, you should use the SUM function in MS Excel and reference the values directly from the accounting statements provided. Attached you will find an example for PayPal and the calculations for Gross Margin and Operating Margin using MS Excel SUM function.
· Research Project – Mastercard Ratios
|
Research Project – Mastercard Ratios |
||||||
|
Criteria |
Ratings |
Pts |
||||
|
This criterion is linked to a Learning OutcomeCalculate Ratios |
|
25 pts |
||||
|
Total Points: 25 |
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
