Description
Finish the assignment questions and DCF valuation (according to my classmate’s answer to wirt a new one)Make sure you are good at cooporate finanece, including enterprise value, dcf analysisFinish the assignment questions and DCF valuationplease finish all questions in attached pdf, give specific calculation process and excel workplease show me very specific calculation process in word document. for lst question, you should show me both specifc calculation process and specific and accurate procress in Excel
Unformatted Attachment Preview
Individual Assignment 2 – Due October 24
This assignment is to be completed and turned in individually.
1. You would like to estimate a per-share value of ABC, Inc. Your estimate of the present
value of ABC’s future free cash flows from operations is $800 million. The book value of
its assets is $850 million. The value of debt and lease obligations are $220 million and
$85 million, respectively. ABC Inc. also has cash holdings (non-operating assets) of
$95 million. There are 5 million shares outstanding.
What is the value per share?
Please use the following information to answer questions 2 and 3: Your firm has
outstanding debt equal to $138 million, cash holdings equal to $47 million, and 19
million shares outstanding.
2. Your firm is currently privately held, and you would like to use information from
publicly traded comparables to estimate a price for your stock ($ per share). Your
firm’s expected 2023 EBITDA is $74 million. The industry median ratio of enterprise
value to EBITDA is currently 6.1. What is the market-implied enterprise value of
your firm?
3. What is the market-implied equity price per share?
4. You have completed a DCF valuation of ElectricCar’s operations and estimate an
enterprise value of $915 million. There are currently 11 million shares outstanding.
ElectricCar Inc. has $80 million in debt and $19 million in cash. There are also
1,000,000 warrants outstanding with an average strike price of $15 per share.
Please estimate the equity value per share of ElectricCar Inc. (assuming exercise of
the warrants).
Please use the following information to answer questions (5) through (7).
BoatShare Inc. has just received approval to operate in coastal cities across
Connecticut. In order to begin operations, it will need to purchase 150 new boats at
$37,000 each (i.e., total cost of $5,550,000). To finance this purchase, it will borrow all
$5,500,000 for one year from the bank. There is a 95 percent chance that BoatShare
will generate sufficient cash flow to repay the loan in full with interest at the end of the
year; otherwise, BoatShare will go bankrupt. In the event of bankruptcy, the fleet of
used boats can be sold for $2,200,000; however, the legal and administrative costs to
the bank of seizing and selling them is $400,000.
5. What interest rate will the bank charge for the loan if its expected return on loans to
companies that are similar to BoatShare is 9 percent?
6. Before calculating anything, how is the interest rate charged on the loan that you
calculated in Question 5 affected by the potential bankruptcy costs? That is, do you
expect the interest rate to be higher, lower, or the same as it would be absent these
costs?
7. Please calculate the interest rate if there are no administrative costs associated with
bankruptcy.
8. DCF Exercise: (Valuation Exercise) See spreadsheet: OrganicFood_DCF.xls
Conagra Foods (CAG) is thinking about acquiring OrganicFood Co. and would like to
determine an offer price. Conagra Foods has no plans to change the capital structure
or operations of OrganicFood. OrganicFood’s income statement and balance sheet
data are provided in “OrganicFood.xls”. For your calculations, you may assume that
the appropriate WACC is 10%
a) Calculate the historical free cashflows of OrganicFood, from the 12 month
periods ending September 2019 through September 2023.
b) Forecast free cash flows to the firm from September 2024 through September
2026. You may assume that the income statement and balance sheet
components of free cash flow (relative to sales) remain fixed at their 2023
values.
c) Calculate a terminal value assuming that, after 2026, sales and all components
of free cash flow grow at 2% per year (i.e., the projected 2026 sales growth
rate) in perpetuity.
d) Provide a DCF valuation of the company as of September 2023. That is,
discount the cash flows in (b) and (c).
e) What is the equity value of Organic Food?
Question 1
PV(FCF)
Debt
Lease Obligation
Cash
Equity value
# shares outstanding
Value per share
$
$
$
$
$
800 million
220 million
85 million
95 million
590 million
5 million
118
$
Question 2
Debt
Cash
# shares outstanding
Expected 2023 EBITDA
Industry Avg EV/EBITDA
Market-implied EV
Question 3
Market-implied Equity value
Market-implied Equity per share
Question 4
EV
Debt
Cash
# warrants
Strike price
Cash from exercise of warrants
Equity value
$
$
138 million
47 million
19 million
$
$
74 million
6.1
451.40 million
$
$
360.40 million
18.97
$
$
$
915 million
80 million
19 million
1 million
15
15 million
869 million
$
$
$
Initial # shares outstanding
# warrants
Total shares outstanding (adj)
Equity value per share
11 million
1 million
12 million
$
Question 5
Loan amount
Interest rate charged
Salvage value of used boats
Legal & adminstrative costs
Target expected return (9%)
Bank’s profit
72.42
5,500,000
13.01% (using Goal Seek)
2,200,000
400,000
495,000
Probability
715,789
95%
(3,700,000)
5%
Expected return from BoatShare
495,000
Question 6
The potential bankruptcy costs (especially the direct costs like legal and administrative) redcude the amount the bank e
In order to compensate for this higher risk of loss, the bank will charge a higher interest rate on the loan.
Therefore, I expect the interest rate to be lower than it would be absent these costs.
Question 7
Interest rate charged
12.63% (using Goal Seek)
Bank’s profit
Probability
694,737
(3,300,000)
95%
5%
Expected return from BoatShare
495,000
dministrative) redcude the amount the bank expects to recover in the event of a bankruptcy.
(a)
Free Cash Flow (FCF) Calculation
For the 12 month Period Ending
Currency
EBIT
Tax rate
Taxes
EBIAT
+Depreciation
-Capital Expenditures
-Change in NWC
FCF
Sept-30-2019
USD
Sept-30-2020
USD
Sept-30-2021
USD
Sept-30-2022
USD
1,313.9
32.4%
425.42
888.44
361.48
506.07
22.43
721.42
1,406.4
33.1%
466.12
940.28
386.78
464.14
(14.42)
877.35
1,509.0
34.4%
519.71
989.27
409.99
491.98
332.57
574.70
1,616.6
33.4%
540.17
1,076.39
434.59
538.89
98.02
874.07
Sept-30-2024
Sept-30-2025
Sept-30-2026
(b)
For the 12 month Period Ending
Currency
USD
USD
EBIT
Tax rate
Taxes
EBIAT
+Depreciation
-Capital Expenditures
-Change in NWC
FCF
1,583.5
33.5%
530.21
1,053.34
478.79
478.79
1,053.34
1,621.8
33.5%
543.01
1,078.78
490.35
490.35
1,078.78
(c)
Growth rate after 2026
WACC
Terminal Value
2%
10%
14,098.25
USD
1,662.3
33.5%
556.59
1,105.75
502.61
502.61 (Assume PP&E remain at 2023 leve
(Assume Account receivables, inve
1,105.75
(d)
Sept-30-2024
Sept-30-2025
Sept-30-2026
USD
USD
USD
FCF
Terminal value
Total CF
1,053.34
1,078.78
1,053.34
1,078.78
1,105.75
14,098.25
15,204.00
PV of Future CF @ Sep 2023
13,272.12
(e)
As of September 2023
Total firm value
Total debt
Cash
Equity value
14,284.2
2,049.9
153.5
12,387.8
For the 12 month Period Ending
Currency
Net Working Capital (NWC) Table
Sept-30-2023
USD
1,526.5
33.5%
511.10
1,015.37
461.53
553.84
(89.00)
1,012.06
A/R
Inventory
A/P
NWC
Change in NWC
Sept-30-2018
Sept-30-2019
266.0
676.2
334.0
608.20
266.0
676.2
311.6
630.63
22.43
(Assume PP&E remain at 2023 level)
(Assume Account receivables, inventory, and account payables remain at 2023 level)
Sept-30-2020
238.9
701.2
323.9
616.21
(14.42)
Sept-30-2021
Sept-30-2022
344.4
948.7
344.4
948.78
332.57
347.5
1,066.3
367.0
1,046.80
98.02
Sept-30-2023
313.7
1,025.1
381.0
957.80
(89.00)
OrganicFood Co. Income Statement
For the Fiscal Period Ending
ACTUAL
12 months Sept30-2018
USD
12 months
Sept-30-2019
USD
12 months
Sept-30-2020
USD
12 months
Sept-30-2021
USD
12 months
Sept-30-2022
USD
Revenue
Other Revenue
Total Revenue
4,384.3
4,384.3
5,372.8
5,372.8
5,630.0
5,630.0
6,446.8
6,446.8
6,880.7
6,880.7
Cost Of Goods Sold
Gross Profit
2,506.5
1,877.8
2,814.7
2,558.1
2,973.1
2,656.9
3,637.4
2,809.4
3,858.6
3,022.1
Selling General & Admin Exp.
R & D Exp.
Depreciation
Other Operating Expense/(Income)
673.6
307.3
0.2
878.2
361.5
4.5
863.1
386.8
0.6
892.7
410.0
(2.3)
973.9
434.6
(3.0)
Operating Expense Total
981.0
1,244.2
1,250.5
1,300.4
1,405.5
Operating Income
896.7
1,313.9
1,406.4
1,509.0
1,616.6
Interest (Expense)
(62.5)
(65.2)
(67.1)
(79.8)
(93.4)
Interest and Investment Income
Net Interest Exp.
7.0
(55.5)
2.8
(62.4)
0
(67.1)
0
(79.8)
0
(93.4)
Tax Rate %
Income Tax Expense
Net Income
32.9%
276.3
564.9
32.4%
405.2
846.2
33.1%
443.9
895.4
34.4%
492.2
937.0
33.4%
509.0
1,014.2
Currency
EXAMPLE IS BASED ON A U.S. PUBLICLY TRADED COMPANY (Data are from Capital IQ, modified for the assignment)
Assumptions
COGS / Sales
SG&A / Sales
Depreciation / Sales
54.5%
15.1%
7.1%
EBIT / Sales
23.3%
Revenue Forecast
12 months
Sept-30-2023
USD
12 months
Sept-30-2024E
USD
12 months
Sept-30-2025E
USD
12 months
Sept-30-2026E
USD
6,545.7
6,545.7
6,790.5
6,954.5
7,128.3
6,790.5
6,954.5
7,128.3
3,570.2
2,975.5
3,703.7
3,086.8
3,793.1
3,161.3
3,888.0
3,240.3
478.8
490.4
502.6
1,583.5
1,621.8
1,662.3
33.5%
33.5%
33.5%
988.8
461.5
(1.3)
1,449.0
1,526.5
(79.4)
0
(79.4)
33.5%
484.5
962.6
Organic Food Co. Balance Sheet
Balance Sheet as of:
Sep-30-2018
USD
Sep-30-2019
USD
Sep-30-2020
USD
Sept-30-2021
USD
Sep-30-2022
USD
456.7
456.7
283.6
283.6
319.8
24.0
343.9
229.7
229.7
256.4
256.4
Accounts Receivable
Total Receivables
266.0
266.0
238.9
238.9
344.4
344.4
347.5
347.5
313.7
313.7
Inventory
Total Current Assets
676.2
1,398.9
701.2
1,223.6
948.7
1,637.0
1,066.3
1,643.5
1,025.1
1,595.2
5,736.65
5,736.6
5,881.24
5,881.2
5,958.59
5,958.6
6,040.59
6,040.6
6,144.89
6,144.9
7,135.5
7,104.9
7,595.6
7,684.1
7,740.1
LIABILITIES
Accounts Payable
Curr. Port. of LT Debt
Other Current Liabilities
Total Current Liabilities
334.0
40.0
102.3
476.3
311.6
70.0
100.8
482.4
323.9
80.0
122.9
526.7
344.4
100.0
146.8
591.2
367.0
50.0
134.1
551.1
Long-Term Debt
Other Non-Current Liabilities
Total Liabilities
1,526.0
30.6
2,032.9
1,130.0
35.6
1,647.9
1,635.4
42.8
2,204.9
1,659.5
105.3
2,356.0
1,807.8
101.1
2,460.0
Common Stock
Additional Paid In Capital
Retained Earnings
Treasury Stock
Total Common Equity
130.2
4,547.9
424.5
5,102.6
135.7
4,575.1
746.1
5,456.9
127.1
4,396.6
866.9
5,390.7
105.7
4,261.2
961.2
5,328.1
79.5
4,125.1
1,075.5
5,280.1
Total Equity
5,102.6
5,456.9
5,390.7
5,328.1
5,280.1
Total Liabilities And Equity
7,135.5
7,104.9
7,595.6
7,684.1
7,740.1
Currency
ASSETS
Cash And Equivalents
Short Term Investments
Total Cash & ST Investments
Property, Plant & Equipment (Net)
Total Long Term Assets
Total Assets
EXAMPLE IS BASED ON A U.S. PUBLICLY TRADED COMPANY (Data are from Capital IQ, modified for the assignment)
FORECAST
Sep-30-2023
USD
Sep-30-2024
USD
Sep-30-2025
USD
Sep-30-2026
USD
309.4
309.4
309.4
309.4
309.4
1,058.2
1,539.1
1,058.2
1,058.2
1,058.2
6,237.20
6,237.2
6,237.2
6,237.2
6,237.2
381.0
381.0
381.0
153.5
18.0
171.5
7,776.3
381.0
50.0
137.6
568.6
1,999.9
109.3
2,677.8
41.7
3,965.8
1,091.0
5,098.5
5,098.5
7,776.3
OrganicFood Co. Income Statement
For the Fiscal Period Ending
ACTUAL
12 months
Sept-30-2018
USD
12 months
Sept-30-2019
USD
12 months
Sept-30-2020
USD
12 months
Sept-30-2021
USD
12 months
Sept-30-2022
USD
12 months
Sept-30-2023
USD
Revenue
Other Revenue
Total Revenue
4,384.3
4,384.3
5,372.8
5,372.8
5,630.0
5,630.0
6,446.8
6,446.8
6,880.7
6,880.7
6,545.7
6,545.7
Cost Of Goods Sold
Gross Profit
2,506.5
1,877.8
2,814.7
2,558.1
2,973.1
2,656.9
3,637.4
2,809.4
3,858.6
3,022.1
3,570.2
2,975.5
Selling General & Admin Exp.
R & D Exp.
Depreciation
Other Operating Expense/(Income)
673.6
307.3
0.2
878.2
361.5
4.5
863.1
386.8
0.6
892.7
410.0
(2.3)
973.9
434.6
(3.0)
988.8
461.5
(1.3)
Operating Expense Total
981.0
1,244.2
1,250.5
1,300.4
1,405.5
1,449.0
Operating Income
896.7
1,313.9
1,406.4
1,509.0
1,616.6
1,526.5
Interest (Expense)
(62.5)
(65.2)
(67.1)
(79.8)
(93.4)
(79.4)
Interest and Investment Income
Net Interest Exp.
7.0
(55.5)
2.8
(62.4)
0
(67.1)
0
(79.8)
0
(93.4)
0
(79.4)
Tax Rate %
Income Tax Expense
Net Income
32.9%
276.3
564.9
32.4%
405.2
846.2
33.1%
443.9
895.4
34.4%
492.2
937.0
33.4%
509.0
1,014.2
33.5%
484.5
962.6
Currency
EXAMPLE IS BASED ON A U.S. PUBLICLY TRADED COMPANY (Data are from Capital IQ, modified for the assignment)
Revenue Forecast
12 months
12 months
12 months
Sept-30-2024E Sept-30-2025E Sept-30-2026E
USD
USD
USD
6,790.5
6,954.5
7,128.3
6,790.5
6,954.5
7,128.3
Organic Food Co. Balance Sheet
Balance Sheet as of:
Sep-30-2018
USD
Sep-30-2019
USD
Sep-30-2020
USD
Sept-30-2021
USD
Sep-30-2022
USD
Sep-30-2023
USD
456.7
456.7
283.6
283.6
319.8
24.0
343.9
229.7
229.7
256.4
256.4
153.5
18.0
171.5
Accounts Receivable
Total Receivables
266.0
266.0
238.9
238.9
344.4
344.4
347.5
347.5
313.7
313.7
309.4
309.4
Inventory
Total Current Assets
676.2
1,398.9
701.2
1,223.6
948.7
1,637.0
1,066.3
1,643.5
1,025.1
1,595.2
1,058.2
1,539.1
5,736.65
5,736.6
5,881.24
5,881.2
5,958.59
5,958.6
6,040.59
6,040.6
6,144.89
6,144.9
6,237.20
6,237.2
7,135.5
7,104.9
7,595.6
7,684.1
7,740.1
7,776.3
LIABILITIES
Accounts Payable
Curr. Port. of LT Debt
Other Current Liabilities
Total Current Liabilities
334.0
40.0
102.3
476.3
311.6
70.0
100.8
482.4
323.9
80.0
122.9
526.7
344.4
100.0
146.8
591.2
367.0
50.0
134.1
551.1
381.0
50.0
137.6
568.6
Long-Term Debt
Other Non-Current Liabilities
Total Liabilities
1,526.0
30.6
2,032.9
1,130.0
35.6
1,647.9
1,635.4
42.8
2,204.9
1,659.5
105.3
2,356.0
1,807.8
101.1
2,460.0
1,999.9
109.3
2,677.8
Common Stock
Additional Paid In Capital
Retained Earnings
Treasury Stock
Total Common Equity
130.2
4,547.9
424.5
5,102.6
135.7
4,575.1
746.1
5,456.9
127.1
4,396.6
866.9
5,390.7
105.7
4,261.2
961.2
5,328.1
79.5
4,125.1
1,075.5
5,280.1
41.7
3,965.8
1,091.0
5,098.5
Total Equity
5,102.6
5,456.9
5,390.7
5,328.1
5,280.1
5,098.5
Total Liabilities And Equity
7,135.5
7,104.9
7,595.6
7,684.1
7,740.1
7,776.3
Currency
ASSETS
Cash And Equivalents
Short Term Investments
Total Cash & ST Investments
Property, Plant & Equipment (Net)
Total Long Term Assets
Total Assets
EXAMPLE IS BASED ON A U.S. PUBLICLY TRADED COMPANY (Data are from Capital IQ, modified for the assignment)
FORECAST
Sep-30-2024
USD
Sep-30-2025
USD
Sep-30-2026
USD
Purchase answer to see full
attachment
