Assignment2_AdvCorpFinance
.xlsx
keyboard_arrow_up
School
Western University *
*We aren’t endorsed by this school
Course
5100
Subject
Finance
Date
Jan 9, 2024
Type
xlsx
Pages
18
Uploaded by BarristerIceBadger32
This spreadsheet su
analyzed by Victoria
upports the analysis of the case "Spotify Direct-Listing IPO" as a Chambers, Corey Wheaton, Breena T'ien, Hartley Shapiro and Austin Krausert
Spotify Direct Listing
General First year of forecast in financial model:
2018
10-year cash flow model discounted to December 31, 2017
Expense Growth Rate
10%
Cost Inflation
18%
Net Debt
467
Depreciation
###
Revenue Growth Rates Year
Growth Rate 2018
25%
2019
25%
2020
25%
2021
25%
2022
25%
2023
20%
2024
15%
2025
11%
2026
8%
2027
5%
Valuation - Market Risk Premium 5%
- Shares outstanding (Millions)
178.11
- Share Price
148.4
The terminal value growth rate is 1.5% to 3.0%
Taxes
- No taxes paid through 2022
- Tax rate after 2022 25%
g - IPO
Valuation - Market Risk Premium 5.0%
- Beta without tax
13.86
- Beta with tax
14.13
- Shares outstanding (Millions)
178.11
- Risk-free
2.85%
- Cost of Equity without tax
0.72
- Cost of Equity with tax
0.73
TAKE 2 Without Tax
Company
5 yr Beta
LTM EBITDA
otal Enterprise Valu
Alibaba
2.58
14,222.70
456,971.0
Amazon
1.55
15,039
706,121.7
Apple
1.18
74,174
889,968.7
Baidu
1.77
4,362
68,388.2
Comcast
1.16
27,812
217,888.2
EBay
1.06
2,941
44,746.5
Facebook
0.66
23,228
402,841.3
Netflix
0.96
910.6
127,686.1
Pandora
-0.4
-289
1,527.2
Walt Disney
1.31
16,912
174,522.2
Time Warner
0.92
9,081
94,621.0
Twenty-First Century F
1.26
6540
82,104.3
Twitter
0.8
344.8
18,675.1
Wayfair
1.27
-174.2
5,560.6
1.15
195,103.70
Spotify
Unlevered B 1.06
Levered B 13.86
TAKE 2 With Tax
Company
5 yr Beta
LTM EBITDA
otal Enterprise Valu
Alibaba
2.58
14,222.70
456,971.0
Amazon
1.55
15,039
706,121.7
Apple
1.18
74,174
889,968.7
Baidu
1.77
4,362
68,388.2
Comcast
1.16
27,812
217,888.2
EBay
1.06
2,941
44,746.5
Facebook
0.66
23,228
402,841.3
Netflix
0.96
910.6
127,686.1
Pandora
-0.4
-289
1,527.2
Walt Disney
1.31
16,912
174,522.2
Time Warner
0.92
9,081
94,621.0
Twenty-First Century F
1.26
6540
82,104.3
Twitter
0.8
344.8
18,675.1
Wayfair
1.27
-174.2
5,560.6
1.15
195,103.70
Spotify
Unlevered B 1.08
Levered B 14.13
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Questions
Calculate
Debt-to-equity
Times interest earned
Return on Financial leverage
arrow_forward
Provide explanationations on the gearing and interest coverage ratios by making comparison between 2021 and 2022
arrow_forward
1. Is coverage better or worse in the period from 2015-2021? Why?
arrow_forward
Estimating Share Value Using the DCF Model
Following are forecasts of sales, net operating profit after tax (NOPAT), and net operating assets (NOA) as of December 31, 2018, for Humana.
Note: Complete the entire question in Excel and format each answer to two decimal places. Then enter the answers into the provided spaces below with two decimal places.
Reported
Forecast Horizon Period
Terminal
$
millions
Sales
NOPAT
NOA
2021 2022
2020
2018 2019
$57,472 $58,326 $59,192 $60,072 $60,964
3,179 3,218
3,140
3,052 3,102
4,844
4,657 4,718 4,781
4,592
Period
$61,568
3,244
4,887
Answer the following requirements with the following assumptions:
Assumptions
Terminal period growth rate
1%
Discount rate (WACC)
7.8%
Common shares outstanding
135.60 million
Net nonoperating obligations (NNO) $(5,569) million
Noncontrolling interest
$0 million
NNO is negative because Humana's nonoperating assets exceed its nonoperating liabilities.
(a) Estimate the value of a share of Humana's common stock using the…
arrow_forward
From the balance sheet prepare a proforma income statement where revenues can increase by 2% and the firm can borrow at 5.5%
BALANCE SHEET
2021
Cash and cash equivalents
280
Receivables
2588
Inventory
2516
Other CA
189
TOTAL CA
5573
Fixed assets
5024
TOTAL ASSETS
10597
Accounts payable
4713
Short term debt
78
TOTAL CL
4790
LT debt
921
Shareh. Equity
4886
TOTAL LIAB. AND SHARH. EQUITY
10597
INCOME STATEMENT
2021
Sales
19418
COGS
13136
Depreciations
354
SG&A
4952
EBIT
976
Interest Expenses
52
Tax
268
Net income
656
Pro-forma statement
Pro Forma Forecasts
Actual
Projected
Projected
Projected
Projected
Projected
2021
2022
2023
2024
2025
2026
COGS/REVENUES
SGA/SALES
INVENTORIES/COGS
OTHER CA/SALES
AR/SALES
AP/COGS
SALES/FIXED ASSETS
DEPR/ FIXED ASSETS
EQUITY/INVESTED CAPITAL
ST DEBT/INVESTED…
arrow_forward
Please help me to analyze this Financials
arrow_forward
Here are the abbreviated financial statements for Planner’s Peanuts:
INCOME STATEMENT, 2019
Sales
$
3,500
Cost
2,700
Net income
$
800
BALANCE SHEET, YEAR-END
2018
2019
2018
2019
Assets
$
4,500
$
5,000
Debt
$
833
$
1,000
Equity
3,667
4,000
Total
$
4,500
$
5,000
Total
$
4,500
$
5,000
Assume the payout ratio is 50%.
a. Calculate the internal growth rate where no external debt or equity is to be issued. (Do not round intermediate calculations. Enter your answer as a whole percent.)
b. Calculate the sustainable growth rate where the firm maintains a fixed debt ratio but issues no equity. (Do not round intermediate calculations. Enter your answer as a whole percent.)
arrow_forward
please calculate the following:
2017
2018
2019
2020
Quick ratio
Debt ratio
Interest coverage ratio
Dividend yield
4- current assets- 1,170.7m
4- inventory- 859.9 m
4- current liabilities- 885.8 m
5- total debts- 1,598.8
5- total assets- 2,452.3
6- operating profit- 259.2
6- interest expense/ finance cost- 10.7m
7- annual dividends per share- 118.0 cents (46. cents per share)
7- current share price- $24.71
2018
4- current assets-1,210.5 m
4- inventory- 891.1 m
4- current liabilities- 917.2 m
5- total debts- 1.544.1m
5- total assets 2,491.7 m
6- operating profit- 334.5 m
6- interest expense/ finance cost- 16.6 m
7- annual dividends per share- 132 (46. cents per share)
7- current share price- $21.48
2019
4- current assets- 1,276.5 m
4- inventory- 886.7 m
4- current liabilities- 927.1 mill
5- total debts/ total liabilities- 1,504.7 m
5- total assets- 927.1 mill
6- operating profit/ profit before tax- 359.3 mil
6- interest expense/ finance cost-…
arrow_forward
Liquidity management Bauman Company's total current assets, total current liabilities, and inventory for each of the past 4 years follow
Item
2019
2020
2021
Total current assets
$17,440
$21,630
$22,920
Total current liabilities
12,890
9,090
5.270
Inventory
6,560
Hem
a. Calculate the firm's current and quick ratios for each year. Compare the resulting time series for these measures of liquidity
b. Comment on the firm's liquidity over the 2019-2022 period
c. If you were told that Bauman Company's inventory turnover for each year in the 2019-2022 penod and the industry averages were as follows, would this information support or conflict with your
evaluation in part (b)? Why?
2019
2020
12,630
7,230
a. Calculate the firm's current and quick ratios for each year.
2022
$26,480
16,920
7,510
2021
2022
LLL
arrow_forward
Finance
arrow_forward
Computing trend analysis and return on common equity
Net sales revenue, net income, and common stockholders’ equity for Eyesight Mission Corporation, a manufacturer of contact lenses, follow for a four-year period.
Requirements
Compute trend analyses for each item for 2017-2019. Use 2016 as the base year, and round to the nearest whole percent.
Compute the rate of return on common stockholders’ equity for 2017-2019, rounding to three decimal places.
arrow_forward
Estimating Share Value Using the ROPI Model
Following are forecasts of sales, net operating profit after tax (NOPAT), and net operating assets (NOA) as of December 31, 2018, for Humana.
Note: Complete the entire question in Excel and format each answer to two decimal places. Then enter the answers into the provided spaces below with two decimal places.
Reported
Horizon period
Terminal
millions
Sales
NOPAT
NOA
2018 2019 2020 2021 2022
$56,912 $57,766 $58,632 $59,512 $60,404
2,492 2,542 2,580 2,619 2,658
4,097 4,158 4,221 4,284
4,032
Answer the following requirements with the following assumptions:
Assumptions
Terminal period growth rate
1%
Discount rate (WACC)
7.8%
Common shares outstanding
135.6 million
Net nonoperating obligations (NNO) $(6,129) million
($ millions)
ROPI (NOPAT- [NOABeg x rw])
Present value of horizon ROPI
Cum present value of horizon ROPI $
Present value of terminal ROPI
NOA
Total firm value
Less NNO
Firm equity value
Shares outstanding (millions)
Stock price per…
arrow_forward
-Equity Ratio-Debt to equity ratio-Times interest earned-Return on common stockholders equity
Figure each ratio out
arrow_forward
Can a Company with this Financial Analysis of Data be successful with obtaining a $500.0 million loan?
Image Attached.
arrow_forward
Calculate the relationship between debt and equity for the year 2019 and 2020 and comment
arrow_forward
The _________ is the internal rate of return a firm must earn on its investment in order to maintain the market value of its stock.
a.
gross profit margin
b.
IRR
c.
Cost of Capital
d.
net profit margin
A snapshot from Violet Flowers Ltd.'s financial information reveals the following for years 2018 and 2019:
Item
2018
2019
Long Term Debt
$4,600,000
$4,900,000
Interest expense
$600,500
$870,000
Dividends
$400,000
$590,000
Common Stock
$1,740,000
$1,815,000
Additional paid-in surplus
$4,200,000
$4,500,000
Violet Flowers' FCF for 2019 was:
a.
$300,000
b.
$515,000
c.
$785,000
d.
$270,000
arrow_forward
Gross Profit Percentage
Calculate the company's 2020 gross profit percentage and compare the result to the industry average.
arrow_forward
Current Attempt in Progress
Amy Dyken, controller at Teal Pharmaceutical Industries, a public company, is currently preparing the calculation for basic and diluted
earnings per share and the related disclosure for Teal's financial statements. Below is selected financial information for the fiscal year
ended June 30, 2020.
Teal Pharmaceutical Industries
Selected Balance Sheet Information
June 30, 2020
Long term debt
Notes payable, 9%
$980,000
8% convertible bonds payable
4,960,000
9% bonds payable
5.940,000
Total long term debt
$11.880,000
Shareholders' equity
Preferred stock, 6% cumulative. $50 par value, 105,000 shares authorized, 26.250 shares issued and outstanding
$1,312.500
Common stock, $1 par, 9,800,000 shares authorized, 980,000 shares issued and outstanding
980,000
Additional paid-in capital
4,020,000
Retained earnings
5.930,000
Total shareholders' equity
$12.242.500
The following transactions have also occurred at Teal.
1.
Options were granted on July 1, 2019, to purchase…
arrow_forward
Calculate the financial ratios of D&G Co. for 2019 and interpret the financial position of the firm.
you already answer first three sub ı need rest of them.
arrow_forward
required
using percentage of sales method, prepare 2019 proforma financial statement and determine external financing needed?
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Cornerstones of Financial Accounting
Accounting
ISBN:9781337690881
Author:Jay Rich, Jeff Jones
Publisher:Cengage Learning
PFIN (with PFIN Online, 1 term (6 months) Printed...
Finance
ISBN:9781337117005
Author:Randall Billingsley, Lawrence J. Gitman, Michael D. Joehnk
Publisher:Cengage Learning
Fundamentals of Financial Management (MindTap Cou...
Finance
ISBN:9781285867977
Author:Eugene F. Brigham, Joel F. Houston
Publisher:Cengage Learning
Fundamentals Of Financial Management, Concise Edi...
Finance
ISBN:9781337902571
Author:Eugene F. Brigham, Joel F. Houston
Publisher:Cengage Learning
Related Questions
- Estimating Share Value Using the DCF Model Following are forecasts of sales, net operating profit after tax (NOPAT), and net operating assets (NOA) as of December 31, 2018, for Humana. Note: Complete the entire question in Excel and format each answer to two decimal places. Then enter the answers into the provided spaces below with two decimal places. Reported Forecast Horizon Period Terminal $ millions Sales NOPAT NOA 2021 2022 2020 2018 2019 $57,472 $58,326 $59,192 $60,072 $60,964 3,179 3,218 3,140 3,052 3,102 4,844 4,657 4,718 4,781 4,592 Period $61,568 3,244 4,887 Answer the following requirements with the following assumptions: Assumptions Terminal period growth rate 1% Discount rate (WACC) 7.8% Common shares outstanding 135.60 million Net nonoperating obligations (NNO) $(5,569) million Noncontrolling interest $0 million NNO is negative because Humana's nonoperating assets exceed its nonoperating liabilities. (a) Estimate the value of a share of Humana's common stock using the…arrow_forwardFrom the balance sheet prepare a proforma income statement where revenues can increase by 2% and the firm can borrow at 5.5% BALANCE SHEET 2021 Cash and cash equivalents 280 Receivables 2588 Inventory 2516 Other CA 189 TOTAL CA 5573 Fixed assets 5024 TOTAL ASSETS 10597 Accounts payable 4713 Short term debt 78 TOTAL CL 4790 LT debt 921 Shareh. Equity 4886 TOTAL LIAB. AND SHARH. EQUITY 10597 INCOME STATEMENT 2021 Sales 19418 COGS 13136 Depreciations 354 SG&A 4952 EBIT 976 Interest Expenses 52 Tax 268 Net income 656 Pro-forma statement Pro Forma Forecasts Actual Projected Projected Projected Projected Projected 2021 2022 2023 2024 2025 2026 COGS/REVENUES SGA/SALES INVENTORIES/COGS OTHER CA/SALES AR/SALES AP/COGS SALES/FIXED ASSETS DEPR/ FIXED ASSETS EQUITY/INVESTED CAPITAL ST DEBT/INVESTED…arrow_forwardPlease help me to analyze this Financialsarrow_forward
- Here are the abbreviated financial statements for Planner’s Peanuts: INCOME STATEMENT, 2019 Sales $ 3,500 Cost 2,700 Net income $ 800 BALANCE SHEET, YEAR-END 2018 2019 2018 2019 Assets $ 4,500 $ 5,000 Debt $ 833 $ 1,000 Equity 3,667 4,000 Total $ 4,500 $ 5,000 Total $ 4,500 $ 5,000 Assume the payout ratio is 50%. a. Calculate the internal growth rate where no external debt or equity is to be issued. (Do not round intermediate calculations. Enter your answer as a whole percent.) b. Calculate the sustainable growth rate where the firm maintains a fixed debt ratio but issues no equity. (Do not round intermediate calculations. Enter your answer as a whole percent.)arrow_forwardplease calculate the following: 2017 2018 2019 2020 Quick ratio Debt ratio Interest coverage ratio Dividend yield 4- current assets- 1,170.7m 4- inventory- 859.9 m 4- current liabilities- 885.8 m 5- total debts- 1,598.8 5- total assets- 2,452.3 6- operating profit- 259.2 6- interest expense/ finance cost- 10.7m 7- annual dividends per share- 118.0 cents (46. cents per share) 7- current share price- $24.71 2018 4- current assets-1,210.5 m 4- inventory- 891.1 m 4- current liabilities- 917.2 m 5- total debts- 1.544.1m 5- total assets 2,491.7 m 6- operating profit- 334.5 m 6- interest expense/ finance cost- 16.6 m 7- annual dividends per share- 132 (46. cents per share) 7- current share price- $21.48 2019 4- current assets- 1,276.5 m 4- inventory- 886.7 m 4- current liabilities- 927.1 mill 5- total debts/ total liabilities- 1,504.7 m 5- total assets- 927.1 mill 6- operating profit/ profit before tax- 359.3 mil 6- interest expense/ finance cost-…arrow_forwardLiquidity management Bauman Company's total current assets, total current liabilities, and inventory for each of the past 4 years follow Item 2019 2020 2021 Total current assets $17,440 $21,630 $22,920 Total current liabilities 12,890 9,090 5.270 Inventory 6,560 Hem a. Calculate the firm's current and quick ratios for each year. Compare the resulting time series for these measures of liquidity b. Comment on the firm's liquidity over the 2019-2022 period c. If you were told that Bauman Company's inventory turnover for each year in the 2019-2022 penod and the industry averages were as follows, would this information support or conflict with your evaluation in part (b)? Why? 2019 2020 12,630 7,230 a. Calculate the firm's current and quick ratios for each year. 2022 $26,480 16,920 7,510 2021 2022 LLLarrow_forward
- Financearrow_forwardComputing trend analysis and return on common equity Net sales revenue, net income, and common stockholders’ equity for Eyesight Mission Corporation, a manufacturer of contact lenses, follow for a four-year period. Requirements Compute trend analyses for each item for 2017-2019. Use 2016 as the base year, and round to the nearest whole percent. Compute the rate of return on common stockholders’ equity for 2017-2019, rounding to three decimal places.arrow_forwardEstimating Share Value Using the ROPI Model Following are forecasts of sales, net operating profit after tax (NOPAT), and net operating assets (NOA) as of December 31, 2018, for Humana. Note: Complete the entire question in Excel and format each answer to two decimal places. Then enter the answers into the provided spaces below with two decimal places. Reported Horizon period Terminal millions Sales NOPAT NOA 2018 2019 2020 2021 2022 $56,912 $57,766 $58,632 $59,512 $60,404 2,492 2,542 2,580 2,619 2,658 4,097 4,158 4,221 4,284 4,032 Answer the following requirements with the following assumptions: Assumptions Terminal period growth rate 1% Discount rate (WACC) 7.8% Common shares outstanding 135.6 million Net nonoperating obligations (NNO) $(6,129) million ($ millions) ROPI (NOPAT- [NOABeg x rw]) Present value of horizon ROPI Cum present value of horizon ROPI $ Present value of terminal ROPI NOA Total firm value Less NNO Firm equity value Shares outstanding (millions) Stock price per…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Cornerstones of Financial AccountingAccountingISBN:9781337690881Author:Jay Rich, Jeff JonesPublisher:Cengage Learning
- PFIN (with PFIN Online, 1 term (6 months) Printed...FinanceISBN:9781337117005Author:Randall Billingsley, Lawrence J. Gitman, Michael D. JoehnkPublisher:Cengage LearningFundamentals of Financial Management (MindTap Cou...FinanceISBN:9781285867977Author:Eugene F. Brigham, Joel F. HoustonPublisher:Cengage LearningFundamentals Of Financial Management, Concise Edi...FinanceISBN:9781337902571Author:Eugene F. Brigham, Joel F. HoustonPublisher:Cengage Learning
Cornerstones of Financial Accounting
Accounting
ISBN:9781337690881
Author:Jay Rich, Jeff Jones
Publisher:Cengage Learning
PFIN (with PFIN Online, 1 term (6 months) Printed...
Finance
ISBN:9781337117005
Author:Randall Billingsley, Lawrence J. Gitman, Michael D. Joehnk
Publisher:Cengage Learning
Fundamentals of Financial Management (MindTap Cou...
Finance
ISBN:9781285867977
Author:Eugene F. Brigham, Joel F. Houston
Publisher:Cengage Learning
Fundamentals Of Financial Management, Concise Edi...
Finance
ISBN:9781337902571
Author:Eugene F. Brigham, Joel F. Houston
Publisher:Cengage Learning