AFF 420 Havard University Valuation Analysis of The Company Alta Gas Ltd and General Economic Condition Paper 1. Select a company from the list 2. Complet

AFF 420 Havard University Valuation Analysis of The Company Alta Gas Ltd and General Economic Condition Paper 1. Select a company from the list

2. Complete the Assignment 3 Excel (Please find the attachment than can log in the video website. PLease watch the video before it statrs, because our professor requires that his format must be used). (After watching the video, you can open the file cadded Assignment Excel to do it, you may need to change some of the details)

3 Finish Assignment, need to do a DFC Model (Open Excel assignment 4 and complete it, noting that you may need to modify some details).

4. Writing twelve pages reports to complete assignment 4 (For specific requirements please refer to the Assignment 4 file, which requires some Excel data and picture to be interspersed with the report).

Note: All data must come from Yahoo Finance, you can google “Yahoo Finance”

This assignment need Minimum 60% of the grade guarantee Website: https://courses.ryerson.ca/d2l/le/content/371537/viewContent/2945550/View
Account: yulu.wang
Password: 980716Wangyulu@
When you login, you will see this screen. Then just click to play the video
Introduction
In Class Sample DCF Model
AFF 420
Dr. Allen Goss
June 1st, 2020
Version:
1
Scenario:
Sheet Name
Link
Introduction
Cover
Index
Styles
Q&A
Cover
Index
Styles
Q&A
2. Input
Asumptions
Assumptions
Income Statement
IS
Balance Sheet
BS
Cash Flow Statement
3. Model
Dicounted Cash Flow
DCF
4. Outputs
Ratios
Dashboard
Ratios
Sheet Contents
Description of model, author, dates etc.
List of Workseets in the model
Description of data styles use in model
To keep track of questions or issues arising from the model. Includes version dates.
Historical Income Statement 2014- 2019.
Historical Balance Seet 2014- 2019
DCF following Damodaran Sales to Capital approach
Contains sensitivity and Scenario test results
Cell Style
Formula: 0 Decimal
Formula: 1 Decimal
Formula: 2 Decimal
Formula: 3 Decimal
Formula: 0 Percent
Formula: 1 Percent
Formula: 2 Percent
Formula: 3 Percent
Positive
100
3.2
3.22
3.220
25%
25.0%
25.00%
25.000%
Negative
(100)
(3.2)
(3.22)
(3.220)
(25%)
(25.0%)
(25.00%)
(25.000%)
Off-sheet link: 0 Decimal
Off-sheet link: 1 Decimal
Off-sheet link: 2 Decimal
Off-sheet link: 3 Decimal
Off-sheet link: 0 Percent
Off-sheet link: 1 Percent
Off-sheet link: 2 Percent
Off-sheet link: 3 Percent
100
3.2
3.22
3.220
25%
25.0%
25.00%
25.000%
(100)
(3.2)
(3.22)
(3.220)
(25%)
(25.0%)
(25.00%)
(25.000%)
Zero
–
Inconsistent formula: 0 Decimal
Inconsistent formula: 1 Decimal
Inconsistent formula: 2 Decimal
Inconsistent formula: 3 Decimal
Inconsistent formula: 0 Percent
Inconsistent formula: 1 Percent
Inconsistent formula: 2 Percent
Inconsistent formula: 3 Percent
Positive
100
3.2
3.22
3.220
25%
25.0%
25.00%
25.000%
Negative
(100)
(3.2)
(3.22)
(3.220)
(25%)
(25.0%)
(25.00%)
(25.000%)
Zero
–
Hardcode: 0 Decimal
Hardcode: 1 Decimal
Hardcode: 2 Decimal
Hardcode: 3 Decimal
Hardcode: 0 Percent
Hardcode: 1 Percent
Hardcode: 2 Percent
Hardcode: 3 Percent
100
3.2
3.22
3.220
25%
25.0%
25.00%
25.000%

(100)
(3.2)
(3.22)
(3.220)
(25%)
(25.0%)
(25.00%)
(25.000%)
–
Date
################
Comment
Version 1.0 released to AFF 420 students
Issue: allow for separate convergence of WACC, EBIT margin and revenue growth
Issue: Add cost of capital schedule
Issue: Add lease and R&D capitalization schedules
Inputs
Assumptions
2018A
2019A
Base
Revenues
EBIT
Interest expense
Book value of equity
Book value of debt
Cash and Marketable Securities
Number of shares outstanding
Net Operating Loss Carry Forwards
Current stock price
Effective tax rate
Marginal tax rate
WACC
Terminal WACC
Terminal Growth Rate
Revenue growth rate
Target pre-tax operating margin
Year of convergence
Sales to capital ratio
Optimistic
Pessimistic
Income Statement
For the Year Ending Dec. 31st
2016A
2017A
2018A
(,000) USD
2015A
Sales
Cost of Goods Sold
Gross Profit
Selling, General and Admin
Operating Income Before Deprec. (EBITDA)
Depreciation
Operating Profit (EBIT)
Interest Income
Interest Expense
Pretax Income (EBT)
Income Tax
Net Income
$185,500.0 $154,683.7 $132,500.0 $118,190.0
$170,289.0 $139,679.4 $119,912.5 $106,489.2
$15,211.0 $15,004.3 $12,587.5 $11,700.8
$6,063.9
$6,007.7
$6,305.7
$6,377.1
$9,147.1
$8,996.6
$6,281.8
$5,323.7
$2,195.0
$2,067.0
$2,235.1
$2,237.0
$6,952.1
$6,929.6
$4,046.7
$3,086.7
$4,754.8
$4,470.2
$6,356.9
$4,418.1
$2,197.2
$2,459.4
($2,310.2) ($1,331.4)
$725.0
$812.0
$1,472.2
$1,647.4
($2,310.2) ($1,331.4)
Dividends
Additions to Retained Earnings
Number of shares Outstanding (thousands)
Earnings per share
Share Price (close)
$1,472.2
500
$2.94
12.51
–
($2,310.2)
($1,331.4)
500
$3.29
15.32
500
($4.62)
7.42
500
($2.66)
3.87
$1,647.4
c. 31st
2019A
$106,394.8
$96,287.3
$10,107.5
$6,414.2
$3,693.3
$2,247.8
$1,445.5
$4,000.5
($2,555.0)
($2,555.0)
($2,555.0)
500
($5.11)
1.89
Balance Sheet
CHECK: OK
Assets
Required Operating Cash
Marketable Securities
Accounts Receivable
Inventory
Other Current Assets
Total Current Assets
Gross Plant, Property & Equipment
Accumulated Depreciation
Net Plant, Property & Equipment
Other Long Term Assets
Total Assets
2015
For the Year Ended Dec 31st
2016
2017
2018
$3,200.4
$3,310.1
$3,360.5
$3,449.9
$17,378.3
$13,979.3
$8,639.2
$5,049.7
$69,969.0
$57,466.1
$47,917.8
$33,028.4
$39,983.6
$34,962.0
$31,210.1
$24,798.9
$3,736.1
$2,483.1
$1,230.1
$1,230.1
$134,267.4 $112,200.5
$92,357.7
$67,557.0
$58,850.0
$61,439.6
$61,839.6
$62,139.6
$15,000.0
$17,067.0
$19,302.1
$21,539.1
$43,850.0
$44,372.6
$42,537.5
$40,600.5
$18,840.1
$18,840.1
$18,840.1
$18,840.1
$196,957.5 $175,413.3 $153,735.3 $126,997.6
Liabilities and Equity
Accounts Payable
Accrued Expenses & Current Other Liabilities
Revolver (Short Term Debt)
Total Current Liabilities
Long Term Debt
Deferred Taxes & Other Liabilities
Total Liabilities
Common Stock / Paid in Capital
Retained Earnings
Total Common Equity
Total Liabilities & Equity
$76,166.7
$61,795.2
$51,346.4
$24,241.7
$95,296.1
$76,337.2
$61,202.2
$38,829.2
$59,383.3
$56,350.0
$53,316.7
$50,283.3
$4,545.9
$3,346.5
$2,147.1
$2,147.1
$159,225.3 $136,033.7 $116,666.0
$91,259.6
$31,260.0
$31,260.0
$31,260.0
$31,260.0
$6,472.2
$8,119.6
$5,809.3
$4,478.0
$37,732.2
$39,379.6
$37,069.3
$35,738.0
$196,957.5 $175,413.3 $153,735.3 $126,997.6
Working Capital Schedule
Current Assets
Cash
Current Liabilities
Non Cash NWC
Change in Non Cash Working Capital
$134,267.4 $112,200.5
$20,578.71 $17,289.41
$95,296.1
$76,337.2
$18,392.6
$18,573.9
$181.31
$19,129.4
$14,542.0
–
$9,855.8
–
$14,587.5
–
$92,357.7
$11,999.65
$61,202.2
$19,155.8
$581.90
–
$67,557.0
$8,499.60
$38,829.2
$20,228.2
$1,072.36
c 31st
2019
$3,489.8
$5,009.9
$27,983.3
$21,104.1
$1,230.1
$58,817.1
$62,439.6
$23,787.0
$38,652.7
$18,840.1
$116,309.9
$13,190.0
$20,539.8
$33,729.9
$47,250.0
$2,147.1
$83,127.0
$31,260.0
$1,922.9
$33,182.9
$116,309.9
$58,817.1
$8,499.65
$33,729.9
$16,587.6
-$3,640.57
Cash Flow Statement
CHECK: OK
2015
For the Year Ending Dec. 31st
2016
2017
2018
Cash Flow from Operations
Net Income
Depreciation Expense
Decrease/(Increase) in Receivables
Decrease/(Increase in Inventory
Decrease/(Increase) in Other Current
Increase/(Decrease) in Payables
Increase/(Decrease) in Other Current
Increase/(Decrease) in Deferred Taxes
Total Cash Flows From Operations
$1,647.4
$2,067.0
$12,502.9
$5,021.7
$1,253.0
($4,587.4)
($1,199.4)
$16,705.1
($2,310.2)
$2,235.1
$9,548.3
$3,751.9
$1,253.0
($4,686.1)
($1,199.4)
$8,592.4
($1,331.4)
$2,237.0
$14,889.4
$6,411.3
$4,731.7
$26,938.0
Cash Flows From Investing
(Additions) to PPE
(Additions) to Other Long Term Assets
Total Cash Flow From Investing
($2,589.7)
($2,589.7)
($400.0)
($400.0)
($300.0)
($300.0)
Cash Flows From Financing
Issuance/(Repayment) to Revolver
Issuance/(Repayment) Long Term Debt
Issue/(Repurchase) of Equity
Dividends Paid to Shareholders
Total Cash Flow from Financing
($14,371.5) ($10,448.9) ($27,104.7)
($3,033.3) ($3,033.3) ($3,033.3)
($17,404.8) ($13,482.2) ($30,138.0)
Change in Cash and Marketable Securities
Check
Beginning Cash and Marketable Securities
Cash
$20,578.7
($3,289.3)
($5,289.8)
($3,500.0)
$17,289.4
$17,289.4
$11,999.7
$11,999.7
$8,499.6
$8,499.6
c. 31st
2019
($2,555.0)
$2,247.8
$5,045.1
$3,694.8
($1,397.5)
$7,035.2
($300.0)
($300.0)
($3,701.9)
($3,033.3)
($6,735.2)
$0.0
$8,499.7
$8,499.6
Model
Discounted Cash Flows
Base-1
Base
1
Sales
Sales Growth (%)
EBIT
EBIT Margin (%)
NOL
Tax Rate
EBIT (1-t)
Less Reinvestment
FCFF
Discount Rate
Cumulative Discount Factor
Reinvestment Schedule
Sales to Capital Ratio
Invested Capital
Return on Invested Capital
Terminal Value
Final FCFF
Terminal WACC
Terminal Growth
Terminal Value
Perpetuity
Terminal Value
Final FCFF
EV/EBITDA Exit Multiple
Terminal Value
2
Line 7
Line 9
Line 10
Line 13
0.00%
#DIV/0!
0.0%
#DIV/0!
0
0
0
#DIV/0!
0
0
0
#DIV/0!
3
Exit Multiple
4
Ten Year Cash Flow Projections
5
6
Firm Valuation
Present Value of FCFF
PV of Terminal Value
Total
Less Debt
+Cash
Shares Outstanding
Share Price Implied
Perpetuity
7
8
Exit Multiple
0
0
0
#DIV/0!
0
0
0
#DIV/0!
0
0
0
#DIV/0!
0
0
0
#DIV/0!
0
0
0
#DIV/0!
0
0
0
#DIV/0!
9
10
11
Common Size Balance Sheets
2015
Assets
Required Operating Cash
Marketable Securities
Accounts Receivable
Inventory
Other Current Assets
Total Current Assets
Gross Plant, Property & Equipment
Accumulated Depreciation
Net Plant, Property & Equipment
Other Long Term Assets
Total Assets
Liabilities and Equity
Accounts Payable
Accrued Expenses & Current Other Liabilities
Revolver (Short Term Debt)
Total Current Liabilities
Long Term Debt
Deferred Taxes & Other Liabilities
Total Liabilities
Common Stock / Paid in Capital
Retained Earnings
Total Common Equity
Total Liabilities & Equity
Common Size (Assets =100%)
2016
2017
2018
1.62%
8.82%
35.52%
20.30%
1.90%
68.17%
29.88%
7.62%
22.26%
9.57%
100.00%
1.89%
7.97%
32.76%
19.93%
1.42%
63.96%
35.03%
9.73%
25.30%
10.74%
100.00%
2.19%
5.62%
31.17%
20.30%
0.80%
60.08%
40.22%
12.56%
27.67%
12.25%
100.00%
2.72%
3.98%
26.01%
19.53%
0.97%
53.20%
48.93%
16.96%
31.97%
14.84%
100.00%
9.71%
8.29%
6.41%
11.49%
38.67%
48.38%
30.15%
2.31%
80.84%
15.87%
3.29%
19.16%
100.00%
35.23%
43.52%
32.12%
1.91%
77.55%
17.82%
4.63%
22.45%
100.00%
33.40%
39.81%
34.68%
1.40%
75.89%
20.33%
3.78%
24.11%
100.00%
19.09%
30.57%
39.59%
1.69%
71.86%
24.61%
3.53%
28.14%
100.00%
=100%)
2019
Common Year (2015= 100%)
2015
2016
2017
2018
3.00%
4.31%
24.06%
18.14%
1.06%
50.57%
53.68%
20.45%
33.23%
16.20%
100.00%
100.00%
100.00%
100.00%
100.00%
100.00%
100.00%
100.00%
100.00%
100.00%
100.00%
100.00%
11.34%
100.00% 76.02% 51.52% 76.26% 68.95%
100.00% 81.13% 67.41% 31.83% 26.97%
100.00% 80.11% 64.22% 40.75% 35.39%
100.00% 94.89% 89.78% 84.68% 79.57%
100.00% 73.62% 47.23% 47.23% 47.23%
100.00% 85.43% 73.27% 57.31% 52.21%
100.00% 100.00% 100.00% 100.00% 100.00%
100.00% 125.45% 89.76% 69.19% 29.71%
100.00% 104.37% 98.24% 94.71% 87.94%
100.00% 89.06% 78.06% 64.48% 59.05%
17.66%
29.00%
40.62%
1.85%
71.47%
26.88%
1.65%
28.53%
100.00%
103.43%
80.44%
82.13%
87.44%
66.46%
83.56%
104.40%
113.78%
101.19%
100.00%
89.06%
105.00%
49.71%
68.48%
78.06%
32.92%
68.79%
105.08%
128.68%
97.01%
100.00%
78.06%
107.80%
29.06%
47.20%
62.02%
32.92%
50.32%
105.59%
143.59%
92.59%
100.00%
64.48%
2019
109.04%
28.83%
39.99%
52.78%
32.92%
43.81%
106.10%
158.58%
88.15%
100.00%
59.05%
Common Size Income Statements
in millions
Sales
Cost of Goods Sold
Gross Profit
Selling, General and Admin
Operating Income Before Deprec. (EBITDA)
Depreciation
Operating Profit (EBIT)
Interest Income
Interest Expense
Pretax Income (EBT)
Income Tax
Net Income
Dividends
Additions to Retained Earnings
2015
100.00%
91.80%
8.20%
3.27%
4.93%
1.18%
3.75%
Common Size (Sales =100%)
2016
2017
2018
100.00%
90.30%
9.70%
3.88%
5.82%
1.34%
4.48%
2.56%
1.18%
0.39%
0.79%
1.06%
100.00%
90.10%
9.90%
5.40%
4.50%
1.89%
2.61%
100.00%
90.50%
9.50%
6.03%
3.47%
2.11%
1.36%
–
4.80%
(1.74%)
(1.74%)
3.74%
(1.13%)
(1.13%)
3.76%
(2.40%)
(2.40%)
–
(1.74%)
(1.13%)
(2.40%)
2.89%
1.59%
0.52%
1.06%
–
0.79%
100.00%
90.50%
9.50%
4.76%
4.74%
1.69%
3.05%
2019
2015
100.00%
100.00%
100.00%
100.00%
100.00%
100.00%
100.00%
Common Year (2015= 100%)
2016
2017
2018
83.39%
82.02%
98.64%
99.07%
98.35%
94.17%
99.68%
–
71.43%
70.42%
82.75%
103.99%
68.68%
101.83%
58.21%
63.71%
62.53%
76.92%
105.16%
58.20%
101.91%
44.40%
2019
57.36%
56.54%
66.45%
105.78%
40.38%
102.41%
20.79%
100.00%
100.00%
100.00%
100.00%
94.01% 133.69%
92.92%
84.14%
111.93% (105.14%) (60.59%) (116.28%)
112.00%
111.89% (156.92%) (90.43%) (173.55%)
100.00%
111.89% (156.92%) (90.43%) (173.55%)
Ratios
2015
Sales to Capital Ratio
Return on Capital
Reinvestment Rate
Growth
For the Year Ended Dec 31st
2016
2017
2018
2019
Output
AFF420
Principles of Finance III
Summer 2020
Assignment #4
The final project is an individual valuation of a public company. It builds on the work you have
completed in the first three assignments and allows you to apply the valuation techniques learned in class
to real world companies. The project deliverable is a fully documented valuation model, including a DCF
valuation, contingent valuation and comparables analysis along with a report on the strategic and
competitive positioning of your company. Each student can pick ONE company from a list of options
provided on the D2L course shell (Your companies are listed by the last 5 digits of your Ryerson ID). No
student has the same options as any other student, so this is truly an individual effort, but you have
enough choice to find a company that looks interesting. The final term project is due at 11:59 p.m. on July
29th. You must submit one electronic copy of your report and your spreadsheet model to Turnitin via
D2L. Late penalty is 20% per day. Detailed requirements are as follows:
I.
Spreadsheet Model (8%)
The financial model should follow the lines of the model you built in Assignment #2.
Prepare a full valuation spreadsheet model for the equity of your firm. Your valuation
should support the narrative you have a developed in section II-3 below. You should
do each of the three valuation approaches covered in class:
Intrinsic Valuation: Value the equity in your firm using a discounted cash flow
model. The choice of inputs is up to you. In a separate appendix, you will list all of
the key input decision and your justification. As part of the model, you must prepare
a bottom up beta calculation, but it is up to you whether you ultimately choose to
use it.
Relative Valuation: Prepare a list of “comparable” companies, using criteria that
you think are appropriate. Choose a multiple that you will use in comparing firms
across the group. Justify your choices. Evaluate your company against the
comparable firms using the multiple that you have chosen for your valuation.
Contingent Valuation: Use the Black Scholes model to estimate the value of equity
for your firm. (See Assignment #3.)
Your valuation is dependent on the inputs you choose. Since the product of this analysis
will depend critically on the assumptions made, the spreadsheet will be appropriately
documented and include interactive sensitivity analysis (scroll bars and interactive
charts to allow for manipulation of key inputs). Your model should be professional,
error free and suitable for presentation to senior management.
II.
Valuation Report (14%)
The valuation report should be 9 to 12 pages, suitable for presentation to senior
management, and should include:
1. A one page executive summary;
2. A summary of general economic conditions with a focus on the industry (or industries)
that your firm operates in. This part of your report will provide context for the valuation
that follows. (approx. 2-3 pages)
3. Develop a narrative for the company that places it within the economic and competitive
context described earlier. (For example, you might argue that COVID 19 has fatally
crippled Air Canada and the company is at risk of bankruptcy. Your valuation would
support that narrative. Of course, this is just an example and I have no opinion on the
fate of airlines.) Carefully describe the value drivers of your company and link your
insights to key ratios. You may want to look at industry or competitor ratios when
discussing the health of the firm. (approx. 4-5 pages)
4. Discuss the valuation output from your model along with sensitivity analysis. Prepare
a final valuation and justify your findings, tying your results to the narrative you
developed in step 3. (approximately 2-3 pages)
III.
Technical Appendix (8%)
Prepare a separate technical appendix that outlines all of the choices you made in
building your financial model. Discuss why you chose the model that you did, which
risk free rate you used, which risk premium, and adjustments to earnings etc. Discuss
all of the key inputs, how you chose them and the sensitivity of the model to your
inputs. (Basically, expand on the “Comment” section of the Input Rationale table
described below.) Describe why it is (or why it is not) appropriate to use the contingent
valuation approach for your company. Discuss any data challenges you faced and
assumptions you made to overcome them. (as many pages as it takes- I imagine 4-7
pages?)
To facilitate marking this term project, please complete the following table and attach
it as one of the appendices to your report. “Location” tells me where to find it in your
model. “Comment” describes the choices you made and how and why you calculated
them. why you made them. Be brief but complete.
Input Rationale
Input
Type of DCF used
Risk-free rate
Equity risk premium
Country risk premium
Beta
Market value of equity and debt
Period of high growth, if applicable
Cash Flow estimates
(note all adjustments)
Convert operating leases into debt,
Location
Comment
DDM, FCFF or FCFE
Which one? Calculation details?
Yes or No? How calculated
Which one? Calculation details.
Be thorough describing adjustments
Yes or No
if appropriate
Capitalize R&D expenses,
if appropriate
Net capital expenditures
Non-cash working capital
Growth factors
Tax Rate
Pre-tax cost of debt
Cost of Equity
WACC, if applicable
Value of equity
Yes or No?
Describe adjustments
Explain what growth factors you used
Key Outputs
Please fill in the following table and add it as a separate appendix. I ONLY want values here- not
any explanations.
Fiscal year-end
Industry
Currency in valuation
Risk-free rate used
Tax rate used at the start of DCF
Marginal tax rate of the country
Operating income in terminal year
After-tax operating income in terminal
year
FCFF in terminal year
Cost of capital at the start of DCF
Cost of capital in perpetuity
Reinvestment rate in perpetuity
Return on capital in perpetuity
Perpetual growth rate
Estimated value per share (DCF)
Estimated value per share (Relative)
Estimated value per share (Contingent)
Current market price
AFF420 Principles of Finance III
Spring/Summer 2020
Assignment #3
The third assignment has two separate components.
FIRST DELIVERABLE
The first component is an Excel workbook with uses three approaches to value puts and calls. The workbook
will have the following:
• a Black Scholes option price calculator (as built in class). The model should be capable of pricing
European puts and calls, either with or without dividends. Add spinners to the key option inputs
and create a dynamic chart of the call option value that changes as the user changes the inputs.
• a 20 step binomial option pricing model of a call option. Use the Cox, Ross and Rubenstein
approach for calculating the up and down probabilities. Verify that your binomial model converges
to the Black Scholes option model.
• Use a model of geometric Brownian motion to simulate call and put prices. You should use 100,000
simulations. (Using any more than that will slow down Excel too much!) Verify that your
simulation converges to the Black Scholes option pricing values. As with the previous two models,
I will build this entirely in class.
As usual, your Excel model should be professional, free of errors and easy to navigate.
No writeup is required for this portion of the assignment. This is a separate workbook from the contingent
valuation model that you will use in Part Two. My hope is that you will be able to use the model you built
when you go on co-op interviews or when you are looking for grad roles. Every university student learns
about the Black Scholes option pricing model in class, but very few of them can show a model that they
built with Monte Carlo simulations, interactive dynamic charts and binomial trees in an Excel spreadsheet
(without any VBA). You are doing this for yourselves and if you do a good job you will have a very
impressive piece of modeling to show for your efforts in this class.
SECOND DELIVERABLE
Use the Black Sholes option pricing model from the previous sec…
Purchase answer to see full
attachment