Notes
1) Have fun with the real data exercise.
This is a starting point. Feel free to make changes if you wish. Fix any residual errors that may exist. Use it as a ‘sandbox’.
It’ll take a few minutes to fill up the data. Then spend time playing around. Change assumptions. Check the impact.
Use historical data from internet sources.
Boeing
Costco For example use any of these from internet sources
Microsoft
2) ‘Proforma’ tab has proforma data for income and balance sheet. Three color coded zones identify
data, assumptions and conclusions. Use historical data to construct the proforma. Change assumptions as needed.
3) ‘FCFF and FCFE’, tab constructs the free cashflows several ways using the proforma from ‘Proforma’ tab.
4) ‘Value’ tab makes assumptions about discount rates and uses data from ‘Proforma’ and ‘FCFF and FCFE’ tabs to find value.
Takeaways (very sketchy description)
1) Payout ratio does not affect value
2) Cost structure and growth assumptions can radically affect value
3) Growth may not enhance value
4) Terminal value can be large
5) Develop intuition for FCFF FCFE and Value
6) Learn the standard process of valuation
This spreadsheet model
1) Sales Growth in a given year will need investment. 10% growth will raise future FCFF by 10%.
2) Growth needs to stop at some point to be harvested
3) Discounted benefits of growth may be negative, net of initial FCFF requirement.
Scenario Summary
Scenario Summary
Current Values: 1 2 3 4 5
Created by User on 6/1/2022
Modified by User on 6/1/2022 Created by User on 6/1/2022 Created by User on 6/1/2022 Created by User on 6/1/2022 Created by User on 6/1/2022
Changing Cells:
$C$3 0% 10% 10% 10% 0% 3%
Result Cells:
$H$31 92.5 101.7 101.7 101.7 92.5 95.3
$H$32 89.2 98.5 98.5 98.5 89.2 92.0
$H$33 64.3 75.6 75.6 75.6 64.3 67.7
$H$34
$H$35 -72.9 -146.9 -146.9 -146.9 -72.9 -95.1
$H$36 2.6 2.6 2.6 2.6 2.6 2.6
$H$37 -70.3 -144.3 -144.3 -144.3 -70.3 -92.5
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Scenario Summary 2
Scenario Summary
Current Values: 1 2 3 4 5 6
Created by User on 6/3/2022 Created by User on 6/3/2022 Created by User on 6/3/2022 Created by User on 6/3/2022 Created by User on 6/3/2022 Created by User on 6/3/2022
Changing Cells:
$C$3 0% 0% 10% 10% 10% 0% 3%
Result Cells:
$H$31 92.5 92.5 101.7 101.7 101.7 92.5 95.3
$H$32 89.2 89.2 98.5 98.5 98.5 89.2 92.0
$H$33 64.3 64.3 75.6 75.6 75.6 64.3 67.7
$H$34
$H$35 -72.9 -72.9 -146.9 -146.9 -146.9 -72.9 -95.1
$H$36 2.6 2.6 2.6 2.6 2.6 2.6 2.6
$H$37 -70.3 -70.3 -144.3 -144.3 -144.3 -70.3 -92.5
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Scenario Summary 3
Scenario Summary
Current Values: 1 2 3 4 5 6
Created by User on 6/3/2022 Created by User on 6/3/2022 Created by User on 6/3/2022 Created by User on 6/3/2022 Created by User on 6/3/2022 Created by User on 6/3/2022
Changing Cells:
$C$3 0% 0% 10% 10% 10% 0% 3%
Result Cells:
$B$10 150.0 150.0 150.0 150.0 150.0 150.0 150.0
$C$10 75.0 75.0 82.5 82.5 82.5 75.0 77.3
$D$10 82.5 82.5 90.8 90.8 90.8 82.5 85.0
$E$10 90.8 90.8 99.8 99.8 99.8 90.8 93.5
$F$10 99.8 99.8 109.8 109.8 109.8 99.8 102.8
$G$10 99.8 99.8 109.8 109.8 109.8 99.8 102.8
$H$10 102.8 102.8 113.1 113.1 113.1 102.8 105.9
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Proforma
Pro Forma Income Statement Historical Data
Year 0 1 2 3 4 5 6 Assumptions
Sales Growth 0% 10% 10% 10% 0% 3% Conclusions
Payout Ratio 40.0% 40.0% 40.0% 40.0% 40.0% 40.0%
Tax Rate 40.0% 40.0% 40.0% 40.0% 40.0% 34.0%
Int. Rate 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% Value $687
Sales 750.0 750.0 825.0 907.5 998.3 998.3 1028.2
CGS 200.0 450.0 495.0 544.5 599.0 599.0 616.9 60.0% % of Sales
SGA 150.0 75.0 82.5 90.8 99.8 99.8 102.8 10.0% % of Sales
Depreciation 100.0 100.0 110.0 121.0 133.1 133.1 137.1 13.3% % of Sales
EBIT 300.0 125.0 137.5 151.3 166.4 166.4 171.4
Interest Expense 25.0 6.5 7.2 7.9 8.7 6.0 8.9 10.0% % of LT Debt
Profit bef. Taxes 275.0 118.5 130.4 143.4 157.7 160.4 162.5
Taxes 110.0 47.4 52.1 57.4 63.1 64.2 55.2
Net Income 165.0 71.1 78.2 86.0 94.6 96.2 107.2
Dividend Payment 28.4 31.3 34.4 37.9 38.5 42.9
Pro Forma Balance Sheet
Year 0 1 1 1 1 1 1
Cash 90.0 90.0 99.0 108.9 119.8 119.8 123.4 12.0% % of Sales
Mktbl Securities 60.0 60.0 66.0 72.6 79.9 79.9 82.3 8.0% % of Sales
A/R 120.0 120.0 132.0 145.2 159.7 159.7 164.5 16.0% % of Sales
Inventory 100.0 100.0 110.0 121.0 133.1 133.1 137.1 13.3% % of Sales
Fixed Assets(net) 280.0 280.0 308.0 338.8 372.7 372.7 383.9 37.3% % of Sales net
Total 650.0 650.0 715.0 786.5 865.2 865.2 891.1
A/P 84.0 84.0 92.4 101.6 111.8 111.8 115.2 11.2% % of Sales
Oth Curr.Liabilities 50.0 50.0 55.0 60.5 66.6 66.6 68.5 6.7% % of Sales
LT Debt 250.0 65.0 71.5 78.7 86.5 59.9 89.1 10% % of Assets
Stockholder’s Equity 266.0 451.0 496.1 545.7 600.3 626.9 618.3
Total 650.0 650.0 715.0 786.5 865.2 865.2 891.1
FCFF 75.0 30.9 34.0 37.4 99.8 92.5
FCFE -113.9 33.1 36.5 40.0 69.6 89.2
Addn to Retained Income 165.0 42.7 46.9 51.6 56.8 57.7 64.3
New Equity Isuued 142.3 -1.8 -2.1 -2.1 -31.1 -72.9
LT Debt Issued -185.0 6.5 7.2 7.8 -26.6 2.6
New External Capital -42.7 4.7 5.1 5.7 -57.7 -70.3
FCFF and FCFE
This tab is tied to ‘Proforma’ worksheet tab
FCFF two methods
1.0 2.0 3.0 4.0 5.0 6.0 check9/16
EBIT after Tax 75.0 82.5 90.8 99.8 99.8 113.1
add back Depreciation 100.0 110.0 121.0 133.1 133.1 137.1
subtract increase in WC 0.0 23.6 26.0 28.6 0.0 9.4
subtract capital expenditure 100.0 138.0 151.8 167.0 133.1 148.3
75.0 30.9 34.0 37.4 99.8 92.5
Net Income 71.1 78.2 86.0 94.6 96.2 107.2
add back after tax interest 3.9 4.3 4.7 5.2 3.6 5.9
add back Depreciation 100.0 110.0 121.0 133.1 133.1 137.1
subtract increase in WC 0.0 23.6 26.0 28.6 0.0 9.4
subtract capital expenditure 100.0 138.0 151.8 167.0 133.1 148.3
75.0 30.9 34.0 37.4 99.8 92.5
FCFE three methods
Net Income 71.1 78.2 86.0 94.6 96.2 107.2
add back Depreciation 100.0 110.0 121.0 133.1 133.1 137.1
subtract increase in WC 0.0 23.6 26.0 28.6 0.0 9.4
subtract capital expenditure 100.0 138.0 151.8 167.0 133.1 148.3
add net borrowing -185.0 6.5 7.2 7.8 -26.6 2.6
-113.9 33.1 36.5 40.0 69.6 89.2
FCFF 75.0 30.9 34.0 37.4 99.8 92.5
subtract after tax interest 3.9 4.3 4.7 5.2 3.6 5.9
add net borrowing -185.0 6.5 7.2 7.8 -26.6 2.6
-113.9 33.1 36.5 40.0 69.6 89.2
Dividends 28.4 31.3 34.4 37.9 38.5 42.9
add equity repurchases -142.3 1.8 2.1 2.1 31.1 72.9
-113.9 33.1 36.5 40.0 69.6 115.8
Value
r0 14% Assumptions This tab is tied to ‘Proforma’ worksheet tab and the ‘FCFF and FCFE’ tab
rb 13.5%
Target debt to value ratio 0.25
WACC Calculation
Cost of Levered Equity, rs 14.11%
WACC 12.81%
Short Horizon 0 1 2 3 4 5 6
Growth Rates 0% 10% 10% 10% 0% 3% From ‘Proforma’ Worksheet
FCFF 75.0 30.9 34.0 37.4 99.8 92.5 From FCFF and FCFE worksheet
Short period Interest 6.5 7.2 7.9 8.7 6.0 From ‘Proforma’ Worksheet
Short period Interest Tax Shield 2.6 2.9 3.1 3.5 2.4
Terminal Values
Terminal Value at target debt ratio $943
Unlevered Terminal Value (UTV) 0 0 0 0 $841
Tax-Shields in Terminal Value 0 0 0 0 $102
Present Values
PV0 of short horizon FCFF @ r0 $186
PV0 of terminal FCFF or PV0 of UTV @ r0 $437
PV0 of short horizon tax-shields @ rb $10
PV0 of tax-shields in Terminal Value @ rb $54
Total Value of Levered Firm $687
CostCo
Period End Date 8/29/21
Period Length Annual
Stmt Source Annual
Stmt Source Date 8/29/21
Stmt Update Type
Income Statement
Total Revenue 168,088,000
Cost of Revenue 52,232,000
Gross Profit 115,856,000
Operating Expense 45,940,000
Operating Income 69,916,000
Net Non Operating Interest Income Expense -215,000
Other Income Expense 1,401,000
Pretax Income 71,102,000
Tax Provision 9,831,000
Net Income Common Stockholders 61,271,000
Diluted NI Available to Com Stockholders 61,271,000
Basic EPS 8.12
Diluted EPS 8.05
Basic Average Shares 7,547,000
Diluted Average Shares 7,608,000
Total Operating Income as Reported 69,916,000
Total Expenses 98,172,000
Net Income from Continuing & Discontinued Operation 61,271,000
Normalized Income 60,150,420
Interest Income 2,131,000
Interest Expense 2,346,000
Net Interest Income -215,000
EBIT 73,448,000
EBITDA –
Reconciled Cost of Revenue 52,232,000
Reconciled Depreciation 11,686,000
Net Income from Continuing Operation Net Minority Interest 61,271,000
Total Unusual Items Excluding Goodwill 1,303,000
Total Unusual Items 1,303,000
Normalized EBITDA 83,831,000
Tax Rate for Calcs 0
Tax Effect of Unusual Items 182,420
Balance Sheet
Total Assets 59,268,000 55,556,000 45,400,000
Current Assets 29,505,000 28,120,000 23,485,000
Cash, Cash Equivalents & Short Term Investments 12,175,000 13,305,000 9,444,000
Receivables 1,803,000 1,550,000 1,535,000
Inventory 14,215,000 12,242,000 11,395,000
Other Current Assets 1,312,000 1,023,000 1,111,000
Total non-current assets 29,763,000 27,436,000 21,915,000
Net PPE 26,382,000 24,595,000 20,890,000
Gross PPE 40,548,000 37,491,000 32,626,000
Accumulated Depreciation -14,166,000 -12,896,000 -11,736,000
Other Non Current Assets 3,381,000 2,841,000 1,025,000
Total Liabilities Net Minority Interest 41,190,000 36,851,000 29,816,000
Current Liabilities 29,441,000 24,844,000 23,237,000
Total Non Current Liabilities Net Minority Interest 11,749,000 12,007,000 6,579,000
Total Equity Gross Minority Interest 18,078,000 18,705,000 15,584,000
Stockholders’ Equity 17,564,000 18,284,000 15,243,000
Minority Interest 514,000 421,000 341,000
Total Capitalization 24,256,000 25,798,000 20,367,000
Common Stock Equity 17,564,000 18,284,000 15,243,000
Capital Lease Obligations 2,642,000 2,558,000 –
Net Tangible Assets 17,564,000 18,284,000 15,243,000
Working Capital 64,000 3,276,000 248,000
Invested Capital 25,055,000 25,893,000 22,066,000
Tangible Book Value 17,564,000 18,284,000 15,243,000
Total Debt 10,133,000 10,167,000 6,823,000
Net Debt – – –
Share Issued 441,825 441,255 439,625
Ordinary Shares Number 441,825 441,255 439,625
Cash flow statement
Operating Cash Flow 8,958,000
Cash Flow from Continuing Operating Activities 8,958,000
Net Income from Continuing Operations 5,079,000
Depreciation Amortization Depletion 1,781,000
Deferred Tax 59,000
Stock based compensation 665,000
Other non-cash items 371,000
Change in working capital 1,003,000
Investing Cash Flow -3,535,000
Cash Flow from Continuing Investing Activities -3,535,000
Financing Cash Flow -6,488,000
Cash Flow from Continuing Financing Activities -6,488,000
Net Issuance Payments of Debt 135,000
Net Common Stock Issuance -496,000
Cash Dividends Paid -5,748,000
Net Other Financing Charges -379,000
End Cash Position 11,258,000
Income Tax Paid Supplemental Data 1,527,000
Interest Paid Supplemental Data 149,000
Capital Expenditure -3,588,000
Issuance of Debt 229,000
Repayment of Debt -94,000
Repurchase of Capital Stock -496,000
Free Cash Flow 5,370,000
Analysis
Assumption change in red (other parameters remain constant)
baseline 1 2 3 4 5 6 7 8 Conclusion
Sales Growth 0% 0% 10% 10% 10% 10% 0% 3% 0.082 increase sales growth increases value
Payout Ratio 40% 0.277 0.277 0.4 0.277 0.277 0.277 0.277 0.277 does not affect
Tax Rate 40% 0.366 0.366 0.366 0.4 0.366 0.366 0.366 0.366 increase in tax rate decreases value
Int. Rate 10% 0.055 0.055 0.055 0.055 0.1 0.035 0.055 0.055 does not affect
FCFF 75 30.9 33.99 37.389 99.825 92.497845 578.0054 538.608 565.5384 593.81532 623.506086 1094.6100292549 -576.2798 -744.648 -856.3452 -984.79698 -1132.516527 1725.0517554596 208.634136 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 141.6064 58.2692 64.09612 70.505732 77.5563052 1381.262920356 208.634136 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 208.634136 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 208.634136 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 208.634136
FCFE -113.9 33.11 36.468 39.999 69.6263 89.217245 146.8379599851 595.4073127612 625.1776783993 656.4365623192 689.2583904352 1101.91762633 -793.2727104925 -435.7922030597 -501.1610335187 -576.3351885465 -662.7854668284 1736.5681702793 -153.9974545678 103.4576629851 113.8034292836 125.1837722119 137.7021494331 1390.4842070307 -217.7668005373 229.3045292836 252.2349822119 277.4584804331 305.2043284764 1390.4842070307 -153.9974545678 103.4576629851 113.8034292836 125.1837722119 137.7021494331 1390.4842070307 -153.9974545678 103.4576629851 113.8034292836 125.1837722119 137.7021494331 1390.4842070307 103.4576629851 113.8034292836 125.1837722119 137.7021494331 1390.4842070307 103.4576629851 113.8034292836 125.1837722119 137.7021494331 1390.4842070307 -153.9974545678 103.4576629851 113.8034292836 125.1837722119 137.7021494331 1390.4842070307 -153.9974545678
r0 14% 10% 10% 10% 10% 10% 10% 10% 10%
rb 14% 5% 5% 5% 5% 5% 5% 5% 7% increase in rb decreases value
Target debt to value ratio 0.25 0.211 0.211 0.211 0.211 0.211 0.211 0.4 0.211 increase in debt to value increases value
Cost of Levered Equity, rs 14.11% 11% 11% 11% 11% 11% 11% 12% 11%
WACC 12.81% 9% 9% 9% 9% 9% 9% 9% 9%
Growth Rates 0% 10% 10% 10% 0% 3% 0.05 0.05 0.05 0.05 0.05 0.03 0.15 0.15 0.15 0.15 0.15 0.03 0.1 0.1 0.1 0.1 0.1 0.03 0.1 0.1 0.1 0.1 0.1 0.03 0.1 0.1 0.1 0.1 0.1 0.03 0.1 0.1 0.1 0.1 0.1 0.03 0.1 0.1 0.1 0.1 0.1 0.03 0.1 0.1 0.1 0.1 0.1 0.03 0.1 0.1 0.1 0.1 0.1 0.03
FCFF 75 30.9 33.99 37.389 99.825 92.497845 512.96 538.608 565.5384 593.81532 623.506086 1094.6100292549 -647.52 -744.648 -856.3452 -984.79698 -1132.516527 1725.0517554596 -67.28 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 52.972 58.2692 64.09612 70.505732 77.5563052 1381.262920356 -67.28 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 -67.28 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 -67.28 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 -67.28 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356 -67.28 -74.008 -81.4088 -89.54968 -98.504648 1381.262920356
Short period Interest 6.5 7.15 7.87 8.65 5.9895 0 93.0006940299 97.6507287313 102.5332651679 107.6599284263 113.0429248476 0 101.8579029851 117.1365884328 134.7070766978 154.9131382024 178.1501089328 0 97.4292985075 107.1722283582 117.889451194 129.6783963134 142.6462359448 0 97.4292985075 107.1722283582 117.889451194 129.6783963134 142.6462359448 0 97.4292985075 107.1722283582 117.889451194 129.6783963134 142.6462359448 0 97.4292985075 107.1722283582 117.889451194 129.6783963134 142.6462359448 0 97.4292985075 107.1722283582 117.889451194 129.6783963134 142.6462359448 0 97.4292985075 107.1722283582 117.889451194 129.6783963134 142.6462359448 0 97.4292985075 107.1722283582 117.889451194 129.6783963134 142.6462359448 0
Short period Interest Tax Shield 2.6 2.86 3.148 3.46 2.3958 0 37.2002776119 39.0602914925 41.0133060672 43.0639713705 45.217169939 0 40.743161194 46.8546353731 53.8828306791 61.965255281 71.2600435731 0 38.971719403 42.8688913433 47.1557804776 51.8713585254 57.0584943779 0 33.1259614925 36.4385576418 40.082413406 44.0906547466 48.4997202212 0 38.971719403 42.8688913433 47.1557804776 51.8713585254 57.0584943779 0 38.971719403 42.8688913433 47.1557804776 51.8713585254 57.0584943779 0 38.971719403 42.8688913433 47.1557804776 51.8713585254 57.0584943779 0 38.971719403 42.8688913433 47.1557804776 51.8713585254 57.0584943779 0 38.971719403 42.8688913433 47.1557804776 51.8713585254 57.0584943779 0
Terminal Values
Terminal Value at target debt ratio 942.8934250765 16406.2522625072 25855.4494382048 19063.3487691544 17763.4947528194 19063.3487691544 19063.3487691544 21436.432680614 19063.3487691544
Unlevered Terminal Value (UTV) 840.8895 14601.6438298553 23011.4749984835 16966.4725665362 15628.0865069383 16966.4725665362 16966.4725665362 16966.4725665362 16966.4725665362
Tax-Shields in Terminal Value 102.0039250765 1804.608432652 2843.9744397214 2096.8762026182 2135.4082458811 2096.8762026182 2096.8762026182 4469.9601140778 2096.8762026182
Present Values
PV0 of short horizon FCFF @ r0 $186 2394.6325239518 -2863.0280133137 916.0738811595 621.7674965952 916.0738811595 916.0738811595 916.0738811595 916.0738811595
PV0 of terminal FCFF or PV0 of UTV @ r0 $437 9037.679131726 14242.9393435006 10501.3885313422 9672.9952420683 10501.3885313422 10501.3885313422 10501.3885313422 10501.3885313422
PV0 of short horizon tax-shields @ rb $10 160.2508432664 212.1359374342 175.471435482 191.772060636 175.471435482 175.471435482 175.471435482 167.6225507422
PV0 of tax-shields in Terminal Value @ rb $54 1387.3304803311 2186.364839108 1612.0174419767 1641.6397562255 1612.0174419767 1612.0174419767 3436.3753376744 1495.043752555
Total Value of Levered Firm $687 12979.8929792753 13778.4121067292 13204.9512899604 12128.174555525 13204.9512899604 13204.9512899604 15029.3091856581 13080.1287157989
Notes
1) Have fun with the real data exercise.
This is a starting point. Feel free to make changes if you wish. Fix any residual errors that may exist. Use it as a ‘sandbox’.
It will take several hours get comfortable with the framework to make meaningful interpretation.
The MSN data links FOR Boeing/Cost/Microsoft are below. Use historical data from these links or any other sources.
Boeing http://moneycentral.msn.com/investor/invsub/results/statemnt.asp?Symbol=ba
Costco http://moneycentral.msn.com/investor/invsub/results/statemnt.asp?Symbol=cost
Microsoft http://moneycentral.msn.com/investor/invsub/results/statemnt.asp?Symbol=msft
2) ‘Proforma’ tab has proforma data for income and balance sheet. Three color coded zones identify
data, assumptions and conclusions. Use historical data to construct the proforma. Change assumptions as needed.
3) ‘FCFF and FCFE’, tab constructs the free cashflows several ways using the proforma from ‘Proforma’ tab.
4) ‘Value’ tab makes assumptions about discount rates and uses data from ‘Proforma’ and ‘FCFF and FCFE’ tabs to find value.
Takeaways (very sketchy description)
1) Payout ratio does not affect value
2) Cost structure and growth assumptions can radically affect value
3) Growth may not enhance value
4) Terminal value can be large
5) Develop intuition for FCFF FCFE and Value
6) Learn the standard process of valuation
This spreadsheet model
1) Sales Growth in a given year will need investment. 10% growth will raise future FCFF by 10%.
2) Growth needs to stop at some point to be harvested
3) Discounted benefits of growth may be negative, net of initial FCFF requirement.
http://moneycentral.msn.com/investor/invsub/results/statemnt.asp?Symbol=ba
http://moneycentral.msn.com/investor/invsub/results/statemnt.asp?Symbol=cost
http://moneycentral.msn.com/investor/invsub/results/statemnt.asp?Symbol=msft
Proforma
Pro Forma Income Statement Historical Data
Year 0 1 2 3 4 5 6 Assumptions
Sales Growth 8% 8% 8% 8% 8% 3% Conclusions
Payout Ratio 27.7% 27.7% 27.7% 27.7% 27.7% 27.7%
Tax Rate 36.6% 36.6% 36.6% 36.6% 36.6% 36.6%
Int. Rate 5.5% 5.5% 5.5% 5.5% 5.5% 5.5% Value $13,205
Sales 71,422 77278.6 83615.4 90471.9 97890.6 105917.6 109095.2
CGS 62,335 67446.5 72977.1 78961.2 85436.0 92441.8 95215.0 87.3% % of Sales
SGA 6,537 7073.0 7653.0 8280.6 8959.6 9694.3 9985.1 9.2% % of Sales
Depreciation 728 787.7 852.3 922.2 997.8 1079.6 1112.0 1.0% % of Sales
EBIT 1,822 1971.4 2133.1 2308.0 2497.2 2702.0 2783.1
Interest Expense 108 95.8 103.7 112.2 121.4 131.4 135.3 4.0% % of LT Debt
Profit bef. Taxes 1,714 1875.6 2029.4 2195.8 2375.8 2570.6 2647.8
Taxes 628 686.5 742.7 803.7 869.6 940.9 969.1
Net Income 1,086 1189.1 1286.6 1392.1 1506.3 1629.8 1678.7 1.5%
Dividend Payment 329.4 356.4 385.6 417.2 451.5 465.0
Pro Forma Balance Sheet
Year 0 1 1 1 1 1 1
Cash 3,727 4032.6 4363.3 4721.1 5108.2 5527.1 5692.9 5.2% % of Sales
Mktbl Securities 371 401.4 434.3 470.0 508.5 550.2 566.7 0.5% % of Sales
A/R 834 902.4 976.4 1056.4 1143.1 1236.8 1273.9 1.2% % of Sales
Inventory 5,405 5848.2 6327.8 6846.6 7408.1 8015.5 8256.0 7.6% % of Sales
Fixed Assets(net) 11,642 12596.6 13629.6 14747.2 15956.5 17264.9 17782.8 16.3% % of Sales
Total 21,979 23781.3 25731.3 27841.3 30124.3 32594.5 33572.3
A/P 5,450 5896.9 6380.4 6903.6 7469.7 8082.3 8324.7 7.6% % of Sales
Oth Curr.Liabilities 3,831 4145.1 4485.0 4852.8 5250.7 5681.3 5851.7 5.4% % of Sales
LT Debt 2,680 2378.1 2573.1 2784.1 3012.4 3259.4 3357.2 10% % of Assets
Stockholder’s Equity 10,018 11361.1 12292.7 13300.7 14391.4 15571.5 16038.6
Total 21,979 23781.3 25731.3 27841.3 30124.3 32594.5 33572.3
FCFF 208.6 225.7 244.3 264.3 286.0 1199.5
FCFE -154.0 355.0 384.1 415.6 449.7 1211.5
Addn to Retained Income 1086.0 859.7 930.2 1006.5 1089.0 1178.3 1213.7
New Equity Isuued 483.4 1.4 1.5 1.6 1.8 -746.5
LT Debt Issued -301.9 195.0 211.0 228.3 247.0 97.8
New External Capital 181.5 196.4 212.5 229.9 248.8 -648.8
FCFF and FCFE
This tab is tied to ‘Proforma’ worksheet tab
FCFF two methods
1.0 2.0 3.0 4.0 5.0 6.0
EBIT after Tax 1249.9 1352.4 1463.3 1583.2 1713.1 1764.5
add back Depreciation 787.7 852.3 922.2 997.8 1079.6 1112.0
subtract increase in WC 86.6 93.7 101.4 109.7 118.7 47.0
subtract capital expenditure 1742.3 1885.2 2039.8 2207.1 2388.0 1629.9
208.6 225.7 244.3 264.3 286.0 1199.5
Net Income 1189.1 1286.6 1392.1 1506.3 1629.8 1678.7
add back after tax interest 60.8 65.7 71.1 77.0 83.3 85.8
add back Depreciation 787.7 852.3 922.2 997.8 1079.6 1112.0
subtract increase in WC 86.6 93.7 101.4 109.7 118.7 47.0
subtract capital expenditure 1742.3 1885.2 2039.8 2207.1 2388.0 1629.9
208.6 225.7 244.3 264.3 286.0 1199.5
FCFE three methods
Net Income 1189.1 1286.6 1392.1 1506.3 1629.8 1678.7
add back Depreciation 787.7 852.3 922.2 997.8 1079.6 1112.0
subtract increase in WC 86.6 93.7 101.4 109.7 118.7 47.0
subtract capital expenditure 1742.3 1885.2 2039.8 2207.1 2388.0 1629.9
add net borrowing -301.9 195.0 211.0 228.3 247.0 97.8
-154.0 355.0 384.1 415.6 449.7 1211.5
FCFF 208.6 225.7 244.3 264.3 286.0 1199.5
subtract after tax interest 60.8 65.7 71.1 77.0 83.3 85.8
add net borrowing -301.9 195.0 211.0 228.3 247.0 97.8
-154.0 355.0 384.1 415.6 449.7 1211.5
Dividends 329.4 356.4 385.6 417.2 451.5 465.0
add equity repurchases -483.4 -1.4 -1.5 -1.6 -1.8 746.5
-154.0 355.0 384.1 415.6 449.7 1211.5
Value
r0 10.07% Assumptions This tab is tied to ‘Proforma’ worksheet tab and the ‘FCFF and FCFE’ tab
rb 5.4% decrease rb increase value
Target debt to value ratio 0.211 increase in D/V increase value firm
WACC Calculation
Cost of Levered Equity, rs 10.86%
WACC 9.29%
Short Horizon 0 1 2 3 4 5 6
Growth Rates 8% 8% 8% 8% 8% 3% From ‘Proforma’ Worksheet
FCFF 208.6 225.7 244.3 264.3 286.0 1199.5 From FCFF and FCFE worksheet
Short period Interest 95.8 103.7 112.2 121.4 131.4 From ‘Proforma’ Worksheet
Short period Interest Tax Shield 35.1 38.0 41.1 44.4 48.1
Terminal Values
Terminal Value at target debt ratio $19,063
Unlevered Terminal Value (UTV) 0 0 0 0 $16,966
Tax-Shields in Terminal Value 0 0 0 0 $2,097
Present Values
PV0 of short horizon FCFF @ r0 $916
PV0 of terminal FCFF or PV0 of UTV @ r0 $10,501
PV0 of short horizon tax-shields @ rb $175
PV0 of tax-shields in Terminal Value @ rb $1,612
Total Value of Levered Firm $13,205
Costco
Cash
Period End Date 8/30/09 BB 2,619 62701 CGS
Period Length 52 Weeks collections 71,336 558 taxes paid
Stmt Source 10-K
Stmt Source Date 10/16/09
Stmt Update Type Updated
Income Statement
Revenue 71,422
Cost of Revenue, Tota (62,335)
Gross Profit 9,087
Selling/General/Administrative Expenses, Total (6,555)
Depreciation (728)
Operating Income 1,804
interest expense (108)
Interest and Investment Income 27
Income (Loss) on Equity Investments 33
Currency Exchange Gains (Loss) (5)
Other Non-Operating Income (Expenses) (8)
Merger & Restructuring Charges (17)
Gain (Loss) on Sale of Investments (12)
Income Before Tax 1,714
Income Tax – Total (628)
Income After Tax 1,086 73,955 63,259
EB 10,696
Assets difference 6,969
Cash & Equivalents 3,157 2,619 538
Short Term Investments 570 656 (86)
Cash and Short Term Investments 3,727 3,275 452
Total Receivables, Net 834 748 86 748 71,422 71,336 834 – 0
Total Inventory 5,405 5,039 366 5,039 62701 62,335 5,405 0
Other Current Assets, Total 371 400 (29) 400 29 371 (0)
Total Current Assets 10,337 9,462 875 – 0
– 0 – 0
Property/Plant/Equipment, Total – Ne 10,900 10,355 545 10,355 1,250 705 10,900 – 0
Goodwill, Net 71 74 (3) 74 3 71 (0) interest
Long Term Investments 322 432 (110) 432 110 322 0 110
Note Receivable – Long Term 56 59 (3) 59 3 56 (0)
Other Long Term Assets, Total 293 300 (7) 300 7 293 0
Total non-current assets 11,642 11,220 422
Total Assets 21,979 20,682 1,297
Liabilities and Shareholders’ Equity
Accounts Payable 5,450 5,225 225
Accrued Expenses 1,720 1,604 116
Notes Payable/Short Term Debt 16 134 (118)
Current Port. of LT Debt/Capital Leases 81 6 75
Other Current Liabilities, Total 2,014 1,905 109
Total Current Liabilities 9,281 8,874 407
Total Long Term Debt 2,206 2,206 0 2,206 2,206 (0)
Deferred Income Tax 388 328 60 328 628 558 388 10
Minority Interest 86 82 4
Total non-current liabilities 2,680 2,616
Total liabilities 11,961 11,490
Equity
Retained Earnings (Accumulated Deficit 6,101 5,361 740 5,361 1,086 (296) 6,101 50
Stock 3,917 3,831 86
Total Equity 10,018 9,192
Total Equity+liabilities 21,979 20,682
CASH FLOW
Net Income/Starting Line 1,086
Depreciation/Depletion 728 ok
Deferred Taxes 70
Non-Cash Items 205
Changes in Working Capital 3
Cash from Operating Activities 2,092
Capital Expenditures (1,250)
Other Investing Cash Flow Items, Total 149
Cash from Investing Activities (1,101)
Financing Long Term (7)
Total Cash Dividends Paid (296)
Issuance (Retirement) of Debt, Net (136) (18)
Cash from Financing Activities (439)
Foreign Exchange Effects (14)
Net change in cash 538
Analysis
Assumption change in red (other parameters remain constant) scenario
baseline 1 2 3 4 5 6 7 8 Conclusion
Sales Growth 8% 5% 15% 8% 8% 8% 8% 8% 8% increase sales growth increases value
Payout Ratio 28% 28% 28% 40% 28% 28% 28% 28% 28% does not affect
Tax Rate 37% 37% 37% 37% 40% 37% 37% 37% 37% increase in tax rate decreases value
Int. Rate 6% 6% 6% 6% 6% 10% 4% 6% 6% does not affect
FCFF 209 226 244 264 286 1200 578 539 566 594 624 1095 -576 -745 -856 -985 -1133 1725 209 -74 -81 -90 -99 1381 142 58 64 71 78 1381 209 -74 -81 -90 -99 1381 209 -74 -81 -90 -99 1381 -74 -81 -90 -99 1381 -74 -81 -90 -99 1381 209 -74 -81 -90 -99 1381 209
FCFE -154 355 384 416 450 1212 147 595 625 656 689 1102 -793 -436 -501 -576 -663 1737 -154 103 114 125 138 1390 -218 229 252 277 305 1390 -154 103 114 125 138 1390 -154 103 114 125 138 1390 103 114 125 138 1390 103 114 125 138 1390 -154 103 114 125 138 1390 -154
r0 10% 10% 10% 10% 10% 10% 10% 10% 10%
rb 5% 5% 5% 5% 5% 5% 5% 5% 7% increase in rb decreases value
Target debt to value ratio 0.211 0.211 0.211 0.211 0.211 0.211 0.211 0.4 0.211 increase in debt to value increases value
Cost of Levered Equity, rs 10.86% 10.86% 10.86% 10.86% 10.82% 10.86% 10.86% 12.04% 10.59%
WACC 9.29% 9.29% 9.29% 9.29% 9.22% 9.29% 9.29% 8.60% 9.29%
Growth Rates 8% 8% 8% 8% 8% 3% 5% 5% 5% 5% 5% 3% 15% 15% 15% 15% 15% 3% 10% 10% 10% 10% 10% 3% 10% 10% 10% 10% 10% 3% 10% 10% 10% 10% 10% 3% 10% 10% 10% 10% 10% 3% 10% 10% 10% 10% 10% 3% 10% 10% 10% 10% 10% 3% 10% 10% 10% 10% 10% 3%
FCFF 209 226 244 264 286 1,200 513 539 566 594 624 1,095 (648) (745) (856) (985) (1,133) 1,725 (67) (74) (81) (90) (99) 1,381 53 58 64 71 78 1,381 (67) (74) (81) (90) (99) 1,381 (67) (74) (81) (90) (99) 1,381 (67) (74) (81) (90) (99) 1,381 (67) (74) (81) (90) (99) 1,381 (67) (74) (81) (90) (99) 1,381
Short period Interest 96 104 112 121 131 – 0 93 98 103 108 113 – 0 102 117 135 155 178 – 0 97 107 118 130 143 – 0 97 107 118 130 143 – 0 97 107 118 130 143 – 0 97 107 118 130 143 – 0 97 107 118 130 143 – 0 97 107 118 130 143 – 0 97 107 118 130 143 – 0
Short period Interest Tax Shield 35 38 41 44 48 – 0 37 39 41 43 45 – 0 41 47 54 62 71 – 0 39 43 47 52 57 – 0 33 36 40 44 48 – 0 39 43 47 52 57 – 0 39 43 47 52 57 – 0 39 43 47 52 57 – 0 39 43 47 52 57 – 0 39 43 47 52 57 – 0
Terminal Values
Terminal Value at target debt ratio 19,063 16,406 25,855 19,063 17,763 19,063 19,063 21,436 19,063
Unlevered Terminal Value (UTV) 16,966 14,602 23,011 16,966 15,628 16,966 16,966 16,966 16,966
Tax-Shields in Terminal Value 2,097 1,805 2,844 2,097 2,135 2,097 2,097 4,470 2,097
Present Values
PV0 of short horizon FCFF @ r0 $916 $2,395 ($2,863) $916 $622 $916 $916 $916 $916
PV0 of terminal FCFF or PV0 of UTV @ r0 $10,501 $9,038 $14,243 $10,501 $9,673 $10,501 $10,501 $10,501 $10,501
PV0 of short horizon tax-shields @ rb $175 $160 $212 $175 $192 $175 $175 $175 $168
PV0 of tax-shields in Terminal Value @ rb $1,612 $1,387 $2,186 $1,612 $1,642 $1,612 $1,612 $3,436 $1,495
Total Value of Levered Firm $13,205 $12,980 $13,778 $13,205 $12,128 $13,205 $13,205 $15,029 $13,080
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.