Questions: 1. (30 pts) Use S&P 500 Company Data.xlsx to answer the following questions (in data file) a) Create a calculated column that calculates the total return for the last three years. Name it "Total Return 3 Yr." Use Get & Transform to remove # N/A values from the calculated column. Make a pivot table that shows the three-year total return (average) by GICS Sector. b) Create a pivot column chart that show the data from part a. Which GICS sector has the lowest total return for the last three years? c) Now add the GICS Industry field to the pivot table. Which industry was the best performer in the worst sector over the past three years? Sort the fields in your pivot table so that this sector and industry is at the very top of the table. d) Create a new pivot table from the original data that shows the top 20 GICS Sub-Industries by average of market capitalization (market value). Also include a count of the number of companies in each sub-industry. (Moving Symbol to Values will create a count of Symbol, which gives you the # of count.) e) Use Get & Transform to remove errors from the net profit margin column, and then create a pivot table that shows the average net profit margin by GICS Industry. Which is the most profitable industry? Which is the least profitable industry? f) Create a pivot table that shows beta and the Total Return 3 Yr for each company. Now, copy and paste the data to another location in the same sheet. Create a regular XY scatter chart of the data. Is there a relationship between beta and historical three-year returns? 1 Rhodes Corporation: Income Statements for Year Ending December 31 2019 2018 Sales COGS 11,000,000 10,000,000 9,322,000 8,448,000 Gross profit 1,678,000 1,552,000 Depreciation and amortization 380,000 360,000 Selling & Admin. Expenses 290,000 280,000 Earnings before interest and taxes 1,008,000 912,000 Interest expense 220,000 100,000 Earnings before taxes 788,000 812,000 Taxes 197,000 203,000 Net income 591,000 609,000 Rhodes Corporation: Balance Sheets as of December 31 2019 2018 Assets Cash 842,000 500,000 Short-term investments 110,000 100,000 Accounts receivable 2,750,000 2,500,000 Inventories 1,650,000 1,500,000 Total current assets 5,352,000 4,600,000 Gross plant and equipment 4,813,000 4,375,000 Accumulated Depreciation 1,255,000 875,000 Net plant and equipment 3,558,000 3,500,000 Total assets 8,910,000 8,100,000 Liabilities & Owner's Equity Accounts Payable 1,100,000 1,000,000 Accruals 550,000 500,000 Notes Payable 384,000 200,000 Total current liabilities 2,034,000 1,700,000 Long-term debt 1,100,000 1,000,000 Total liabilities 3,134,000 2,700,000 Common stock 4,312,000 4,400,000 Retained earnings 1,464,000 1,000,000 Total common equity 5,776,000 5,400,000 Total liabilities and equity 8,910,000 8,100,000 Note: Tax rate 2 25% 2. (35 pts) The financial statements of Rhodes Corporation are shown above. a) Recreate the income statement and balance sheet using formulas wherever possible. Each statement should be on a separate worksheet. Please show all dollar amount to thousands of dollars. b) On another sheet, create a statement of cash flows for 2019. Do not enter any numbers directly on this sheet. All formulas should be linked directly to the source on previous worksheets. c) Using Excel's outlining feature, create an outline on the statement of cash flows that, when collapsed, shows only the subtotals for each section. d) Create a common-size statement of cash flows for 2019 that can be switched between using sales and the 2018 cash balance in the denominator. 3. (35 pts) Forecasting Rhodes Corporation financial statement Forecast 2020 income statement and balance sheet using the percent of sales method and the following assumptions: (1) sales in 2020 will be 12.5 million; (2) tax rate keeps the same; (3) each item that changes with sales will be the 2 year average percentage of sales; (4) fixed asset will increase $1,000,000 with a 10 year straight line depreciation schedule with 0 salvage value; (5) the common stock dividends will be $202,000; (6) interest rate on short-term and long-term debt will be 9%; (7) Cash, short-term investment will be the same as 2019; (8) COGS, Selling G&A expenses, A/R, inventory, A/P, Accruals will change in proportion to sales; (9) Notes payable and long-term debt will keep the same; and if there is borrowing need, the company will borrow from long-term debt; (10) the company will not issue stocks in 2020. a) What is the additional funds needed in 2020? Is this a surplus or deficit or balanced? (Without iteration, or borrowing happens at last day of the year) b) Assume that the AFN will be absorbed by long-term debt, set up an iterative worksheet to find total accumulated AFN (borrowing happens during the year) c) Why accumulated AFN increases in part b)? Please explain the phenomenon. 3