Income and Cash Flow Statement Forecast

User Generated

Tbhuve

Writing

Description

Using the income statement from the previous exercise, forecast a ten year cash flow using the following assumptions:

  • Capital Expenditures of $50,000 per year.
  • Leasehold Improvements of $10,000 per year.
  • DSO of 75 Days.
  • Inventory Turnover of 12 times.
  • Accounts Payable of 30 days.
  • Depreciation is constant.
  • The combined Federal and State Tax Rate is 40%.
  • There are no additional financing expenses associated with the transaction.

After you have completed your cash flow forecast, calculate a Net Present Value assuming a discount rate of 15%.

Remember that once you've calculated the Total Working Capital for Year 0, all future Working Capital needs will be incremental. Almost all of the variables will be provided to you here.

The income statement is given in the excel worksheet. Use it and the assumptions given above to do a ten-year cash flow forecast. Use excel to finish all the work, make it just like the income statement that is already given.

Unformatted Attachment Preview

2007 Units Price Units 500000 REVENUES Products Service TOTAL REVENUE EXPENSES COGS Administration Utilities Rent Depreciation Consultants Bad debt TOTAL EXPENSES 2008 Price 540000 25000000 1500000 26500000 50 3 53 28080000 1593000 29673000 52 2.95 54.95 10000000 5000000 200000 500000 500000 35000 15000 16250000 20 10 0.4 1 1 0.07 0.03 32.5 11124000 5151600 205200 513000 502200 43200 10800 17550000 20.6 9.54 0.38 0.95 0.93 0.08 0.02 32.5 EBIT Margin 10250000 38.68% 20.5 12123000 40.86% 22.45 EBITDA Margin 10750000 40.57% 21.5 12625200 42.55% 23.38 Compound unit growth Compound price growth Compund service price COGS growth 4.94% 2.32% -1.12% 1.99% Fixed Costs Administration Utilities Rent Depreciation Average Depreciation 1.99% 2.25% 2.06% 0.18% 2% 0.18% INCOME STATEMENT 2009 Units Price 577800 2010F Price Units Units 606336 2011F Price 636281 30946968 1675620 32622588 53.56 2.9 56.46 33228505 1738616 34967121 54.80 2.87 57.67 12260916 5304204 213786 531576 502686 40446 17334 18870948 21.22 9.18 0.37 0.92 0.87 0.07 0.03 32.66 13122922 5410288 218062 542208 503585 42444 18190 19857697.72 21.64 8.92 0.36 0.89 0.87 0.07 0.03 32.78978038 13751640 42.15% 23.80 15109423.31 43.21% 24.88 16574613.36 44.22% 25.48 14254326 43.69% 24.7 15613007.8 44.65% 25.7 17079098.0 45.57% 26.4 Variable costs Consultants Bad debt 4.94% 4.94% DSO Inventory turnover Acc payables days 35678247 1803980 37482227 56.07 2.84 58.91 14045532 22.07 5518494 9.10 222423 0.37 553052 0.91 504485 0.87 44540 0.07 19088 0.03 20907613.34 33.4247336 75 0.20547945 12 30 2013F Units Price 700682 2014F Units Price 735287 2015F Units Price 771601 57.37 2.80 60.18 41132859 1942173 43075032 58.70 2.77 61.48 44165342 2015190 46180532 47421392 2090952 49512344 61.46 2.71 64.17 15033006 22.51 5628864 9.28 226871 0.37 564113 0.93 505386 0.87 46739 0.07 20031 0.03 22025010.95 34.07236 16089905 5741441 231409 575395 506290 49048 21020 23214507.53 22.96 9.47 0.38 0.95 0.87 0.07 0.03 34.7329 17221109 23.42 18431843 5856270 9.66 5973395 236037 0.39 240758 586903 0.97 598641 507195 0.87 508101 51470 0.07 54012 22059 0.03 23148 24481042 35.40663 25829897.84 23.89 9.85 0.40 0.99 0.87 0.07 0.03 36.0938 18155383.78 45.18% 26.10 19860524.93 46.11% 18660770.2 46.44% 27.0 20366814.7 47.28% Units 2012F Price 667706 38308593 1871802 40180395 26.74 21699490.32 46.99% 27.6 22206685.1 48.09% 60.07 2.74 62.81 27.40 23682446.52 47.83% 28.3 24190547.9 48.86% 28.07 28.9 2016F Units Price 809708 50917491 2169563 53087054 2017F Units Price 849697 62.88 2.68 65.56 54671337 2251128 56922466 2018F Units Price 891662 64.34 2.65 66.99 58701932 2335761 61037693 Units 65.83 2.62 68.45 19727697 24.36 21114657 24.85 22599127 25.34 6092863 10.05 6214720 10.25 6339015 10.45 245573 0.41 250484 0.41 255494 0.42 610614 1.01 622826 1.03 635283 1.05 509010 0.87 509919 0.87 510831 0.87 56680 0.07 59479 0.07 62416 0.07 24291 0.03 25491 0.03 26750 0.03 27266727.2 36.79469 28797576.84 37.50956 30428915.72 38.23869 25820326.83 48.64% 26329336.4 49.60% 28.77 28124889.03 49.41% 29.6 28634808.5 50.30% 29.48 30608777.40 50.15% 30.4 31119608.3 50.98% 2019F Price 935699 63029679 2423575 65453254 67.36 2.59 69.95 24187964 25.85 6465795 10.66 260604 0.43 647988 1.07 511744 0.87 65499 0.07 28071 0.03 32167664.69 38.98238 30.22 33285589.43 50.85% 30.97 31.1 33797333.4 51.64% 31.8
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

2007
Units

Price

Units

500000
REVENUES
Products
Service
TOTAL REVENUE
EXPENSES
COGS
Administration
Utilities
Rent
Depreciation
Consultants
Bad debt
TOTAL EXPENSES

2008
Price
540000

25000000
1500000
26500000

50
3
53

28080000
1593000
29673000

52
2,95
54,95

10000000
5000000
200000
500000
500000
35000
15000
16250000

20
10
0,4
1
1
0,07
0,03
32,5

11124000
5151600
205200
513000
502200
43200
10800
17550000

20,6
9,54
0,38
0,95
0,93
0,08
0,02
32,5

EBIT
Margin

10250000
38,68%

20,5

12123000
40,86%

22,45

EBITDA
Margin

10750000
40,57%

21,5

12625200
42,55%

23,38

Compound unit growth
Compound price growth
Compund service price
COGS growth

4,94%
2,32%
-1,12%
1,99%

Fixed Costs
Administration
Utilities
Rent
Depreciation
Average
Depreciation

1,99%
2,25%
2,06%
0,18%
2%
0,18%

INCOME STATEMENT
2009
Units
Price
577800

2010F
Price

Units

Units

606336

2011F
Price
636281

30946968
1675620
32622588

53,56
2,9
56,46

33228505
1738616
34967121

54,80
2,87
57,67

12260916
5304204
213786
531576
502686
40446
17334
18870948

21,22
9,18
0,37
0,92
0,87
0,07
0,03
32,66

13122922
5410288
218062
542208
503585
42444
18190
19857697,72

21,64
8,92
0,36
0,89
0,87
0,07
0,03
32,78978038

13751640
42,15%

23,80

15109423,31
43,21%

24,88

16574613,36
44,22%

25,48

1425432...


Anonymous
Great! 10/10 would recommend using Studypool to help you study.

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Similar Content

Related Tags