Chat with us, powered by LiveChat cash flow analysis | Gen Paper
+1(978)310-4246 credencewriters@gmail.com

cash flow analysis

by | Jun 9, 2022 | Uncategorized | 0 comments

  

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
We offer the best custom paper writing services.  We have done this question before,  we can also do it for you.

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 Your 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.
 
error: Content is protected !!