Scribd
Upload
138 views3 pages

10-Year Financial Projection Analysis

The document provides financial data for accounts receivable, inventory, and accounts payable from 2010 to 2019 using formulas to calculate them based on days sales outstanding, cost of sales, and inventory turnover ratio. It also includes a table showing the annual net cash flow calculated from income statements and balance sheets for the years 2009 to 2019. The net present value is calculated for the net cash flows over this period.

Uploaded by

spectrum_48
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLS, PDF, TXT or read online on Scribd
138 views3 pages

10-Year Financial Projection Analysis

The document provides financial data for accounts receivable, inventory, and accounts payable from 2010 to 2019 using formulas to calculate them based on days sales outstanding, cost of sales, and inventory turnover ratio. It also includes a table showing the annual net cash flow calculated from income statements and balance sheets for the years 2009 to 2019. The net present value is calculated for the net cash flows over this period.

Uploaded by

spectrum_48
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLS, PDF, TXT or read online on Scribd

Assuming 360 days in a year

Accounts Receivable = (DSO * Sales) / 360


Inventory = Cost of Sales / Inventory Turnover Ratio
Accounts Payable = (Accounts Payable Days * Cost of Sales) / 360

Accounts Receivable

2010
7,460,492.93 $

2011
8,191,550.44 $

Table 1
2012
8,995,632.60 $

2013
9,879,189.63 $

Inventory

1,114,517.26

1,204,458.81

1,289,493.60

1,367,507.96

1,436,430.36

1,494,318.51

1,539,446.93

1,601,486.64

1,682,201.56

1,783,974.76

Accounts Payable

1,114,517.26

1,204,458.81

1,289,493.60

1,367,507.96

1,436,430.36

1,494,318.51

1,539,446.93

1,601,486.64

1,682,201.56

1,783,974.76

7,460,492.93

8,191,550.44

8,995,632.60

9,879,189.63

10,453,221.00

11,060,049.64

Increase in Net Working Capital

664,120.43

731,057.51

804,082.16

883,557.03

574,031.37

EBIT
Less: Income Tax
Net Income after tax
Add: Depreciation
Less: Increase in Net working Capital
Less: Capital expenditure
Less: Leasehold Improvement
Net Cash Flow

$ 15,693,440.26
$ 6,277,376.10
$ 9,416,064.15
$
503,172.00
$
664,120.43
$
50,000.00
$
10,000.00
$ 9,195,115.72

$
$
$
$
$
$
$
$

23,494,914.35
9,397,965.74
14,096,948.61
505,115.29
883,557.03
50,000.00
10,000.00
13,658,506.86

$
$
$
$
$
$
$
$

25,019,137.18
10,007,654.87
15,011,482.31
505,600.79
574,031.37
50,000.00
10,000.00
14,883,051.72

$
$
$
$
$
$
$
$

2009

Net Working Capital

6,796,372.50

2014
10,453,221.00 $

2015
2016
2017
2018
2019
11,060,049.64 $ 11,700,044.89 $ 12,618,311.92 $ 13,361,101.05 $ 14,422,209.96

$ 11,700,044.89

$ 12,618,311.92

$ 13,361,101.05

$ 14,422,209.96

606,828.64

639,995.25

918,267.03

742,789.13

1,061,108.91

26,742,712.81
10,697,085.12
16,045,627.68
506,086.10
606,828.64
50,000.00
10,000.00
15,884,885.14

$
$
$
$
$
$
$
$

28,673,223.39
11,469,289.36
17,203,934.03
506,571.71
639,995.25
50,000.00
10,000.00
17,010,510.50

$
$
$
$
$
$
$
$

31,611,013.21
12,644,405.28
18,966,607.93
507,057.16
918,267.03
50,000.00
10,000.00
18,495,398.05

$
$
$
$
$
$
$
$

33,332,792.26
13,333,116.90
19,999,675.36
507,543.16
742,789.13
50,000.00
10,000.00
19,704,429.38

$
$
$
$
$
$
$
$

36,864,656.23
14,745,862.49
22,118,793.74
508,029.27
1,061,108.91
50,000.00
10,000.00
21,505,714.10

Table 2

Year
Net Cash Flows
PV Factor
Present Value
NPV

$ 69,205,440.01

$ 17,931,110.36
$ 7,172,444.14
$ 10,758,666.22
$
504,143.76
$
731,057.51
$
50,000.00
$
10,000.00
$ 10,471,752.47

$ 20,515,173.18
$ 8,206,069.27
$ 12,309,103.91
$
504,629.01
$
804,082.16
$
50,000.00
$
10,000.00
$ 11,949,650.77

NPV
4
5
6
9,195,115.72 $ 10,471,752.47 $ 11,949,650.77 $
0.870
0.756
0.658
7,995,752.80 $ 7,918,149.31 $ 7,857,089.35 $

7
13,658,506.86 $
0.572
7,809,295.63 $

8
14,883,051.72 $
0.497
7,399,507.07 $

9
10
11
12
13
15,884,885.14 $ 17,010,510.50 $ 18,495,398.05 $ 19,704,429.38 $ 21,505,714.10
0.432
0.376
0.327
0.284
0.247
6,867,474.21 $ 6,394,880.96 $ 6,046,178.43 $ 5,601,228.62 $ 5,315,883.62

Week 1: Income Statement 10 year projection

2007
500000

UNITS
TOTAL

PER UNIT TOTAL

2008
540000
PER UNIT TOTAL

2009
577800
PER UNIT TOTAL

2010
624024
PER UNIT TOTAL

2011
667706
PER UNIT TOTAL

2012
707768
PER UNIT TOTAL

2013
743156
PER UNIT TOTAL

2014
772883
PER UNIT TOTAL

2015
796069
PER UNIT TOTAL

2016
811991
PER UNIT TOTAL

2017
836350
PER UNIT TOTAL

2018
869804
PER UNIT TOTAL

2019
913294
PER UNIT

REVENUE
PRODUCTS
SERVICE

$25,000,000.00 $ 50.00
$1,500,000.00 $ 3.00

$28,080,000.00 $ 52.00
$1,593,000.00 $ 2.95

$30,946,968.00 $ 53.56
$1,675,620.00 $ 2.90

$34,091,179.95 $ 54.63
$1,719,186.12 $ 2.76

$37,571,889.42 $ 56.27
$1,747,552.69 $ 2.62

$41,419,250.90 $ 58.52
$1,759,785.56 $ 2.49

$45,664,724.12 $ 61.45
$1,755,386.10 $ 2.36

$48,441,139.34 $ 62.68
$1,734,321.46 $ 2.24

$51,391,204.73 $ 64.56
$1,697,033.55 $ 2.13

$54,515,789.97 $ 67.14
$1,644,425.51 $ 2.03

$58,958,826.86 $ 70.50
$1,609,070.36 $ 1.92

$62,543,523.53 $ 71.91
$1,589,761.52 $ 1.83

$67,640,820.70 $ 74.06
$1,585,787.11 $ 1.74

TOTAL REVENUE

$26,500,000.00 $ 53.00

$29,673,000.00 $ 54.95

$32,622,588.00 $ 56.46

$35,810,366.07 $ 57.39

$39,319,442.11 $ 58.89

$43,179,036.46 $ 61.01

$47,420,110.21 $ 63.81

$50,175,460.80 $ 64.92

$53,088,238.28 $ 66.69

$56,160,215.49 $ 69.16

$60,567,897.22 $ 72.42

$64,133,285.05 $ 73.73

$69,226,607.81 $ 75.80

EXPENSES
COGS
ADMINISTRATION
UTILITIES
RENT
DEPRECIATION
CONSULTANTS
BAD DEBT (estimated at 3%)

$10,000,000.00
$5,000,000.00
$200,000.00
$500,000.00
$500,000.00
$35,000.00
$15,000.00

$11,124,000.00
$5,151,600.00
$205,200.00
$513,000.00
$502,200.00
$43,200.00
$10,800.00

$12,260,916.00
$5,304,204.00
$213,786.00
$531,576.00
$502,686.00
$40,446.00
$17,334.00

$13,374,207.17
$5,410,288.08
$220,199.58
$547,523.28
$503,172.00
$43,681.68
$17,854.02

$14,453,505.69
$5,572,596.72
$229,007.56
$563,948.98
$504,143.76
$46,739.40
$18,389.64

$15,473,923.19
$5,795,500.59
$240,457.94
$580,867.45
$504,629.01
$49,543.76
$18,941.33

$16,410,095.55
$6,085,275.62
$254,885.42
$598,293.47
$505,115.29
$52,020.95
$19,509.57

$17,237,164.36
$6,450,392.16
$272,727.40
$616,242.28
$505,600.79
$54,101.79
$20,094.86

$17,931,822.09
$6,901,919.61
$294,545.59
$634,729.54
$506,086.10
$55,724.84
$20,697.70

$18,473,363.11
$7,454,073.18
$321,054.69
$653,771.43
$506,571.71
$56,839.34
$21,318.63

$19,217,839.65
$8,124,939.76
$353,160.16
$673,384.57
$507,057.16
$58,544.52
$21,958.19

$20,186,418.76
$8,937,433.74
$392,007.78
$693,586.11
$507,543.16
$60,886.30
$22,616.94

$21,407,697.10
$9,205,556.75
$439,048.71
$714,393.69
$508,029.27
$63,930.61
$23,295.45

TOTAL EXPENSES

$16,250,000.00 $ 32.50

$17,550,000.00 $ 32.50

$18,870,948.00 $ 32.66

$20,116,925.81 $ 32.25

$21,388,331.75 $ 32.06

$22,663,863.28 $ 32.07

$23,925,195.86 $ 32.24

$25,156,323.63 $ 32.60

$26,345,525.47 $ 33.14

$27,486,992.10 $ 33.88

$28,956,884.01 $ 34.63

$30,800,492.79 $ 35.40

$32,361,951.59 $ 35.41

EBIT
MARGIN

$10,250,000.00 $ 20.50
38.68%

$12,123,000.00 $ 22.45
40.86%

$13,751,640.00 $ 23.80
42.15%

$15,693,440.26 $ 25.14
43.82%

$17,931,110.36 $ 26.83
45.60%

$20,515,173.18 $ 28.94
47.51%

$23,494,914.35 $ 31.56
49.55%

$25,019,137.18 $ 32.32
49.86%

$26,742,712.81 $ 33.55
50.37%

$28,673,223.39 $ 35.28
51.06%

$31,611,013.21 $ 37.78
52.19%

$33,332,792.26 $ 38.33
51.97%

$36,864,656.23 $ 40.39
53.25%

EBITDA
MARGIN

$10,750,000.00 $ 21.50
40.57%

$12,625,200.00 $ 23.38
42.55%

$14,254,326.00 $ 24.67
43.69%

$16,196,612.26 $ 25.95
45.23%

$18,435,254.12 $ 27.58
46.89%

$21,019,802.19 $ 29.66
48.68%

$24,000,029.64 $ 32.24
50.61%

$25,524,737.97 $ 32.97
50.87%

$27,248,798.91 $ 34.19
51.33%

$29,179,795.10 $ 35.91
51.96%

$32,118,070.37 $ 38.39
53.03%

$33,840,335.42 $ 38.91
52.77%

$37,372,685.50 $ 40.95
53.99%

$ 20.00
$ 10.00
$ 0.40
$ 1.00
$ 1.00
$ 0.07
$ 0.03

$ 20.60
$ 9.54
$ 0.38
$ 0.95
$ 0.93
$ 0.08
$ 0.02

$ 21.22
$ 9.18
$ 0.37
$ 0.92
$ 0.87
$ 0.07
$ 0.03

$ 21.43
$ 8.67
$ 0.36
$ 0.88
$ 0.81
$ 0.07
$ 0.03

$ 21.65
$ 8.35
$ 0.37
$ 0.84
$ 0.76
$ 0.07
$ 0.03

$ 21.86
$ 8.19
$ 0.38
$ 0.82
$ 0.71
$ 0.07
$ 0.03

$ 22.08
$ 8.19
$ 0.39
$ 0.81
$ 0.68
$ 0.07
$ 0.03

$ 22.30
$ 8.35
$ 0.40
$ 0.80
$ 0.65
$ 0.07
$ 0.03

$ 22.53
$ 8.67
$ 0.41
$ 0.80
$ 0.64
$ 0.07
$ 0.03

$ 22.75
$ 9.18
$ 0.42
$ 0.81
$ 0.62
$ 0.07
$ 0.03

$ 22.98
$ 9.71
$ 0.43
$ 0.81
$ 0.61
$ 0.07
$ 0.03

$ 23.21
$ 10.28
$ 0.44
$ 0.80
$ 0.58
$ 0.07
$ 0.03

$ 23.44
$ 10.08
$ 0.45
$ 0.78
$ 0.56
$ 0.07
$ 0.03

Solution 1 :
Table 1
Accounts Receivable

2010
7,460,492.93 $

2011
8,191,550.44 $

2012
8,995,632.60 $

2013
9,879,189.63 $

Inventory

2009

1,114,517.26

1,204,458.81

1,289,493.60

1,367,507.96

1,436,430.36

1,494,318.51

1,539,446.93

1,601,486.64

1,682,201.56

Accounts Payable

1,114,517.26

1,204,458.81

1,289,493.60

1,367,507.96

1,436,430.36

1,494,318.51

1,539,446.93

1,601,486.64

1,682,201.56

1,783,974.76

7,460,492.93

8,191,550.44

8,995,632.60

9,879,189.63

10,453,221.00

11,060,049.64

11,700,044.89

12,618,311.92

13,361,101.05

14,422,209.96

664,120.43

731,057.51

804,082.16

883,557.03

574,031.37

606,828.64

639,995.25

918,267.03

742,789.13

1,061,108.91

$
$
$
$
$
$
$
$

15,693,440.26
6,277,376.10
9,416,064.15
503,172.00
664,120.43
50,000.00
10,000.00
9,195,115.72

$
$
$
$
$
$
$
$

17,931,110.36
7,172,444.14
10,758,666.22
504,143.76
731,057.51
50,000.00
10,000.00
10,471,752.47

$
$
$
$
$
$
$
$

20,515,173.18
8,206,069.27
12,309,103.91
504,629.01
804,082.16
50,000.00
10,000.00
11,949,650.77

$
$
$
$
$
$
$
$

23,494,914.35
9,397,965.74
14,096,948.61
505,115.29
883,557.03
50,000.00
10,000.00
13,658,506.86

$
$
$
$
$
$
$
$

25,019,137.18
10,007,654.87
15,011,482.31
505,600.79
574,031.37
50,000.00
10,000.00
14,883,051.72

$
$
$
$
$
$
$
$

26,742,712.81
10,697,085.12
16,045,627.68
506,086.10
606,828.64
50,000.00
10,000.00
15,884,885.14

$
$
$
$
$
$
$
$

28,673,223.39
11,469,289.36
17,203,934.03
506,571.71
639,995.25
50,000.00
10,000.00
17,010,510.50

$
$
$
$
$
$
$
$

31,611,013.21
12,644,405.28
18,966,607.93
507,057.16
918,267.03
50,000.00
10,000.00
18,495,398.05

$
$
$
$
$
$
$
$

33,332,792.26
13,333,116.90
19,999,675.36
507,543.16
742,789.13
50,000.00
10,000.00
19,704,429.38

$
$
$
$
$
$
$
$

36,864,656.23
14,745,862.49
22,118,793.74
508,029.27
1,061,108.91
50,000.00
10,000.00
21,505,714.10

Net Working Capital

6,796,372.50

Increase in Net Working Capital

2014
10,453,221.00 $

2015
11,060,049.64 $

2016
11,700,044.89 $

2017
12,618,311.92 $

2018
13,361,101.05 $

2019
14,422,209.96
1,783,974.76

Table 2
EBIT
Less: Income Tax
Net Income after tax
Add: Depreciation
Less: Increase in Net working Capital
Less: Capital expenditure
Less: Leasehold Improvement
Net Cash Flow

NPV
Year
Net Cash Flows
PV Factor
Present Value

1
9,195,115.72 $
0.870
7,995,752.80 $

2
10,471,752.47 $
0.756
7,918,149.31 $

3
11,949,650.77 $
0.658
7,857,089.35 $

4
13,658,506.86 $
0.572
7,809,295.63 $

5
14,883,051.72 $
0.497
7,399,507.07 $

6
15,884,885.14 $
0.432
6,867,474.21 $

7
17,010,510.50 $
0.376
6,394,880.96 $

8
18,495,398.05 $
0.327
6,046,178.43 $

9
19,704,429.38 $
0.284
5,601,228.62 $

10
21,505,714.10
0.247
5,315,883.62

$69,205,440.01

Net Present Value

$1,314,005.13

Net Residual Value

Revised Net Present Value

$70,519,445.14

Residual value will increase net present value.


Solution 2 :
2010
624024

UNITS
TOTAL

2011
667706
PER UNIT

TOTAL

2012
707768
PER UNIT

TOTAL

2013
743156
PER UNIT

TOTAL

2014
772883
PER UNIT

TOTAL

PER UNIT

2015
796069
TOTAL

2016
811991
PER UNIT

TOTAL

2017
836350
PER UNIT

TOTAL

PER UNIT

2018
869804
TOTAL
PER UNIT

2019
913294
TOTAL

PER UNIT

REVENUE
PRODUCTS
SERVICE

$34,091,179.95
$1,719,186.12

$54.63
$2.76

$37,571,889.42
$1,747,552.69

$56.27
$2.62

$41,419,250.90
$1,759,785.56

$58.52
$2.49

$45,664,724.12
$1,755,386.10

$61.45
$2.36

$48,441,139.34
$1,734,321.46

$62.68
$2.24

$51,391,204.73
$1,697,033.55

$64.56
$2.13

$54,515,789.97
$1,644,425.51

$67.14
$2.03

$58,958,826.86
$1,609,070.36

$70.50
$1.92

$62,543,523.53
$1,589,761.52

$71.91
$1.83

$67,640,820.70
$1,585,787.11

$74.06
$1.74

TOTAL REVENUE

$35,810,366.07

$57.39

$39,319,442.11

$58.89

$43,179,036.46

$61.01

$47,420,110.21

$63.81

$50,175,460.80

$64.92

$53,088,238.28

$66.69

$56,160,215.49

$69.16

$60,567,897.22

$72.42

$64,133,285.05

$73.73

$69,226,607.81

$75.80

EXPENSES
COGS
ADMINISTRATION
UTILITIES
RENT
DEPRECIATION
CONSULTANTS
BAD DEBT (estimated at 3%)

$13,374,207.17
$5,410,288.08
$220,199.58
$547,523.28
$503,172.00
$43,681.68
$17,854.02

$21.43
$8.67
$0.35
$0.88
$0.81
$0.07
$0.03

$14,453,505.69
$5,572,596.72
$229,007.56
$563,948.98
$504,143.76
$46,739.40
$18,389.64

$21.65
$8.35
$0.34
$0.84
$0.76
$0.07
$0.03

$15,473,923.19
$5,795,500.59
$240,457.94
$580,867.45
$504,629.01
$49,543.76
$18,941.33

$21.86
$8.19
$0.38
$0.82
$0.71
$0.07
$0.03

$16,410,095.55
$6,085,275.62
$254,885.42
$598,293.47
$505,115.29
$52,020.95
$19,509.57

$22.08
$8.19
$0.34
$0.81
$0.68
$0.07
$0.03

$17,237,164.36
$6,450,392.16
$272,727.40
$616,242.28
$505,600.79
$54,101.79
$20,094.86

$22.30
$8.35
$0.35
$0.80
$0.65
$0.07
$0.03

$17,931,822.09
$6,901,919.61
$294,545.59
$634,729.54
$506,086.10
$55,724.84
$20,697.70

$22.53
$8.67
$0.37
$0.80
$0.64
$0.07
$0.03

$18,473,363.11
$7,454,073.18
$321,054.69
$653,771.43
$506,571.71
$56,839.34
$21,318.63

$22.75
$9.18
$0.40
$0.81
$0.62
$0.07
$0.03

$19,217,839.65
$8,124,939.76
$353,160.16
$673,384.57
$507,057.16
$58,544.52
$21,958.19

$22.98
$9.71
$0.42
$0.81
$0.61
$0.07
$0.03

$20,186,418.76
$8,937,433.74
$392,007.78
$693,586.11
$507,543.16
$60,886.30
$22,616.94

$23.21
$10.28
$0.45
$0.80
$0.58
$0.07
$0.03

$21,407,697.10
$9,205,556.75
$439,048.71
$714,393.69
$508,029.27
$63,930.61
$23,295.45

$23.44
$10.08
$0.48
$0.78
$0.56
$0.07
$0.03

TOTAL EXPENSES

$20,116,925.81

$32.24

$21,388,331.75

$32.03

$22,663,863.28

$32.07

$23,925,195.86

$32.19

$25,156,323.63

$32.55

$26,345,525.47

$33.09

$27,486,992.10

$33.85

$28,956,884.01

$34.62

$30,800,492.79

$35.41

$32,361,951.59

$35.43

EBIT
MARGIN

$15,693,440.26
43.82%

$25.15

$17,931,110.36
45.60%

$26.85

$20,515,173.18
47.51%

$28.94

$23,494,914.35
49.55%

$31.62

$25,019,137.18
49.86%

$32.37

$26,742,712.81
50.37%

$33.59

$28,673,223.39
51.06%

$35.31

$31,611,013.21
52.19%

$37.80

$33,332,792.26
51.97%

$38.32

$36,864,656.23
53.25%

$40.36

EBITDA
MARGIN

$16,196,612.26
45.23%

$25.96

$18,435,254.12
46.89%

$27.61

$21,019,802.19
48.68%

$29.66

$24,000,029.64
50.61%

$32.29

$25,524,737.97
50.87%

$33.03

$27,248,798.91
51.33%

$34.23

$29,179,795.10
51.96%

$35.94

$32,118,070.37
53.03%

$38.40

$33,840,335.42
52.77%

$38.91

$37,372,685.50
53.99%

$40.92

NPV
Year
Net Cash Flows
PV Factor
Present Value

$
$

Net Present Value


Net Residual Value

Revised Net Present Value


Residual value will increase net present value.

1
16,196,612.26 $
0.870
14,084,010.66 $

2
18,435,254.12 $
0.756
13,939,700.66 $

$120,364,092.53
$29,752,162.84
$150,116,255.38

3
21,019,802.19 $
0.658
13,820,861.14 $

4
24,000,029.64 $
0.572
13,722,094.84 $

5
25,524,737.97 $
0.497
12,690,305.89 $

6
27,248,798.91 $
0.432
11,780,407.72 $

7
29,179,795.10 $
0.376
10,969,765.80 $

8
32,118,070.37 $
0.327
10,499,454.18 $

9
33,840,335.42 $
0.284
9,619,535.37 $

10
37,372,685.50
0.247
9,237,956.28

You might also like