Financial Modeling (FIN 355). Take Home Midterm Exam
Rules: Answer all questions. This is an open book exam. However, you should not discuss the questions or potential answers with anyone. If you have any doubts about what you should do, ask me, not your fellow students.
Delivery Procedures: Students should submit your midterm on Canvas by 5:00pm on Sunday March 17, 2024. Only excel file will be accepted. Write your verbal answer in Excel too. Name your file as your last name + first letter of your first name + _ + midterm. Do not send me your midterm through emails. Late submission will NOT be considered.
Questions:
1. (30 pts) Use S&P 500 Company Data.xlsx to answer the following questions (in data file)
- 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.
- 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?
- 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.
- 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.)
- 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?
- 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?
Rhodes Corporation: Income Statements for Year Ending December 31
|
|
2019 |
2018 |
|
Sales |
11,000,000 |
10,000,000 |
|
COGS |
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 |
25% |
2. (35 pts) The financial statements of Rhodes Corporation are shown above.
- 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.
- 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.
- Using Excel’s outlining feature, create an outline on the statement of cash flows that, when collapsed, shows only the subtotals for each section.
- 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;
- 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.
- 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)
- 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)
- Why accumulated AFN increases in part b)? Please explain the phenomenon.