In Week 2, you will take your income statements and start to construct a cash flow that will also extend into Week 3. For the most part, cash flows are pretty straight forward. If you’re going to expe

In Week 2, you will take your income statements and start to construct a cash flow that will also extend into Week 3. For the most part, cash flows are pretty straight forward. If you’re going to experience trouble, it almost always happens with working capital. 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.

Using the income statement from Assignment 1, 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%.

ATTACHMENT PREVIEW

Download attachment

INCOME STATEMENT
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
UNITS
500000
540000
577800
612468
643091
668815
688880
702657
709684
709684
709684
709684
709684
TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT TOTAL
PER UNIT
REVENUE
PRODUCTS
$25,000,000.00
$50.00
$28,080,000.00
$52.00
$30,946,968.00
$53.56
$33,459,861.80
$54.63
$35,484,183.44
$55.18
$36,903,550.78
$55.18
$38,010,657.30
$55.18
$38,770,870.45
$55.18 $39,158,579.15
$55.18 $39,158,579.15
$55.18 $39,158,579.15
$55.18 $39,158,579.15
$55.18 $39,158,579.15
$55.18
SERVICE
$1,500,000.00
$3.00
$1,593,000.00
$2.95
$1,675,620.00
$2.90
$1,740,634.06
$2.84
$1,791,112.44
$2.79
$1,825,501.80
$2.73
$1,842,661.52
$2.67
$1,841,924.45
$2.62
$1,823,136.83
$2.57
$1,786,674.09
$2.52
$1,750,940.61
$2.47
$1,715,921.80
$2.42
$1,681,603.36
$2.37
TOTAL REVENUE
$26,500,000.00
$53.00
$29,673,000.00
$54.95
$32,622,588.00
$56.46
$35,200,495.86
$57.47
$37,275,295.88
$57.96
$38,729,052.58
$57.91
$39,853,318.82
$57.85 $40,612,794.90
$57.80 $40,981,715.98
$57.75 $40,945,253.24
$57.70 $40,909,519.76
$57.64
$40,874,500.95
$57.60
$40,840,182.51
$57.55
EXPENSES
COGS
$10,000,000.00
$20.00
$11,124,000.00
$20.60 $12,260,916.00
$21.22
$13,386,468.09
$21.86 $14,477,465.24
$22.51
$15,508,260.76
$23.19 $16,452,713.84
$23.88 $17,285,221.16
$24.60 $17,981,815.58
$25.34
$18,521,270.04
$26.10 $19,076,908.15
$26.88 $19,649,215.39
$27.69
$20,238,691.85
$28.52
ADMINISTRATION
$5,000,000.00
$10.00
$5,151,600.00
$9.54
$5,304,204.00
$9.18
$5,463,330.12
$8.92
$5,627,230.02
$8.75
$5,796,046.92
$8.67
$5,969,928.33
$8.67
$6,149,026.18
$8.75
$6,333,496.97
$8.92
$6,523,501.88
$9.19
$6,719,206.93
$9.47
$6,920,783.14
$9.75
$7,128,406.63
$10.04
UTILITIES
$200,000.00
$0.40
$205,200.00
$0.38
$213,786.00
$0.37
$224,475.30
$0.37
$237,943.82
$0.37
$254,599.89
$0.38
$274,967.88
$0.40
$299,714.98
$0.43
$329,686.48
$0.46
$365,952.00
$0.52
$409,866.24
$0.58
$463,148.85
$0.65
$527,989.69
$0.74
RENT
$500,000.00
$1.00
$513,000.00
$0.95
$531,576.00
$0.92
$558,154.80
$0.91
$586,062.54
$0.91
$615,365.67
$0.92
$646,133.95
$0.94
$678,440.65
$0.97
$712,362.68
$1.00
$747,980.81
$1.05
$785,379.85
$1.11
$824,648.85
$1.16
$865,881.29
$1.22
DEPRECIATION
$500,000.00
$1.00
$502,200.00
$0.93
$502,686.00
$0.87
$495,547.86
$0.81
$478,699.23
$0.74
$453,040.95
$0.68
$419,968.96
$0.61
$381,247.82
$0.54
$338,853.07
$0.48
$294,802.17
$0.42
$253,529.86
$0.36
$215,500.38
$0.30
$181,020.32
$0.26
CONSULTANTS
$35,000.00
$0.07
$43,200.00
$0.08
$40,446.00
$0.07
$39,548.67
$0.06
$39,548.67
$0.06
$39,548.67
$0.06
$39,548.67
$0.06
$39,548.67
$0.06
$39,548.67
$0.06
$39,548.67
$0.06
$39,548.67
$0.06
$39,548.67
$0.06
$39,548.67
$0.06
BAD DEBT
$15,000.00
$0.03
$10,800.00
$0.02
$17,334.00
$0.03
$17,146.80
$0.03
$18,157.48
$0.03
$18,865.63
$0.03
$19,413.28
$0.03
$19,783.23
$0.03
$19,962.94
$0.03
$19,945.18
$0.03
$19,927.77
$0.03
$19,910.71
$0.03
$19,894.00
$0.03
TOTAL EXPENSES
$16,250,000.00
$32.50
$17,550,000.00
$32.50
$18,870,948.00
$32.66 $20,184,671.64
$32.96 $21,465,106.99
$33.38
$22,685,728.49
$33.92 $23,822,674.91
$34.58
$24,852,982.70
$35.37 $25,755,726.38
$36.29
$26,513,000.74
$37.36
$27,304,367.47
$38.47 $28,132,755.99
$39.64 $29,001,432.45
$40.87
EBIT
$10,250,000.00
$20.50
$12,123,000.00
$22.45
$13,751,640.00
$23.80 $15,015,824.22
$24.52
$15,810,188.89
$24.58
$16,043,324.10
$23.99 $16,030,643.91
$23.27 $15,759,812.20
$22.43 $15,225,989.60
$21.45 $14,432,252.50
$20.34 $13,605,152.29
$19.17 $12,741,744.96
$17.95
$11,838,750.06
$16.68
MARGIN
38.68%
40.86%
42.15%
42.66%
42.41%
41.42%
40.22%
38.81%
37.15%
35.25%
33.26%
31.17%
28.99%
EBITDA
$10,750,000.00
$21.50
$12,625,200.00
$23.38
$14,254,326.00
$24.67 $15,511,372.08
$16,288,888.12
$16,496,365.05
$16,450,612.88
$16,141,060.03
$15,564,842.66
$14,727,054.66
$13,858,682.15
$12,957,245.34
$12,019,770.39
MARGIN
40.57%
42.55%
43.69%
44.07%
43.70%
42.59%
41.28%
39.74%
37.98%
35.97%
33.88%
31.70%
29.43%

Unlock Solution Unlocking…

1CALCULATION OF GROWTH RATE IN UNITS
2008 (5400000-500000)/500000
8%
2009 (577800-540000)/540000
7%
As the growth rate is fal ing every year by 1%
thus we should predict results using using fol owing rates:
2010
6%
2011
5%
2012
4%
2013
3%
2014
2%
2015
1%
2016 to 2019
0%
2 CALCULATION OF GROWTH RATE IN PRODUCT SELLING PRICE
2008 (52-50)/50
4%
2009 (53.56-52)/52
3%
As the growth rate is fal ing every year by 1%
thus we should predict results using using fol owing rates:
2010
2%
2011
1%
2012 to 2019
0%
3CALCULATION OF GROWTH RATE IN SERVICE PRICE
2008 (2.95-3)/3
-2%
2009 (2.9-2.95)/2.95
-2%
As the growth rate is constant but negative thus we should predict results using fol owing rates:
2010 to 2019
-2%
4 CALCULATION OF GROWTH RATE IN COGS
THESE ARE GENERALLY VARIABLE EXPENSES
2008 (20.60-20)/20
3%
2009 (21.22-20.60)/20.60
3%
As the growth rate is constant thus we should predict results using fol owing rates:
2010 to 2019
3%
5CALCULATION OF GROWTH RATE IN ADMINISTRATION COST
NOTE: THIS COST IN NEVER ANALYSED ON PER UNIT BASIS THUS WE WILL CHECK IN TOTALITY AS IT A FIXED EXPENSE
2008 (5151600-5000000)/5000000
3%
2009 (5304204-5151600)/5151600
3%
As the growth rate is constant thus we should predict results using fol owing rates:
2010 to 2019
3%
6 CALCULATION OF GROWTH RATE IN UTILITIES
NOTE: THIS COST IN NEVER ANALYSED ON PER UNIT BASIS THUS WE WILL CHECK IN TOTALITY AS IT A FIXED EXPENSE
2008 (205200-200000)/200000
3%
2009 (213786-205200)/205200
4%
As the growth rate is rising constantly by 1% every year thus we should predict results using fol owing rates:
2010
5%
2011
6%
2012
7%
2013
8%
2014
9%
2015
10%
2016
11%
2017
12%
2018
13%
2019
14%
7CALCULATION OF GROWTH RATE IN RENT
NOTE: THIS COST IN NEVER ANALYSED ON PER UNIT BASIS THUS WE WILL CHECK IN TOTALITY AS IT A FIXED EXPENSE
2008 (513000-500000)/500000
3%
2009 (531576-513000)/513000
4%
As the growth rate is rising constantly by 1% every year thus we should predict results using fol owing rates:
2010
5%
2011
6%
2012
7%
2013
8%
2014
9%
2015
10%
2016
11%
2017
12%
2018
13%
2019
14%
8CALCULATION OF GROWTH RATE IN DEPRECIATION
NOTE: THIS COST IN NEVER ANALYSED ON PER UNIT BASIS THUS WE WILL CHECK IN TOTALITY AS IT A FIXED EXPENSE
2008 (502200-500000)/500000
0.44%
2009 (502686-502200)/502200
0.10%
AS HERE THERE IS NO TREND IN GROWTH RATE IF WE CHECK IT ON TOTALITY BASIS THUS WE SHOULD NOW CHECK IT ON PER UNIT
BASIS
2008 (0.93-1)/1
-7%
2009 (0.87-0.93)/0.93
-6%
As the growth rate is
fal ing constantly by 1% every year that too negative thus we should predict results using fol owing rates:
2010
-7%
2011
-8%
2012
-9%
2013
-10%
2014
-11%
2015
-12%
2016
-13%
2017
-14%
2018
-15%
2019
-16%
9 CALCULATION OF GROWTH RATE IN CONSULTANTS EXPENSES
NOTE: THIS COST IN NEVER ANALYSED ON PER UNIT BASIS THUS WE WILL CHECK IN TOTALITY AS IT A FIXED EXPENSE
2008 (43200-35000)/35000
23%
2009 (40446-43200)/43200
-6%
AS HERE THERE IS NO TREND IN GROWTH RATE IF WE CHECK IT ON TOTALITY BASIS THUS WE SHOULD NOW CHECK IT ON PER UNIT
BASIS
2008 (0.
.08-0.
.07)/0.07
14%
2009 (0.07-0.08)/0.08
-13%
AS HERE THERE IS NO TREND IN GROWTH RATE IF WE CHECK IT ON PER UNIT
BASIS ALSO WE SHOULD USE AVERAGE METHOD
AVERAGE OF LAST 3 YEARS
$39,548.67
(35000+43200+40446)/3
THUS WE SHOULD TAKE THIS AS EXPENSE IN PREDICTIONS
9 CALCULATION OF GROWTH RATE IN BAD DEBTS
THE BEST WAY TO ANALYSE BAD DEBTS IS AS PERCENTAGE OF REVENUE
200715000/26500000
0.06%
2008 10800/29673000
0.04%
2009 17334/32622588
0.05%
AVERAGE RATE OF BAD DEBT
0.05%

Leave a Comment