Data Analytics presentation paper

profileLu101
LewisJaren-W1Excel.xlsx
Home>Computer Science homework help>Data Analytics presentation paper

Week One

Note: These templates are provided for your convenience; you may not need every line.
Name of Company: Oracle
Stock Symbol: ORCL
Income Statement Balance Sheet Statement of Cash Flows
Current Year Previous Year %-Change in Current Year Current Year Previous Year Current Year Previous Year
5/31/20 5/31/19 5/31/20 5/31/19 5/31/20 5/31/19
$ Amounts % $ Amounts % Assets $ Amounts % $ Amounts % $ Amounts $ Amounts
Revenue 39,068 100% 39,506 100% -1% Cash and Equivalents 37,239 32% 20,514 19% Total Cash Flow from Operating Activities 13,139 14,551
Cost of Revenue (7,938) -20% (7,995) -20% -1% Short-term Investments 5,818 5% 17,313 16%
Gross Profit 31,130 80% 31,511 80% -1% Receivables, net 5,551 5% 5,134 5% Total Cash Flow from Investing Activities 9,843 26,557
Inventory - 0 0% - 0 0%
Research and Development (6,067) -16% (6,026) -15% 1% Other Current Assets 3,532 3% 3,425 3% Total Cash Flow from Financing Activities (6,132) (42,056)
Selling, General, and Administration (9,275) -24% (9,774) -25% -5% Total Current Assets 52,140 45% 46,386 43%
Other (Plug) (1,586) (1,689) -6% Long-term Investment Change in Cash and Equivalents 16,850 (948)
Total Operating Expenses (16,928) -43% (17,489) -44% -3% Property, Plant, & Equipment 6,244 5% 6,252 6%
Operating Income or (Loss) 14,202 36% 14,022 35% 1% Goodwill 47,507 41% 49,058 45% Beginning Cash 20,514 21,620
Other Assets (Plug) 9,547 7,013
Other Income/Expense, net (144) -0% 328 1% -144% Total Assets 115,438 100% 108,709 100% Effect of Exchange Rate Changes (125) (158)
Earnings Before Interest and Taxes (EBIT) 14,058 36% 14,350 36% -2%
Interest Expense (1,995) -5% (2,082) -5% -4% Liabilities Cash at End of Period 37,239 20,514
Income Before Tax 12,063 31% 12,268 31% -2% Accounts Payable 637 1% 580 1%
Income Tax Expense (1,928) (1,185) 63% Short/Current LT Debt 2,371 2% 4,494 4%
Net Income from Continuing Operations 10,135 26% 11,083 28% -9% Other Current Liabilities 14,192 12% 13,556 12%
Misc. Difference (Plug) - 0 0% - 0 0% 0% Total Current Liabilities 17,200 15% 18,630 17%
Net Income 10,135 26% 11,083 28% -9% Long-Term Debt 69,226 60% 51,673 48%
Other Liabilities (Plug) 16,295 14% 16,043 15%
Total Liabilities 102,721 89% 86,346 79%
Note: As a check figure, all items in blue should be 100%. Stockholders' Equity
Preferred Stock - 0 0% - 0 0%
Common Stock 26,486 23% 26,909 25%
Additional Paid-in Capital 0% 0%
Retained Earnings (12,696) -11% (3,496) -3%
Treasury Stock 0% 0%
Other Stockholders' Equity (Plug) (1,073) -1% (1,050) -1%
Total Stockholders' Equity 12,717 11% 22,363 21%
Total Liabilities & SE 115,438 100% 108,709 100%

Week Two

Ratio Analysis Current Year Previous Year Blue cells require information from the financial statements (see Week 1); Current Year Previous Year
[Formulas and discussion are found in Parino: Chapter 4] Tan cells are calculated amounts.
5/30/20 5/30/19 5/30/20 5/30/19
Liquidity Ratios Profitability Ratios
Working Capital (Current Assets - Current Liabilities) Current Assets 52,140,000 46,386,000 Gross Profit Margin (Gross Profit / Sales) Gross Profit 31,130,000 0.7968158083 31,511,000 0.7976256771
Less: Current Liabilities 17,200,000 18,630,000 Sales 39,068,000 39,506,000
Working Capital 34,940,000 27,756,000
Profit Margin (Net Income / Sales) Net Income 10,135,000 0.2594194737 11,083,000 0.2805396649
Current Ratio (Current Assets / Current Liabilities) Current Assets 52,140,000 3.0313953488 46,386,000 2.489855 Sales 39,068,000 39,506,000
Current Liabilities 17,200,000 18,630,000
Return on Assets (Net Income / Total Assets) Net Income 10,135,000 0.0877960464 11,083,000 0.1019510804
Quick Ratio (Quick Assets / Current Liabilities) Quick Assets 48,608,000 2.8260465116 42,961,000 2.306012 Total Assets 115,438,000 108,709,000
Current Liabilities 17,200,000 18,630,000
Efficiency Ratios Return on Equity (Net Income / Total Equity) Net Income 10,135,000 0.8394069902 11,083,000 0.508744549
Inventory Turnover (COGS / Inventory): # turns Cost of Goods Sold 7,938,000 7,995,000 Total Stockholders' Equity 12,074,000 21,785,000
Inventory
Dupont Breakdown of ROA and ROE: Profit Margin 0.2594194737 0.2805396649
Days in Inventory (365 / # of Inventory turns) 365 365 365 [Each of these items are ratios calculated on this page] x Total Asset Turnover 0.3384327518 0.3634105732
# of Inventory Turns Return on Assets 0.0877960464 0.1019510804
x Equity Multiplier 9.5608746066 4.9900849208
Accounts Receivable Turnover (Sales / Receivables) Sales 39,068,000 7.0380111692 39,506,000 7.6949746786 Return on Equity 0.1045929417 0.2003973912
Receivables 5,551,000 5,134,000
Market Value Indicators
Average Collection Period (365 / # of Receivables turns) 365 365 51.8612419371 365 47.4335543968 Earnings Per Share (EPS): [Net Income / # Shares] Net Income 10,135,000 3.1563375895
# of Receivables Turns 7.0380111692 7.6949746786 # of Shares Outstanding 3,211,000
Total Asset Turnover (Sales / Total Assets) Sales 39,068,000 0.3384327518 39,506,000 0.3634105732 Price / Earnings Ratio (P/E): [Price per Share / EPS Current Stock Price 66.89 21.1922831771
Total Assets 115,438,000 108,709,000 Get stock price from finance.yahoo.com or some other financial site. Earnings Per Share 3.1563375895
Leverage Ratios
Debt to Assets (Total Liabilities / Total Assets) Total Liabilities 102,721,000 0.8898369688 86,346,000 0.7942856617
Total Assets 115,438,000 108,709,000 Payout Ratio (Dividends / Net Income) Current Year Dividends 3070000
Net Income 10,135,000 0.3029107055
Debt to Equity (Total Liabilities / Total Equity) Total Liabilities 102,721,000 8.5076196786 86,346,000 3.9635529034
Total Stockholders Equity 12,074,000 21,785,000
Equity Multiplier (Total Assets / Total Common Equity) Total Assets 115,438,000 9.5608746066 108,709,000 4.9900849208
Total Stockholders Equity 12,074,000 21,785,000
Times Interest Earned (EBIT / Interest Expense) Earnings Before Interest and Taxes 14,058,000 7.0466165414 14,350,000 6.8924111431
Interest Expense 1,995,000 2,082,000

Week Three

Forecasted Income Statement
Name of Company:
Stock Symbol:
Income Statement Forecast for Next Year These assumptions are the expectations for the coming year; use these assumptions to compute the forecasted income statement.
Current Year
5/31/20
$ Amounts % $ Amounts % Assumptions
Revenue 39,068 100% 781,360 100% Sales will grow 20%
Cost of Revenue (7,938) -20% (158,760) -20% Varies with sales…grow by the same %
Gross Profit 31,130 80% 622,600 80% Provide the expected $ amount for each line item.
Research and Development (6,067) -16% (12,134) -2% Fixed cost; management made decision to double next year.
Selling, General, and Administration (9,275) -24% (139,125) -18% Mixed; Grow by 15% Calculate the common-size %'s for the forecasted information.
Other (Plug) (1,586) (1,586) Fixed; no change
Total Operating Expenses (16,928) -43% (152,845) -20%
Operating Income or (Loss) 14,202 36% 469,755 60%
Other Income/Expense, net (144) -0% (144) -0% Fixed; no change from previous year
Earnings Before Interest and Taxes 14,058 36% 469,611 60%
Interest Expense (1,995) -5% (23,981) -3% Varies with debt; calculate as same % of debt as in previous year.
Income Before Tax 12,063 31% 445,630 57%
Income Tax Expense (1,928) 93,582 21% of Income Before Tax
Net Income from Continuing Operations 10,135 26% 539,213 69%
Nonrecurring Events, net - 0 0% - 0 0% zero in the forecast year
Net Income 10,135 26% 539,213 69%
Note: Copy the $-amounts and common-size %'s that you completed in Week One for the most recent year. Based on the assumptions given in green, prepare a forecasted income statement for next year. Let Excel do the work. In other words, use cell references and formulas to calculate your forecasts...don't just type in the numbers. That will allow your supervisor (i.e., your grader) to follow your work.

Week 5

Capital Budgeting Techniques to Evaluate Project
Estimated Cost of Project :
Total Assets of Your Company: 115,438
x project % of total assets 2%
Proposed Capital Expenditure $ 2,309
rounded to nearest $ million = $ 2,000,000
$ (2,000,000)
Opportunity cost of project: 69,226,000 12,717,000 81,943,000 0.1551932441 0.02 0.8448067559 0.51
(assuming 4% return on savings)
3,959,396.68
800000
Payback Period of Project: $ (2,000,000) $ (1,200,000) $ (400,000) $ 400,000 $ 1,200,000 $ 2,000,000 $ 2,800,000 $ 3,600,000 $ 4,400,000 $ 5,200,000 $ 6,000,000
(assuming an $3 million increase in annual net income over the next 10 years)
$ 4
Net Present Value of Project:
(assuming discount rate of 12%; and $3 million increase in annual net income over the next 10 years) $ 18,984,749.38 $ 2,000,000 0.12
$ 16,984,749.38
Can you find the IRR? [the discount rate that results in an NPV = 0?
31%
Time line for your use: 0 1 2 3 4 5 6 7 8 9 10
(amounts are in millions)
3 3 3 3 3 3 3 3 3 3
Cash Outlay = 2% of total assets=

Week 6

Evaluation of Financing Options Determine the required cash flows for each financing alternative: All colored cells require a dollar amount.
Retained Earnings 0 1 2 3 4 5 6 7 8 9 10
(savings earn 4% over next 10 years)
Yr 1 Yr 2 Yr 3 Yr 4 Yr 5 Yr 6 Yr 7 Yr 8 Yr 9 Yr 10 Total
Foregone interest income :
5-Year; 5% Note Payable 0 1 2 3 4 5
(Interest paid annually;
principal due in full at end of 5th year.) Yr 1 Yr 2 Yr 3 Yr 4 Yr 5 Total
Annual Interest Payments (5%):
Maturity Value (principal repayment):
Annual Cash Flows:
Ten Year; 8% Serial Bond Issue
(principal to be paid in ten equal annual installments along with annual interest) Outstanding Principal at Beg. Of Year:
0 1 2 3 4 5 6 7 8 9 10
Yr 1 Yr 2 Yr 3 Yr 4 Yr 5 Yr 6 Yr 7 Yr 8 Yr 9 Yr 10 Total
Annual Interest Payments (8% of Outstanding Principal):
Annual Principal Payment):
Annual Cash Flows:
6% Preferred Stock
(annual dividend is 6%) 0 1 2 3 4 5 6 7 8 9 10
Yr 1 Yr 2 Yr 3 Yr 4 Yr 5 Yr 6 Yr 7 Yr 8 Yr 9 Yr 10 Total
Annual Preferred Dividend:
New Common Stock
0 1 2 3 4 5 6 7 8 9 10
Yr 1 Yr 2 Yr 3 Yr 4 Yr 5 Yr 6 Yr 7 Yr 8 Yr 9 Yr 10 Total
Annual Required Dividend: