FIN601 Accounting and Financial Markets

I need help finishing the yellow boxes thought out the excel doc.

Consolidated Statements Of Income – USD ($) shares in Thousands, $ in Millions
REVENUE:
Net sales
Membership fees
Total revenue
OPERATING EXPENSES:
Merchandise costs
Selling, general and administrative
Preopening expenses
Operating Income
OTHER INCOME (EXPENSE):
Interest expense
Interest income and other, net
INCOME BEFORE INCOME TAXES
Provision for income taxes
Net income including noncontrolling interests
Net income attributable to noncontrolling interests
NET INCOME ATTRIBUTABLE TO COSTCO
NET INCOME PER COMMON SHARE ATTRIBUTABLE TO COSTCO:
Basic (in dollars per share)
Diluted (in dollars per share)
Shares used in calculation (000’s)
Basic (shares)
Diluted (shares)
CASH DIVIDEND DECLARED PER COMMON SHARE
2022
$
$
$
222.730
4.224
226.954
$
$
199.382
19.779
$
7.793
$
$
$
$
$
(158)
205
7.840
1.925
5.915
$
5.915
$
$
13
13
$
443.651
444.757
3,38
Consolidated Balance Sheets – USD ($) shares in
Thousands, $ in Millions
CURRENT ASSETS
Cash and cash equivalents
Short-term investments
Receivables, net
Merchandise inventories
Other current assets
Total current assets
PROPERTY AND EQUIPMENT
Land
Buildings and improvements
Equipment and fixtures
Construction in progress
Gross property and equipment
Less accumulated depreciation and amortization
2022
$
$
$
$
$
$
10.203
846
2.241
17.907
1.499
32.696
$
$
$
$
$
$
7.955
20.120
10.275
1.582
39.932
(15.286)
Net property and equipment
Operating lease, right-of-use assets
OTHER ASSETS
TOTAL ASSETS
$
$
$
$
24.646
2.774
4.050
64.166
CURRENT LIABILITIES
Accounts payable
Accrued salaries and benefits
Accrued member rewards
Deferred membership fees
Current portion of long-term debt
Other current liabilities
Total current liabilities
LONG-TERM DEBT, excluding current portion
Long-term operating lease liabilities
OTHER LIABILITIES
Total liabilities
COMMITMENTS AND CONTINGENCIES
$
$
$
$
$
$
$
$
$
$
$
17.848
4.381
1.911
2.174
73
5.611
31.998
6.484
2.482
2.555
43.519
$
$
$
$
$
$
–
EQUITY
Preferred stock $.01 par value
Common stock $0.01 par value
Additional paid-in capital
Accumulated other comprehensive loss
Retained earnings
Total Costco stockholders’ equity
2
6.884
(1.829)
15.585
20.642
Noncontrolling interests
Total equity
TOTAL LIABILITIES AND EQUITY
$
$
$
5
20.647
64.166
Consolidated Statements Of Cash Flows $ in Millions
CASH FLOWS FROM OPERATING ACTIVITIES
Net income including noncontrolling interests
Adjustments to reconcile net income including noncontrolling interests to net cash provided by
operating activities:
Depreciation and amortization
Non-cash lease expense
Stock-based compensation
Other non-cash operating activities, net
Deferred income taxes
Changes in operating assets and liabilities:
Merchandise inventories
Accounts payable
Other operating assets and liabilities, net
Net cash provided by operating activities
CASH FLOWS FROM INVESTING ACTIVITIES
Purchases of short-term investments
Maturities and sales of short-term investments
Additions to property and equipment
Acquisitions
Other investing activities, net
Net cash used in investing activities
CASH FLOWS FROM FINANCING ACTIVITIES
Change in bank checks outstanding
Repayments of short-term borrowings
Proceeds from short-term borrowings
Proceeds from issuance of long-term debt
Repayments of long-term debt
Tax withholdings on stock-based awards
Repurchases of common stock
Cash dividend payments
Dividend to noncontrolling interest
Acquisition of noncontrolling interest
Other financing activities, net
Net cash (used in) provided by financing activities
EFFECT OF EXCHANGE RATE CHANGES ON CASH AND CASH EQUIVALENTS
Net change in cash and cash equivalents
CASH AND CASH EQUIVALENTS BEGINNING OF YEAR
CASH AND CASH EQUIVALENTS END OF YEAR
SUPPLEMENTAL DISCLOSURE OF CASH FLOW INFORMATION:
Cash paid during the year for::
Interest
Income taxes, net
$
2.022
$
5.915
$
$
$
$
$
1.900
377
724
76
(37)
$
$
$
$
(4.003)
1.891
549
7.392
$
$
$
$
$
$
(1.121)
1.145
(3.891)
(48)
(3.915)
$
$
$
$
$
$
$
$
$
$
$
$
(800)
(363)
(439)
(1.498)
(208)
(842)
(133)
(4.283)
$
$
$
$
(249)
(1.055)
11.258
10.203
$
$
145
1.940
27-Sep-22
28-Sep-21
29-Sep-20
3-Oct-19
18-Oct-18
Market price per share of common stock
(=Adjusted Close price per share)
$478,30
$444,46
$341,82
$288,37
$225,82
Shares outstanding of common stock
442.604.145 441.823.811 441.228.027 439.656.950 438.208.376
10-Oct-17
4-Oct-16
$153,28
$139,66
436.989.606 437.126.569
Do not change the format of cells
Bond Symbol Coupon rate Amount outstanding (in $)
COST4495547
COST4495548
COST4977642
COST4977643
COST4977644
Total
BV (Book value) (in $)
% of Total BV
Last sale price
MV (Market value) (in $)
% of Total MV
YTM (of last sale)
YTM based on BV
YTM based on MV
Date
9/1/2019
10/1/2019
11/1/2019
12/1/2019
1/1/2020
2/1/2020
3/1/2020
4/1/2020
5/1/2020
6/1/2020
7/1/2020
8/1/2020
9/1/2020
10/1/2020
11/1/2020
12/1/2020
1/1/2021
2/1/2021
3/1/2021
4/1/2021
5/1/2021
6/1/2021
7/1/2021
8/1/2021
9/1/2021
10/1/2021
11/1/2021
12/1/2021
1/1/2022
2/1/2022
3/1/2022
4/1/2022
5/1/2022
6/1/2022
7/1/2022
8/1/2022
9/1/2022
1MTB (%)
1,99
1,73
1,58
1,55
1,53
1,58
0,37
0,11
0,10
0,13
0,11
0,08
0,09
0,09
0,09
0,08
0,08
0,04
0,02
0,02
0,01
0,03
0,05
0,04
0,05
0,06
0,07
0,04
0,05
0,04
0,18
0,31
0,58
1,06
1,85
2,28
2,61
Average
Variance
Standard Deviation
Covariance
Beta with risk premium
Required rate of return
risk free rate of return
0,025
1MTB_r
COST_aP
274,35
282,92
286,11
280,49
291,56
268,29
272,67
289,76
295,67
290,63
312,02
333,95
341,00
343,51
377,04
362,61
348,06
326,90
348,80
368,21
375,12
392,38
426,15
452,55
446,45
488,36
536,76
564,95
502,68
516,73
573,92
529,94
465,42
478,45
540,37
522,10
472,27
COST_r
COST_r_Pr
market risk premium
0,03
SP500_aP
2.976,74
3.037,56
3.140,98
3.230,78
3.225,52
2.954,22
2.584,59
2.912,43
3.044,31
3.100,29
3.271,12
3.500,31
3.363,00
3.269,96
3.621,63
3.756,07
3.714,24
3.811,15
3.972,89
4.181,17
4.204,11
4.297,50
4.395,26
4.522,68
4.307,54
4.605,38
4.567,00
4.766,18
4.515,55
4.373,94
4.530,41
4.131,93
4.132,15
3.785,38
4.130,29
3.955,00
3.585,62
SP500_r
SP500_r_Pr
Do not change the format of cells
Regression Results
the format of cells
Questions (Do not change the format of cells)
Q1 How many shares are outstanding (as of September 27, 2022) ?
Q2 What is the adjusted close price (in $) of COSTCO stock (as of September 27, 2022)? (in $)
Q3 How much is the total market value (in $) of equity issued by COSTCO? (in $)
Q4 What is the required rate of return on the COSTCO stock ?
Q5 How much is the total market value (in $) of bonds (or debts) issued by COSTCO? (in $)
Q6 What is the weighted average cost of debt based on the market value for COSTCO?
Q7 What is the total market value (in $) of total capital raised by COSTCO? (in $)
Q8 What is the weight of capital raised by issuing equity ?
Q9 What is the weight of capital raised by issuing bonds ?
Q10 What is the WACC (after tax) for COSTCO? (Note: Tax rate is “0.24554 “)
Answers
Do not change the format of cells
Item
Amount (in US$)
Cash flow from operation
$7.392.000.000,00
Net income
$5.915.000.000,00
EBIT
$7.793.000.000,00
Dep. & Amort.
$1.900.000.000,00
Other non-cash adj.
$1.140.000.000,00
Chang in WC
$1.563.000.000,00
Capital expenditure
$3.891.000.000,00
Net borrowings
($800.000.000,00)
Interest paid
($47.000.000,00)
Tax rate
Tax-affected interest paid
0,24554
($35.459.821,43)
FCF
FCFE
FCFF
Note: Amount is in US$, not “in Millions.”
Do not change the format of cells
Formula
= Cash flow from operation
= Net earnings (a.k.a Net Income Attributable to Costco)
= Income before income taxes + Interest paid
(If the information of “intrest paid” is missing, use the interest expense.)
(If there is “operating income”, use the operating income.)
= Depreciation and Amortization
= Sum of all adjustments to reconcile to net cash except Depreciation and Amortization
= –(Chg. Receivables, net + Chg. Inventories – Chg. Account payable – Chg. Accrued liabilities – Chg. Accrued income taxes)
(If there are more items than those in the above equation, use all the items listed under “Changes in assets and liabilities)
= –(Payments for property and equipment + Proceeds from the disposal of property and equipment)
(If there is “Other investing activities, net”, we assume that “Onther investing activities, net” is the proceeds from the disposal of property
and equipment)
= Proceeds from issuance of long-term debt – Repayment of long-term debt + Proceeds from inssuance of short-term debt – Payment of short-term debt
= Interest paid
(Costco Wholesale Corp. has “interest expense” and “interest income and other, net.” Therefore, we add these two items, resulting in total
interest paid.)
= Provision of income taxes/Income before income taxes
= Interest paid*(1 – tax rate)
= Cash flow from operations – Capital expenditures
= Cash flow from operations – Capital expenditures + Net borrowings
= Net Income + Depreciation + Amortization + Other non-cash charges – Increase in working capital – Capital expenditures + Net
borrowings
*Increase in working capital: Please use the change in WC. The cell (B8) shows the value in the brackets. So, it means the WC has actually
decreased. To input the value in the equation, “(Increase in working capital) = (-669,000.000.00)”. And, please don’t forget ” – ” in front of
“Increase in working capital”.
*Depreciation & Amortization: Most companies show these information in the Cash flow statement. And, some companies provide the
accounting item as “Depreciation and Amortization” while other companies report these information as “Depreciation” and “Amortization”
separately. If a company reports these information separately, we add together as you see in the formula (Depreciation + Amortization).
Otherwise, we use what a company reports (as “Depreciation and Amortization).
= Cash flow from operation + (Interest*(1 – tax rate)) – Capital expenditure
= [EBIT*(1 – tax rate)] + Non-cash charges – Capital expenditure – Increase in working capital
*Non-cash charges: This includes “Dep. & Amort.” and “Other non-cash adj.”
*Increase in working capital: Please use the change in WC. The cell (B8) shows the value in the brackets. So, it means the WC has actually
decreased. To input the value in the equation, “(Increase in working capital) = (-669,000.000.00)”. And, please don’t forget ” – ” in front of
“Increase in working capital”.
= Net income + [Interest*(1 – tax rate)] + Non-cash charges – Capital expenditure – Increase in working capital
*Non-cash charges: This includes “Dep. & Amort.” and “Other non-cash adj.”
*Increase in working capital: Please use the change in WC. The cell (B8) shows the value in the brackets. So, it means the WC has actually
decreased. To input the value in the equation, “(Increase in working capital) = (-669,000.000.00)”. And, please don’t forget ” – ” in front of
“Increase in working capital”.
= FCFE + [interest*(1 – tax rate)] – Net borrowings
Note: Amount is in US$, not “in Millions.”
Sources
Cash flow statement
Income statement
Income statement
Cash flow statement
Cash flow statement
Cash flow statement
Cash flow statement
Cash flow statement
Income statement
Income statement
Income statement
Do not change the format of cells
Table 1 (in US$):
a. stock price on September 27, 2022
b. shares outstanding on September 27, 2022
c. market capitalization
d. beta
e. risk free rate
f. market premium
g. cost of equity
h. total MV debts
i. cost of debt (based on Market Value)
j. Total capial
k. w_equity
l. w_debt
m. tax rate
n. cost of capital (WACC)
o. Avg. of GeoMean growth rate over past 4 years
p. Avg. of GeoMean growth rate over past 7 years
0,025000
0,030000
0,031000
0,015000
Table 2 (in US$):
Year
2022
2023*
2024*
2025*
FCFE
FCFF
*: Year with “*” implies that the FCFE and FCFF is estimated. FCFE and FCFF for 2018 is from the actual financial reports.
Note: Everything is in US$, not in Millions
Table 3 (in US$):
Year
2022
2023*
2024*
2025*
FCFE
FCFF
*: Year with “*” implies that the FCFE and FCFF is estimated. FCFE and FCFF for 2018 is from the actual financial reports.
Note: Everything is in US$, not in Millions
2026*
2027*
2028*
2026*
2027*
Terminal Value
cial reports.
cial reports.
2029*
2030*
2031*
… ∞
… ∞
… ∞
Do not change the format of cells
Table 1 (in US$):
a.
stock price on September 27, 2022
b.
shares outstanding on September 27, 2022
c.
market capitalization
d.
beta
e.
risk free rate
0,025000
0,030000
f.
market premium
g.
cost of equity
h.
total MV debts
i.
cost of debt (based on Market Value)
j.
Total capial
k.
w_equity
l.
w_debt
m.
tax rate
n.
cost of capital (WACC)
o.
Avg. of GeoMean growth rate over past 4 years
0,031000
p.
Avg. of GeoMean growth rate over past 7 years
0,015000
Table 2 (in US$):
Year
Future FCFE
PV in 2023 (at the beginnng)
Future FCFF
2023
2024
2025
2026
2027
Terminal Value
Total PV in 2023
(at the beginnng)
For equity investors
$
Note: Everything is in US$, not in Millions.
–
For capital investors
PV in 2023 (at the beginnng)
$
–
Do not change the format of cells
Table 1 (US$):
a.
stock price on September 27, 2022
b.
shares outstanding on September 27, 2022
c.
market capitalization
d.
beta
e.
risk free rate
0,025000
0,030000
f.
market premium
g.
cost of equity
h.
total MV debts
i.
cost of debt (based on Market Value)
j.
Total capial
k.
w_equity
l.
w_debt
m.
tax rate
n.
cost of capital (WACC)
o.
Avg. of GeoMean growth rate over past 4 years
0,031000
p.
Avg. of GeoMean growth rate over past 7 years
0,015000
Table 2 (in US$):
Year
Future FCFE
PV in 2023 (at the beginnng)
Future FCFF
2023
Total PV in 2023
(at the beginnng)
For equity investors
$
–
For capital investors
Note: Everything is in US$, not in Millions
Table 3 (in US$):
Year
Future FCFE
PV in 2023 (at the beginnng)
Future FCFF
2023
Total PV in 2023
(at the beginnng)
For equity investors
Note: Everything is in US$, not in Millions
$
–
For capital investors
PV in 2023 (at the beginnng)
$
–
PV in 2023 (at the beginnng)
$
–
FIN 601: Financial Management and Markets
Instructor: Dr. Jinsuk Yang
Project Report Part 2
 Instruction for Project Part 2 (PP2) Report: Total of 60 points




The Final Report is due by 11:00 pm CST, December 10, 2023.
You must submit “Project Part 2” through Blackboard to earn credits.
Any late submission will not be accepted.
Use your own words. If you obtain some information from other websites, interpret those with
your own words! Any plagiarism will not be tolerated (you will receive zero credits for your
project and, as a result, automatically fail the class. Read the following article
http://www.usi.edu/media/5599778/academic-integrity-policies-and-procedures-fall-2016.pdf )
 Download all the necessary files from Blackboard
 To complete the Project Part 2, please download the following files
 PP2-CoD_FA23.pdf Project Part 2 Resources”>
 PP2-Qs_FA23.xlsx Project Part 2 Resources”>
 There are 11 spreadsheets in PP2-Qs_FA23.xlsx.
 Income Stmt, Balance Sheet, Cash Flow Stmt, and Supplement:
 They contain the given information used to complete Project Part 2.
 Other spreadsheets:
 You must fill up yellow cells in several tables.
 Cost of Debts Spreadsheet (10 points)
 We already studied and learned how to compute the weighted average cost of debt from
Module 4 (Module_4_CoD_Example).
 It is your responsibility to watch and follow lecture videos in all the modules. If you do not
know how to complete the “Cost of debt” part of PP2, please watch again the lecture
videos from Module 4. The videos in Module 4 use an example and explain how to fill up
boxes in great detail. Please remember that the company used in Module 4 lectures is
different from the company used in PP2.
 Please read the comments in cells.
 IMPORTANT!
o Using the Excel file (file name: PP2-Qs_FA23) posted on Blackboard, you can find the
cost of debts spreadsheet. The data related to Costco’s debts is provided; therefore,
you should use the data posted (file name: PP2-CoD_FA23). For the “weighted
average cost of debt” in WACC, you use “YTM based on MV”, the last cell.
o Please read the comments in cells.
o Make sure that you are correctly converting “$ in millions” into “$”
 Cost of Equity Spreadsheet (15 points)
 We already studied and learned how to compute the cost of equity from Module 4
(Module_4_CoE_Example). All the equations or Excel functions to find average,
variance, standard deviation, covariance, beta, and the required rate of returns are
provided in “Module_4_CoE_Example_Instruction”. The lecture videos also demonstrate
1|Page
FIN 601: Financial Management and Markets
Instructor: Dr. Jinsuk Yang
Project Report Part 2
how to use the Excel “Regression” function and to compute “Required return” by using
the CAPM model.
 It is your responsibility to watch and follow lecture videos in all the modules. If you do not
know how to complete the “Cost of Equity” part of PP2, please watch again the lecture
videos from Module 4. The videos include the example and explain how to fill up boxes in
great detail. Please remember that the company used in Module 4 lectures is different
from the company used in PP2.
 Use the following information to compute the “Required rate of return”:
Risk-free rate of return: 0.025 (2.5%) & Market risk premium: 0.03 (3%)
CAPM: Required return = Risk free rate of return + (beta)*(Market risk premium)
 IMPORTANT!
o Please express all values in cells as raw numbers with 6 decimal points, not in %.
(The cell format is already set up as “6 decimal points” except “Variance” which has
8 decimal points. Please don’t change the format.)
o Please read the comments in cells.
 WACC Spreadsheet (5 points)
 We already studied and learned how to compute the “WACC” from Module 4
(Module_4_WACC_Example). It is your responsibility to watch and follow lecture videos
in all the modules. If you do not know how to complete the “WACC” part of PP2, please
watch again the lecture videos from Module 4. The videos include the example and
explain how to fill up boxes in great detail. Please remember that the company used in
Module 4 lectures is different from the company used in PP2.
 Answers to some questions can be found in other spreadsheets in the same Excel
workbook.
1. Find the capital raised by issuing equity: Q3
a. In the workbook (PP2-Qs_FA23), the data related to the market price per share and
the shares outstanding are provided (please look at the “Supplement” spreadsheet).
b. Compute the market capitalization (a.k.a. “Total market value of equity”)
i. The market capitalization is nothing but the product of “shares outstanding”
and “market price per share.”
ii. The amount of market capitalization is the capital raised by issuing equity.
2. Compute the required rate of return on the stock: Q4
a. The required rate of return on the stock is what we call the “Cost of Equity”
b. We will use the CAPM to compute it. Use the following information:
Risk-free rate of return: 0.025 & Market risk premium: 0.03
CAPM: Required return = Risk free rate of return + (beta)*(Market risk premium)
c. You already have this information in your “Cost of Equity” spreadsheet.
2|Page
FIN 601: Financial Management and Markets
Instructor: Dr. Jinsuk Yang
Project Report Part 2
3. Find the capital (in the market value) raised by issuing bonds: Q5
a. This is also called the “Total market value of bonds.”
b. You already have this information in your “Cost of Debt” spreadsheet.
4. Compute the cost of debt: Q6
a. It is already computed in your spreadsheet “Cost of Debt.”
b. This is also known as the “Weighted average cost of debt.”
It is because, in the “Cost of debt” spreadsheet, we use the “% of Total MV” and
multiply this value by “YTM (of the last sale)” when computing each bond type and
we sum up all the “YTM based on MV” when computing the cost of debt.
5. Compute the total market value of total capital: Q7
a. This is nothing but the sum of answers to Q3 and Q5.
6. Find the weight (Read Chapter 13: Section 13.8): Q8 and Q9
a. To compute WACC, you must find out the weight of each capital raised by the
company.
b. For the weight of capital raised by issuing equity:
𝑾𝒆𝒊𝒈𝒉𝒕𝑬
=
𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑒𝑞𝑢𝑖𝑡𝑦
𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑡𝑜𝑡𝑎𝑙 𝑐𝑎𝑝𝑖𝑡𝑎𝑙
=
𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑒𝑞𝑢𝑖𝑡𝑦
𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑒𝑞𝑢𝑖𝑡𝑦 + 𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑏𝑜𝑛𝑑𝑠
c. For the weight of capital raised by issuing bonds:
𝑾𝒆𝒊𝒈𝒉𝒕𝑩
=
𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑏𝑜𝑛𝑑𝑠
𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑡𝑜𝑡𝑎𝑙 𝑐𝑎𝑝𝑖𝑡𝑎𝑙
=
𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑏𝑜𝑛𝑑𝑠
𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑒𝑞𝑢𝑖𝑡𝑦 + 𝑇𝑜𝑡𝑎𝑙 𝑚𝑎𝑟𝑘𝑒𝑡 𝑣𝑎𝑙𝑢𝑒 𝑜𝑓 𝑏𝑜𝑛𝑑𝑠
7. Find the WACC: Q10
a. To compute WACC after the corporation tax, please use the following formula:
𝑊𝐴𝐶𝐶 = (𝑊𝑒𝑖𝑔ℎ𝑡𝐸 × 𝐶𝑜𝐸) + (𝑊𝑒𝑖𝑔ℎ𝑡𝐵 × 𝐶𝑜𝐷 × (1 − 𝑇𝑎𝑥 𝑟𝑎𝑡𝑒))
, where “Cost of debts (CoD)” is the weighted average cost of debts based on the
market value and “cost of equity (CoE)” is the required rate of return on the stock
computed by using CAPM.
To be accurate, if a company issues “Preferred stock”, we must include an additional
cost of equity (only related to the preferred stock) in the equation.
𝑊𝐴𝐶𝐶 = (𝑊𝑒𝑖𝑔ℎ𝑡𝐸 × 𝐶𝑜𝐸) + (𝑊𝑒𝑖𝑔ℎ𝑡𝑃 × 𝐶𝑜𝑃)
+ (𝑊𝑒𝑖𝑔ℎ𝑡𝐵 × 𝐶𝑜𝐷 × (1 − 𝑇𝑎𝑥 𝑟𝑎𝑡𝑒))
3|Page
FIN 601: Financial Management and Markets
Instructor: Dr. Jinsuk Yang
Project Report Part 2
, where “CoP” stands for the cost of preferred stock, implying the “required rate of
return on the preferred stock” and 𝑊𝑒𝑖𝑔ℎ𝑡𝑃 is the weight of capital raised by issuing
the preferred stock. However, we assume that Costco Company doesn’t issue the
preferred stock. Please read the textbook for the WACC (including the preferred
stock).
b. We use “0.24554” as the tax rate. And, it is from the “FCFE & FCFF” spreadsheet.
c. To understand how to find this rate, please look at the formula in the “Formula”
column.
 FCFE & FCFF Spreadsheet
Use the formula given in the spreadsheet (Formula) and fill in the yellow cells in the Table.
 Tips:
 FCFE: There are two values you must find. And, these values should be the same. If you
have different values, you might want to read the formula and watch the lecture videos
again. And, please double-check your computation.
 FCFF: There are four values you must find. And, these values should be the same. If you
have different values, you might want to read the formula and watch the lecture videos
again. And, please double-check your computation.
 Forecast FCFEs & FCFFs Spreadsheet (10 points)
We assume that your company will grow at 0.031 (3.1%) over the next 5 years (from 2022 to
2023, from 2023 to 2024, from 2024 to 2025, from 2025 to 2026, from 2026 to 2027).
Then, the company will grow forever at 0.015 (1.5%) from 2028 (2027 to 2028, 2028 to 2029,
2029 to 2030, and forever…).

Table 1:
Fill the empty boxes. And, please read a comment posted in each cell.
 Table 2:
 From 2023 to 2027, we will use the growth rate of 0.031 for the FCFE and FCFF.
 For example:
FCFE (or FCFF) in the year 2022 will grow by 0.031 to the year 2023. So, the FCFE
(or FCFF) in the year 2023 can be computed by multiplying FCFE (or FCFF) in the
year 2022 to “1 + growth rate (0.031)”.
FCFE (or FCFF) in the year 2023 will grow by 0.031 to the year 2024. So, the FCFE
(or FCFF) in the year 2024 can be computed by multiplying FCFE (or FCFF) in the
year 2023 to “1 + growth rate (0.031)”.
 From 2028, we will use the growth rate of 0.015 for the FCFE and FCFF.
 For example:
FCFE (or FCFF) in the year 2027 will grow by 0.015 to the year 2028. So, the FCFE
(or FCFF) in the year 2028 can be computed by multiplying FCFE (or FCFF) in the
year 2027 to “1 + growth rate (0.015)”.

4|Page
FIN 601: Financial Management and Markets
Instructor: Dr. Jinsuk Yang
Project Report Part 2
FCFE (or FCFF) in the year 2028 will grow by 0.015 to the year 2029. So, the FCFE
(or FCFF) in the year 2029 can be computed by multiplying FCFE (or FCFF) in the
year 2028 to “1 + growth rate (0.015)”.
 Why do we use two different growth rates?
 It will be great if a company can maintain the growth rate (Average geometric
mean growth rate over the past 4 years). However, companies have competitors
and lose their customers (resulting in less profits) over the long run. So, the growth
rate becomes lower.
 Read a comment posted in each cell.
 Table 3:
 Compute the “Terminal Value” by using the constant growth model. Please read
“Chapter 9 => Case 3 (Differential Growth)”. In Example 9.3, the price at the end of
Year 5 (capturing all the upcoming dividends that grow at g2) is what we consider as
“Terminal Value.”
 For the terminal value of FCFEs, the discount rate is “Cost of Equity”
𝑻𝒆𝒓𝒎𝒊𝒏𝒂𝒍 𝒗𝒂𝒍𝒖𝒆 𝒐𝒇 𝑭𝑪𝑭𝑬𝑠 𝑖𝑛 2028, 2029, 2030, … …
=
=
“Estimated FCFE in 2027” × (1 + 𝐺𝑟𝑜𝑤𝑡ℎ 𝑟𝑎𝑡𝑒)
𝐶𝑜𝑠𝑡 𝑜𝑓 𝑒𝑞𝑢𝑖𝑡𝑦 − 𝐺𝑟𝑜𝑤𝑡ℎ 𝑟𝑎𝑡𝑒
“𝑬𝒔𝒕𝒊𝒎𝒂𝒕𝒆𝒅 𝑭𝑪𝑭𝑬 𝒊𝒏 𝟐𝟎𝟐𝟖”
𝐶𝑜𝑠𝑡 𝑜𝑓 𝑒𝑞𝑢𝑖𝑡𝑦 − 𝐺𝑟𝑜𝑤𝑡ℎ 𝑟𝑎𝑡𝑒
* Since the FCFE in the year 2027 will grow by 0.015 to the year 2028,
please use 1.5% as the growth rate in the equation.

For the terminal value of FCFFs, the discount rate is “WACC”
𝑻𝒆𝒓𝒎𝒊𝒏𝒂𝒍 𝒗𝒂𝒍𝒖𝒆 𝒐𝒇 𝑭𝑪𝑭𝑭𝑠 𝑖𝑛 2028, 2029, 2030, … …
=
=
“Estimated FCFF in 2027” × (1 + 𝐺𝑟𝑜𝑤𝑡ℎ 𝑟𝑎𝑡𝑒)
𝑊𝐴𝐶𝐶 − 𝐺𝑟𝑜𝑤𝑡ℎ 𝑟𝑎𝑡𝑒
“𝑬𝒔𝒕𝒊𝒎𝒂𝒕𝒆𝒅 𝑭𝑪𝑭𝑭 𝒊𝒏 𝟐𝟎𝟐𝟖”
𝑊𝐴𝐶𝐶 − 𝐺𝑟𝑜𝑤𝑡ℎ 𝑟𝑎𝑡𝑒
* Since the FCFF in the year 2027 will grow by 0.015 to the year 2028,
please use 1.5% as the growth rate in the equation.
 Read a comment posted in each cell.
 Discounted Free Cash Flows Spreadsheet (10 points)
 From the previous step, you must have the future FCFEs and FCFFs and the terminal value.
 Now, let’s compute the present value of those future cash flows.
5|Page
FIN 601: Financial Management and Markets
Instructor: Dr. Jinsuk Yang
Project Report Part 2

We computed the “Cost of debt”, “Cost of equity”, and “WACC” based on the information
in 2022. However, for simplicity, we are going to assume that we are estimating the value
of the business at the beginning of 2023. And, each future FCFE and FCFF comes at the
end of each year.
 Table 2:
 Use the Cost of Equity (it is in “Table 1”) to compute the PV of each FCFE
 Use the WACC (it is in “Table 1”) to compute the PV of each FCFF
 After computing each PV of each FCFE, FCFF, and Terminal value, find the total PV in
2023 (at the beginning of the Year 2023).
 Read a comment posted in each cell.
 Capitalized Free Cash Flows Spreadsheet (10 points)
 Table 2:
 We assume that future FCFEs and FCFFs will be constant.
 So, we use the perpetuity equation to compute the PV.
 Read Chapter 4
 Use the Cost of Equity (it is in “Table 1”) to compute the PV of FCFE
 Use the WACC (it is in “Table 1”) to compute the PV of FCFF
 Read a comment posted in each cell.

Table 3:
 We assume that the future FCFEs and FCFFs will grow at 0.015 (1.5%) forever.
 So, we use the constant growth model to compute the PV.
 Read Chapter 4
 The discount rate for the FCFE
 Use the Cost of Equity to compute the PV of FCFE
 The discount rate for the FCFF
 Use the WACC to compute the PV of FCFE
 Read a comment posted in each cell.
6|Page
FIN 601: Financial Management and Markets
Instructor: Dr. Jinsuk Yang
Project Report Part 2
What you have done is the simplified approach to value the business. To do so, you have applied or
used:
 Financial statements
 Free cash flow
 Free cash flow to equity
 Free cash flow to the firm
 The time value of money
 Discounting multiple periods of future cash flows
 Discounting with a constant growth model
 Discounting with the perpetuity
 Cost of equity
 Cost of debts
 WACC
The last two spreadsheets provide information, which is the total present value in the Year 2023 (at
the beginning). This is what we call the implied value of a business. However, the business valuation
must be done by using a very sophisticated approach with more steps than what we did and by
licensed professionals.

Requirements
o Excel file
1. In your Excel file, you must have 11 spreadsheets.
2. Name your file as “PP2-first name-last name”
3. You must save the file as “Save as type: Excel Workbook”, not “CSV” or any other
format.

FAQ
o Do I visit the FINRA website and collect data?
No, you use the data provided by the instructor.
7|Page
Financial Management and Markets
FIN 601
Company: Costco Wholesale Corporation
Ticker: COST
The following tables provide information regarding the bonds issued by Costco Wholesale Corporation. All information is
from http://www.finra.org/ .
To learn how to collect the data, please read the “Cost of Debt” instruction from Blackboard.
The coupon rate is expressed in %.
For example, “2.750” is “2.750%”
This is quoted price, implying that it is percentage of par (or face) value.
For example, take a look at the first bond (Symbol: COST4495547).
Costco Wholesale Corporation issued many of this bond, and the price
of each is 96.954% of the par value of each.
The Yield (Yield to maturity) is
expressed in %. For example, “4.779”
is “4.779%”