Costing Sheet

Zinger Fish Burger

S/no
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Item
Burger buns 4
fish fillet 4
Flour
Salt 1 tsp
Black Pepper 1/2 tsp
Mustard paste 1 tsp
Mayonnaise
White pepper 1/2 tsp
Ajinomoto 1 tsp
Oil
Butter
Lettuce
Cheese slices
Milk
Cornflour 1/2 cup
Baking powder 1/2 tsp
Egg
Ketchup and Sauces

Quantity
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000
1000
12

Rate/kg

Rate /gram

250
38
125
2000
325
400
300
200
175
150
500
400
74
240
55
90

0.25
0.04
0.13
2.00
0.33
0.40
0.30
0.20
0.18
0.15
0.50
0.40
0.07
0.24
0.06
7.50

Costing of four order

Cost of one order in Rs

Quantity used Amount

4
30.00
250
62.50
1000
38.00
15
1.88
8
16.00
15
4.88
50
20.00
8
2.40
15
3.00
200
35.00
200
30.00
30
15.00
200
80.00
250
18.50
500
120.00
8
0.44
5
37.50
10
525.09
131.27

Chicken Zinger Burger

S/no
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Item
Bun
Chicken kg
Black pepper 1 tsp
Mustard powder 1 tsp
Salt 1 tsp
Chinese salt 1 tsp
Worchester sauce 2 tbsp
Flour 2 tsp
Baking powder tsp
Egg 1
Rice flour 2 tbsp
Salt tsp
Corn flour 1 cup
Bread crumbs 1 cup
Ketchup and Sauces
oil
mayonnaise

Quantity

Rate/kg

Rate /gram

1000
1000
1000
1000
1000
1000
1000
1000
12
1000
1000
1000
1000

250
2000
325
125
200
150
38
55
90
40
125
240
225

0.25
2.00
0.33
0.13
0.20
0.15
0.04
0.06
7.50
0.04
0.13
0.24
0.23

1000
1000

175
400

0.18
0.40

Quantity used Amount

4
30
500
125.00
15
30.00
15
4.88
15
1.88
45
9.00
60
9.00
30
1.14
8
0.44
1
7.50
60
2.40
8
1.00
250
60.00
250
56.25
10
200
35.00
50
20.00

Cost of four orders

cost of 1 order Rs

403.48
100.87

Chicken Burger
S/no
1
2
3
4
5
6
7
8
9
10

Item
1 egg
2 tbsp (30 mL) lemon juice
1 small onion
breadcrumbs
2 tsp chopped fresh thyme
1/2 tsp salt
1/2 tsp pepper
454 g chicken
Ketchup and Sauces
4 hamburger buns

Quantity
12
1000
1000
1000
1000
1000
1000
1000

Rate/kg

Rate /gram Quantity used Amount

7.50
1
7.50
0.10
50
5.00
0.03
100
3.00
0.23
250
56.25
1.00
30
30.00
0.13
8
1.00
2.00
8
16.00
0.25
454
113.50
10
4
30.00
Cost of 4 orders
272.25
Cost of 1 Order in Rs
68.06

90
100
30
225
1000
125
2000
250

Beef Burger
S/no
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Item
750g beef mince
1 large onion,
2 garlic cloves, crushed
1 tbs Worcestershire sauce
fresh parsley
breadcrumbs,
1 egg
Salt
2 tbs olive oil
6 hamburger buns,
150g cheddar cheese,
6 Iceberg lettuce leaves
Ketchup and Sauces
tomatoes

Quantity Rate/kg Rate /gram Quantity used Amount

1000
400
0.40
750
300.00
1000
30
0.03
200
6.00
1000
100
0.10
150
15.00
1000
150
0.15
15
2.25
1000
85
0.09
70
5.95
1000
225
0.23
250
56.25
12
90
7.50
1
7.50
1000
125
0.13
8
1.00
1000
900
0.90
30
27.00
7.50
6
45.00
1000
300
0.30
150
45.00
1000
55
0.06
120
6.60
10
1000
40
0.04
150
6.00
Cost of 6 orders
533.55
Cost of 1 order in Rs
88.93

Crispy Chicken Broast

S/no
1
2
3
4

Item
Chicken kg
Vinegar
Salt
Papaya paste 4 tbsp

Quantity Rate/kg Rate /gram Quantity used Amount

1000
250
0.25
500
125.0
1000
150
0.15
50
7.5
1000
125
0.13
15
1.9
1000
200
0.20
100
20.0

5
6
7
8
9
10
11

Red chili
Broast masala
Egg 4
Oil
Salad leaves
Ketchup and Sauces
Onion

1000
1000
12
1000
1000
1000

400
250
90
175
80

0.40
0.25
7.50
0.18
0.08

10
50
4
200
200

30
0.03
Cost of 2 orders
Cost of 1 order in Rs

150

4.0
12.5
30.0
35.0
16.0
10
4.5
266.38
133.19

French Fries
S/no
1
2
3
4
5

Item
Potatoes
Oil for frying
Corn flour
Ketchup and Sauces
Salt to taste

Quantity Rate/kg Rate /gram Quantity used Amount

1000
25
0.03
1000
25.00
1000
175
0.18
500
87.50
1000
240
0.24
500
120.00
10
1000
125
0.13
20
2.50
Cost of 6 orders
245.00
Cost of 1 order in Rs
40.83

Beverages
S/no Item
1 Pepsi
2 Sting

Quantity
250 ml
250 ml

Amount
14
17

Chicken Wings
S/no
1
2
3
4
5
6
7
9
10
11
12

Item
Chicken wings drumsticks
White pepper 1 tsp
Sugar 1 tsp
Baking powder 1/2 tbsp
Cornflour 2 tbsp
White flour 1/2 cup + 1 tbsp
Chicken cube 1 crushed
Eggs 2
Salt to taste
Ketchup and Sauces
Oil

Quantity Rate/kg Rate /gram Quantity used Amount

1000
250
0.25
1000
250.00
1000
2000
2.00
15
30.00
1000
60
0.06
15
0.90
1000
55
0.06
15
0.83
1000
240
0.24
30
7.20
1000
40
0.04
155
6.20
1000
15
0.02
1000
15.00
12
90
7.50
2
15.00
1000
125
0.13
20
2.50
10
1000
175
0.18
400
70.00
Cost of 7 orders
407.63
Cost of 1 order
58.23

Deal Costing
Deal chicken Burger

Fries

Drink

Total Cost

68.06

40.83

14

122.90

133.19

Fries
40.83

Drink
14

Total Cost
188.02

Beef burger Deal

88.93

Fries
40.83

Drink
14

Total Cost
143.76

Fish Zinger Deal

131.27

Fries
40.83

Drink
14

Total Cost
186.11

Chicken Zinger Deal

100.87

Fries
40.83

Drink
14

Total Cost
155.70

Wings Deal

Fries
40.83

Drink
17

Total Cost
116.07

Broast Deal

58.23

Sales Budgeting in Rs
S.no
1
2
3
4
5
6
7
8
9
10
11
12

Item
Chicken zinger
Chicken Burger
Beef Burger
Broast
Fish Zinger
Wings
Deal chicken Burger
Broast Deal
Beef burger Deal
Fish Zinger Deal
Chicken Zinger Deal
Wings Deal

Qty sales per

day
unit price
30.00
25.00
7.00
18.00
15.00
8.00
45.00
20.00
10.00
20.00
50.00
2.00
250.00

285.00
210.00
250.00
350.00
325.00
100.00
435.00
500.00
400.00
455.00
435.00
200.00

Sales in Rs Sale %
8550.00
12.00
5250.00
10.00
1750.00
2.80
6300.00
7.20
4875.00
6.00
800.00
3.20
19575.00
18.00
10000.00
8.00
4000.00
4.00
9100.00
8.00
21750.00
20.00
400.00
0.80
92350.00
100.00

sales per
month(Rs)
256500.00
157500.00
52500.00
189000.00
146250.00
24000.00
587250.00
300000.00
120000.00
273000.00
652500.00
12000.00
2770500.00

In words : Three Crore thirty two lacs forty six thousand

Sales Estimation in Rs for 10 years

10%

Sales of 1st year

33246000.00 10% increase per year
Sales of 2nd year
36570600.00
3324600.00
Sales of 3rd year
40227660.00
3657060.00
Sales of 4th year
44250426.00
4022766.00
Sales of 5th year
48675468.60
4425042.60
Sales of 6th year
53543015.46
4867546.86
Sales of 7th year
58897317.01
5354301.55
Sales of 8th year
64787048.71
5889731.70
Sales of 9th year
71265753.58
6478704.87
Sales of 10th year
78392328.93
7126575.36
Total Sales of 10 years 529855618.28
In Words : Forty five Crore ninety three lack forty one thousand six hundred and twenty nine

Percentage Sale
25
20
15
10
5
0

Percentage Sale
Chicken Chicken Beef Broast Fish
zinger Burger Burger
Zinger

Wings

Deal Broast Beef

Fish Chicken Wings
chicken Deal burger Zinger Zinger Deal
Burger
Deal
Deal
Deal

Burger

Deal

Deal

Deal

Sales of 1st
year (Rs)
3078000.00
1890000.00
630000.00
2268000.00
1755000.00
288000.00
7047000.00
3600000.00
1440000.00
3276000.00
7830000.00
144000.00
33246000.00

Percentage Sale

Production Budgeting in Rs
Qty sales
sales per
Sales of 1st
per day
month(Rs) year (Rs)
S.no Item
unit price Sales in Rs Sale %
30.00
90783.00 1089396.00
1 Chicken zinger
100.87
3026.10
12.00
51046.88
2 Chicken Burger
25.00
68.06
1701.56
10.00
612562.50
18674.25
3 Beef Burger
7.00
88.93
622.48
2.80
224091.00
71921.25
4 Broast
18.00
133.19
2397.38
7.20
863055.00
59072.63
5 Fish Zinger
15.00
131.27
1969.09
6.00
708871.50
8.00
13975.71
6 Wings
58.23
465.86
3.20
167708.57
7 Deal chicken Burger
45.00
122.90
5530.31
18.00 165909.38 1990912.50
8 Broast Deal
20.00
188.02
3760.42
8.00 112812.50 1353750.00
43127.50
9 Beef burger Deal
10.00
143.76
1437.58
4.00
517530.00
10 Fish Zinger Deal
20.00
186.11
3722.12
8.00 111663.50 1339962.00
11 Chicken Zinger Deal
50.00
155.70
7785.17
20.00 233555.00 2802660.00
6963.93
12 Wings Deal
2.00
116.07
232.13
0.80
83567.14
250.00
32650.18
100.00 979505.52 11754066.21
In words : One crore fourteen lacs fifteen thousand and ten

Production Estimation in Rs for 10 years

7%
Sales of 1st year
Sales of 2nd year
Sales of 3rd year
Sales of 4th year
Sales of 5th year
Sales of 6th year
Sales of 7th year
Sales of 8th year
Sales of 9th year
Sales of 10th year
Total Sales of 10 years

11754066.21
12576850.85
13457230.41
14399236.54
15407183.09
16485685.91
17639683.93
18874461.80
20195674.13
21609371.32
162399444.18

7 % increase per year

822784.64
880379.56
942006.13
1007946.56
1078502.82
1153998.01
1234777.87
1321212.33
1413697.19

In words : Fifteen crore seventy seven lacs fourteen thousand eight hundred and eighty six

Direct Labour Budgeting

Required production
Direct Labour Hour per unit
Total direct labour hour required
Rate per hour (Rs)
Total Direct labour Cost

Yearly
90000.00
0.06
5760.00
408.33
2351998.80

Direct Labour Budgeting in Rs for Ten Years

First year
Second year
Third year
Fourth year
Fifth year
Sixth year
Seventh year
Eight year
Nine year
Tenth Year

10%
10 % Increase /
2351998.80
year
2587198.68 235199.88
2845918.55 258719.87
3130510.40 284591.85
3443561.44 313051.04
3787917.59 344356.14
4166709.35 378791.76
4583380.28 416670.93
5041718.31 458338.03
5545890.14 504171.83

Calculations
Total direct labour hour annually =16 *30 = 480 *12 = 5760
Production Units = 250 *30 = 7500 *12 = 90000
Anunually Direct labour hour per unit = 5760/90000 = 0.064
Direct labour rate per hour = 150 *5760 = 864000
Direct labour required per day = 2400/16 = 150
Direct labour hour per unit = 16/250 = 0.064

Manufacturing Over Head Budgeting

Daily
Budgeted Direct Labor Hours
Variable manufacturing overhead rate
Variable manufacturing overhead
Fixed manufacturing overhead
Total manufacturing overhead
Less Depreciation
Cash disbursement for manufacturing
overhead

16.00
8.68
138.89
277.78
416.67
22.37

Monthly
480.00
8.68
4166.67
8333.33
12500.00
671.14

Yearly
5760.00
8.68
50000.00
100000.00
150000.00
8053.68

394.30

11828.86

141946.32

Manufacturing Overhead Budgeting in Rs for

Ten Years
10%
First year
Second year
Third year
Fourth year
Fifth year
Sixth year
Seventh year
Eighth year
Nineth year
Tenth Year

10 % Increase /
year
141946.32

156140.95
171755.04
188930.55
207823.60
228605.96
251466.56
276613.21
304274.54
334701.99

14194.63
15614.09
17175.50
18893.05
20782.36
22860.60
25146.66
27661.32
30427.45

Fixed Manufacturing Overhead

Rent
Total

100000
100000

Variable Manufacturing Overhead

Utility Expenses
Total

50000
50000

Remaining Book value

after 10 years

Depreciation
Chiller
Oven
Air Exhaust
Peeler
Hot plate
Cutter
Sink
Frier
Total

622.266667
570
4500
342.666667
237.5
360
780
641.25
8053.68333

3111.333333
5700
0
1713.333333
2375
0
3900
19237.5
36037.16667

Selling and Administrative Budgeting

Yearly
33246000.00

Budgeted Sales
Variable selling and administrative expense per unit
Variable selling and administrative expense
Fixed selling and administrative expenses:
Office Equipment expenses
Advertising
Maintenance
Fumigation
Depreciation
Total fixed selling and administrative expense
Total Selling and administrative expenses
Less : Depreciation

0.04
1260000.00

Cash disbursements for selling and administrative expenses

1695050.00

124850.00
250200.00
36000.00
24000.00
6566.67
441616.67
1701616.67
6566.67

Selling and Administrative Budgeting in Rs for

Ten Years

10%
First year
Second year
Third year
Fourth year
Fifth year
Sixth year
Seventh year
Eight year
Nine year
Tenth Year

1695050.00 10 % Increase / year

1864555.00
169505.00
2051010.50
186455.50
2256111.55
205101.05
2481722.71
225611.16
2729894.98
248172.27
3002884.47
272989.50
3303172.92
300288.45
3633490.21
330317.29
3996839.23
363349.02

Variable selling and administrative

Packing (7 * 250)
Disposable glass ( 6 *250)
Straw ( 1 *112)
Total

630000
540000
90000
1260000

Depreciation Of Selling and Administrative

Computer
Printer
Cash Register machine
A.C
Total

1250
250
900
4166.66667
6566.66667

CASH BUDGET
Annually
(year 1)
Daily
Monthly
Cash beginning Balance
xxx
xxx
Add : receipts
Cash collection from customer
Total Available
less : Disbursement
Raw material
Direct Labour
Manufacturing overhead
Selling and administrative
Equipment
Total Disbursement
Excess or deficit
Finance
Borrowing
xxx
xxx
Repayment
xxx
xxx
Interest
Total Finance
Cash Ending

Year 2
Year 3
Year 4
Year 5
0 2141882.24 19629342.51 39491990.78 62005616.64

33246000.00 36570600.00 40227660.00 44250426.00 48675468.60

33246000.00 38712482.24 59857002.51 83742416.78 110681085.24
11754066.21 12576850.85 13457230.41 14399236.54
2351998.8 2587198.68 2845918.55 3130510.40
141946.32
156140.95
171755.04
188930.55
1695050.00 1864555.00 2051010.50 2256111.55
257000.00
0
0
0
16200061.33 17184745.48 18525914.50 19974789.04
17045938.67 21527736.77 41331088.01 63767627.74

15407183.09
3443561.44
207823.60
2481722.71
0
21540290.85
89140794.40

6480024.532
0
0
0
6480024.532
0
0
1944007.36 1898394.26 1839097.23 1762011.09
14904056.42 1898394.26 1839097.23 1762011.09
2141882.24 19629342.51 39491990.78 62005616.64

0
0
1661799.11
1661799.115
87478995.28

15943061.33 17184745.48 18525914.50 19974789.04

21540290.85

Year 6
Year 7
Year 8
Year 9
Year 10
87478995.28 116258382.77 148732790.17 185340211.08 226575023.33
53543015.46 58897317.01 64787048.71 71265753.58 78392328.93
141022010.74 175155699.77 213519838.88 256605964.66 304967352.27
16485685.91 17639683.93 18874461.80 20195674.13 21609371.32
3787917.59
4166709.35
4583380.28
5041718.31
5545890.14
228605.96
251466.56
276613.21
304274.54
334701.99
2729894.98
3002884.47
3303172.92
3633490.21
3996839.23
0
0
0
0
0
23232104.44 25060744.30 27037628.22 29175157.18 31486802.68
117789906.31 150094955.47 186482210.66 227430807.48 273480549.59
0
0
0
0
0
0
0
0
0
0
1531523.54
1362165.30
1141999.58
855784.15
483704.08
1531523.542 1362165.297 1141999.579 855784.1458 483704.0824
116258382.77 148732790.17 185340211.08 226575023.33 272996845.50
23232104.44

25060744.30

27037628.22

29175157.18

31486802.68

Cash Flow Statement

Revenue
Cost of manufacturing
Depreciation of Manufacturing
Selling and Administration
Depreciation
Profit before Interest and Tax
Interest
Profit before tax
Tax
Profit

Year 1
33246000.00
14248011.33
8053.68
1695050.00
6566.67
17288318.32
1944007.36
15344310.96
1534431.1
13809879.86

Year 2
36570600.00
15320190.48
8053.68
1864555.00
6566.67
19371234.17
1898394.26
17472839.91
1747283.99
15725555.92

Year 3
40227660.00
16474904.00
8053.68
2051010.50
6566.67
21687125.15
1839097.23
19848027.92
1984802.79
17863225.13

Amount
152043.66
197656.76
256953.79
334039.93
434251.91
564527.48
733885.73
954051.44
1240266.88
1612346.94

balance
6327980.87
6130324.10
5873370.31
5539330.38
5105078.47
4540550.99
3806665.26
2852613.82
1612346.94
0.00

Loan Amortization
year
1
2
3
4
5
6
7
8
9
10

loan
installment
interest
6480024.53 2096051.02 1944007.36
6327980.87 2096051.02 1898394.26
6130324.10 2096051.02 1839097.23
5873370.31 2096051.02 1762011.09
5539330.38 2096051.02 1661799.11
5105078.47 2096051.02 1531523.54
4540550.99 2096051.02 1362165.30
3806665.26 2096051.02 1141999.58
2852613.82 2096051.02
855784.15
1612346.94 2096051.02
483704.08
20960510.24 14480485.71

wacc = Wc *Ks +Wd*Kd(1-T)

wacc =
32%
WE know that :
Wc
0.6
Ks
0.35
Wd
0.4
Tax
0.1
Kd
0.3
1-Tax
0.9

Year 4
44250426.00
17718677.49
8053.68
2256111.55
6566.67
24261016.61
1762011.09
22499005.52
2249900.55
20249104.97

Year 5
48675468.60
19058568.14
8053.68
2481722.71
6566.67
27120557.40
1661799.11
25458758.29
2545875.83
22912882.46

Year 6
53543015.46
20502209.46
8053.68
2729894.98
6566.67
30296290.67
1531523.54
28764767.13
2876476.71
25888290.42

20960510.24

Year 7
58897317.01
22057859.83
8053.68
3002884.47
6566.67
33821952.35
1362165.30
32459787.06
3245978.71
29213808.35

Year 8
64787048.71
23734455.30
8053.68
3303172.92
6566.67
37734800.14
1141999.58
36592800.56
3659280.06
32933520.51

Year 9
71265753.58
25541666.97
8053.68
3633490.21
6566.67
42075976.04
855784.15
41220191.90
4122019.19
37098172.71

Year 10
78392328.93
27489963.44
8053.68
3996839.23
6566.67
46890905.91
483704.08
46407201.82
4640720.18
41766481.64

Cash flows
year
0

Cash flow
-16200061.33

1
2
3
4
5
6
7
8
9
10

15185305.37
17069052.25
19165213.54
21497133.08
24090762.19
26974977.25
30181944.41
33747540.56
37711841.96
42191769.18

Wacc
NPV

32%
$45,729,624.90

IRR
MIRR
PI
Payback Period
Discounted Payback Period

106%
51%
3.82
1.06
1.48

Net Present Value

Formula = Present Value of Inflow - present Value of Outflow
Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Year 7

NPV =

15185305.37 +
1.318

17069052.25 +
1.737124

19165213.54 +
2.289529432

21497133.08 +
3.017599791

24090762.19 +
3.977196525

26974977.25 +
5.24194502

30181944.41 +
6.908883536

NPV =

11521476.00 +

9826041.35 +

8370808.98 +

7123917.87 +

6057221.98 +

5145986.30 +

4368570.44 +

NPV =

61929686.23 -

NPV =

16200061.33

45729624.90

Internal Rate Of Return

YEARS:
Formula :
Outflow

Year 1
=

Inflow
(1.i)^n

Year 2
Inflow
(1.i)^n

Year 3
Inflow
(1.i)^n

Year 4
Inflow
(1.i)^n

Year 5
Inflow
(1.i)^n

Year 6
Inflow
(1.i)^n

-16200061.33 =

15185305.37 +
2.06

17069052.25 +
4.23

19165213.54 +
8.70

21497133.08 +
17.90

24090762.19 +
36.83

26974977.25 +
75.75

-16200061.33 =

7382258.32 +

4034045.80 +

2201966.91 +

1200724.52 +

654152.62 +

356086.36 +

16200061.33 =

16216515.19

Therefore, The IRR is 105.5 % that is greater than Wacc

Modified Internal Rate

YEARS :
Year 0

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

-16200061.33
1

15185305.37 +
12.0015874

17069052.25 +
9.105908501

19165213.54 +
6.908883536

21497133.08 +
5.24194502

24090762.19 +
3.977196525

26974977.25 +
3.017599791

-16200061.33

182247769.6 +

155429228 +

132410228.3 +

112686789.7 +

95813695.67 +

81399685.71 +

Out Flow
16200061.33
Formula :
Outflow =

Inflow
979609486.57

Inflow
(1+Mirr)^10

16200061.33 =

979609486.6
(1+Mirr)^10

(1+Mirr)^10 =

979609486.6
16200061.33

(1+Mirr)^10 =

60.46949246

1+Mirr =

1.507140125

Mirr =

0.507140125

Mirr =

Percentage figure
2.051
2.052
2.053
2.054
2.055
2.056
2.057
2.058
2.059

1.01
1.02
1.03
1.04
1.05
1.06
1.07
1.08
1.09
1.10
1.11
1.12
1.13

50.71%

1.14
1.15
1.16
1.17
1.18
1.19
1.20
1.21
1.22
1.23
1.24
1.25
1.26

1.27
1.28
1.29
1.30
1.31
1.32
1.33
1.34
1.35
1.36
1.37
1.38
1.39

1.40
1.41
1.42
1.43
1.44
1.45
1.46
1.47
1.48
1.49
1.50
1.51
1.52

1.53
1.54
1.55
1.56
1.57
1.58
1.59
1.6
1.61
1.62
1.63
1.64
1.65

1.66
1.67
1.68
1.69
1.7
1.71
1.72
1.73
1.74
1.75
1.76
1.77
1.78

1.79
1.8
1.81
1.82
1.83
1.84
1.85
1.86
1.87
1.88
1.89
1.9
1.91

Year 8

Year 9

Year 10

33747540.56 +
9.105908501

37711841.96 +
12.0015874

42191769.18 15.8180922

3706114.61 +

3142237.83 +

2667310.86 -

Year 7
Inflow
(1.i)^n

Year 8
Inflow
(1.i)^n

Year 9
Inflow
(1.i)^n

16200061.33

16200061.33

Year 10
Inflow
(1.i)^n

30181944.41 +
155.83

33747540.56 +
320.53

37711841.96 +
659.34

42191769.18
1356.26

193689.99 +

105285.31 +

57196.46 +

31108.91

Year 7

Year 8

Year 9

Year 10

30181944.41 +
2.289529432

33747540.56 +
1.737124

37711841.96 +
1.318

42191769.18
1

69102450.04 +

58623662.65 +

49704207.7 +

42191769.18

1.92
1.93
1.94
1.95
1.96
1.97
1.98
1.99
2
2.01
2.02
2.03
2.04

2.05
2.06
2.07
2.08
2.09
2.1
2.11
2.12
2.13
2.14
2.15
2.16
2.17

PAY BACK PERIOD

Year
1
2
3
4
5
6
7
8
9
10

Cash flows
(16,200,061.33)
15,185,305.37
17,069,052.25
19,165,213.54
21,497,133.08
24,090,762.19
26,974,977.25
30,181,944.41
33,747,540.56
37,711,841.96
42,191,769.18

(1,014,755.97)
16,054,296.29
35,219,509.83
56,716,642.91
80,807,405.10
107,782,382.35
137,964,326.76
171,711,867.32
209,423,709.28
251,615,478.46
1.06

PBP

DISCOUNTED PAY BACK PERIOD

Wacc

32%

Year

Cash flows

1
2
3
4
5
6
7
8
9
10

(16,200,061.33)
15,185,305.37
17,069,052.25
19,165,213.54
21,497,133.08
24,090,762.19
26,974,977.25
30,181,944.41
33,747,540.56
37,711,841.96
42,191,769.18

DPBP

Profitability Of Index

Present value

11,521,476.00
9,826,041.35
8,370,808.98
7,123,917.87
6,057,221.98
5,145,986.30
4,368,570.44
3,706,114.61
3,142,237.83
2,667,310.86

(4,678,585.33)
5,147,456.02
13,518,265.00
20,642,182.87
26,699,404.85
31,845,391.14
36,213,961.59
39,920,076.20
43,062,314.03
45,729,624.90
1.48

1
2
3
4
5
6
7
8
9
10

Total
Divide by

11521476.00
9826041.35
8370808.98
7123917.87
6057221.98
5145986.30
4368570.44
3706114.61
3142237.83
2667310.86

61929686.23
16200061.33
3.822805665

Regression Line
n
1
2
3
4
5
6
7
8
9
10

(x)^2
Formula :

Total sales
no of units sold (x)
(y)
90000 33246000.00
90000 36570600.00
90000 40227660.00
90000 44250426.00
90000 48675468.60
90000 53543015.46
90000 58897317.01
90000 64787048.71
90000 71265753.58
90000 78392328.93
900000 529855618.28
x
y
x^2
810000000000.00

x^2
8100000000
8100000000
8100000000
8100000000
8100000000
8100000000
8100000000
8100000000
8100000000
8100000000
81000000000

xy
2992140000000.00
3291354000000.00
3620489400000.00
3982538340000.00
4380792174000.00
4818871391400.00
5300758530540.00
5830834383594.00
6413917821953.40
7055309604148.74
47687005645636.10
xy

Y = a +bx
For b :
b =

n (xy) -(x)(y)
n (x^2) -(x)^2

#DIV/0!

(y) - b (x)
n

#DIV/0!

Total Cost = 0 x + 52985561.83

Regression Line
n
1
2
3

Sales(x)
33246000.00
36570600.00
40227660.00

Cost ( y )
15943061.33
17184745.48
18525914.50

x^2
1105296516000000.00
1337408784360000.00
1618264629075600.00

xy
530043017008843.00
628456452963815.00
745254189691143.00

4
5
6
7
8
9
10
x
(x)^2

44250426.00 19974789.04 1958100201181480.00

883892924175282.00
48675468.60 21540290.85 2369301243429590.00 1048483750673960.00
53543015.46 23232104.44 2866854504549800.00 1243916927036040.00
58897317.01 25060744.30 3468893950505260.00 1476010601637680.00
64787048.71 27037628.22 4197361680111360.00 1751688136132240.00
71265753.58 29175157.18 5078807632934740.00 2079189562413310.00
78392328.93 31486802.68 6145357235851040.00 2468323792645130.00
529855618.3 229161238.01 30145646377998900.00 12855259354377400.00
y
x^2
xy
280746976228016000.00

Formula :
Y = a +bx
For b :
b =

n (xy) -(x)(y)
n (x^2) -(x)^2

0.344297466

(y) - b (x)
n

4673329.12

Total Cost =
Total Cost =
Total Cost =

4673329.12 + 0.344297466 b
0.344297455 (529855618.3) + 4673329.12
187101276

Regression Line
n
1
2
3
4
5
6
7
8
9
10

(x)^2
Formula :

no of units
sold (x)
Total cost ( y )
90000
16200061.33
90000
17184745.48
90000
18525914.50
90000
19974789.04
90000
21540290.85
90000
23232104.44
90000
25060744.30
90000
27037628.22
90000
29175157.18
90000
31486802.68
900000
229418238.01
x
y
810000000000.00
Y = a +bx

For b :
b =

n (xy) -(x)(y)
n (x^2) -(x)^2

#DIV/0!

(y) - b (x)
n

#DIV/0!

Total Cost =0 x + 52985561.83

xy
x^2
8100000000.00 1458005519785.71
8100000000.00 1546627092985.71
8100000000.00 1667332304991.21
8100000000.00 1797731013386.75
8100000000.00 1938626176074.59
8100000000.00 2090889399325.65
8100000000.00 2255466987296.87
8100000000.00 2433386539427.92
8100000000.00 2625764146510.17
8100000000.00 2833812241020.41
81000000000 20647641420805.00
x^2
xy

Summary Chart
IRR
Moderate
Sensitivity
a) Sale decrease 20 %
b) Cost increase 20 %
c) Investment amount increase
d) Interest Increase
e) Tax % increase by 40 %
Scenario
a) Decline in sale by 20 % and Cost increase by 10%
b)Increase in investment and increase in wacc
c) Decline in Tax rate by 5 % and increase in debt ratio 60 %
d) decrease in Sale and increase in debt ratio

Wacc

Mirr

106%

32%

51%

69%
76%
105%
103%
66%

32%
32%
32%
48%
27%

44%
45%
51%
50%
43%

-6%
98%
111%
78%

32%
36%
31%
31%

1%
46%
52%
46%

Npv

payback

$22,777,719.44
$31,976,501.32
$45,659,059.69

1.67
1.51
1.06

$26,036,299.06

1.71

($73,485,815.16)
$26,302,855.91
$50,455,160.03
$30,309,020.77

(2.88)
1.10
1.01
1.45