roi template final

8/6/2019 ROI Template Final

1/23

ROI TEMPLATE

PROJECT TITLE

Please fill this page

TEMPLATE KEY

Input to be given in all Rows/Columns having Pink color in all sheets

General Assumption

PKR 1

US $ 86Euro 112

LC Commission Charges % 0.05%

Import Incidentals % (Import Value) 3%

Erection and Installation Cost % (Including Civil Work) 5%

Revenue Infaltion % 10%

Cost/Expense Price Infaltion % 12%

Depreciation Rate % 5%

Contigencies of Total Investment 5%

Loan and Repayment Assuption

Debt % of Total Investment 60%

Tenor Years 10

Mark-up Rate (First Year)% 15%

Increase in M-UP rate evry Year % 0.5%

PROFIT AND LOSS ASSUMPTIONS

Insurance factor % (All Capital Items except Land) 4.5%

Tax rate% 1%

Cost of Capital% 10%

Repair and Maintainance (All Capital Items except Land) % 3%


2/23

first


3/23

INITIAL INVE

Description/Location

6KM Khurrianwala Jaranwala Road

Total Value of Land

Building

Description

Machinery Hall

Total Value of Building

PLANT AND MACHINERY

1.

Land (If Required)

Sr.No.


4/23

Sr.No.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

Total Initail Investment before Contigen

Add:

Contigencies % of Total Initial Investme

Grandt total of intial investment


5/23

PROJECT TITLE

STMENT

Acres Rate per Acre

2.00 1,000,000.00

Sq Ft Rate Per Sq Ft

2,500.00 1000

Description Number Currency

Purchase price (Imported Machinery Item 12.00 Euro 112.00

Exchange Rate(In Terms of

PKR)


6/23

Description Number CurrencyExchange Rate

(In Terms ofPKR)

Purchase price (Imported Machinery Item 25.00 PKR 1.00

Purchase price (Imported Machinery Item 3)

US $ 86.00

Purchase price (Imported Machinery Item 41.00 Euro 112.00

Purchase price (Imported Machinery Item 5)US $ 86.00

Purchase price (Imported Machinery Item 6)US $ 86.00

Purchase price (Local Machinery Item 7)PKR 1.00

Purchase price (Local Machinery Item 8) PKR 1.00

Purchase price (Local Machinery Item 9)PKR 1.00

Sales value of old plantPKR 1.00

LC Comission Charges @ 0.05%PKR 1.00

Import IncidentalsPKR 1.00

PKR 1.00

Grand Total (Rs.)

ies

t

Erection and Installation Cost (IncludingCivil Work)


7/23

Amount PKR in Millions

2,000,000.00 2.00

- -

- -

2,000,000.00 2.00

Amount PKR in Millions

2,500,000.00 2.50

- -

- -

- -

- -

- -

2,500,000.00 2.50

Per Unit Price

PKR

200,000 44,800,000 44.80

US $

Toal Cost inPKR

Conversion inPak Rs. in

Million


8/23

Per Unit Price

PKR

Toal Cost inPKR

Conversion inPak Rs. in

Million

50,000 250,000 0.25

Euro

- - -

25,000 2,800,000 2.80

- - -

- - -

- - -

- - -

- - -

- - - PKR

- 23,925 0.02

- 1,435,500 1.44

- 2,392,500 2.39

49,309,425.00 49.309

53,809,425.00 53.809

2,690,471.25 2.69

56,499,896.25 56.50


9/23

ADDITIONAL REVENUE FROM PRODUCTION (IF RE

Years 1

Additionally Produced Units10,000.00

Price Per Unit (Rs.)600.00

Cost Per Unit (Rs.)480.00

Additional Revenue (Million Rs.)1,200,000.00


10/23

UIRED

2 3 4 5 6 7

10,000.00 10,000.00 10,000.00 10,000.00 10,000.00 10,000.00

660.00 726.00 798.60 878.46 966.31 1,062.94

537.60 602.11 674.37 755.29 845.92 947.43

1,224,000.00 1,238,880.00 1,242,345.60 1,231,707.07 1,203,819.92 1,155,017.11


11/23

PKR in Million

8 9 10

10,000.00 10,000.00 10,000.00

1,169.23 1,286.15 1,414.77

1,061.13 1,188.46 1,331.08

1,081,031.84 976,909.61 836,908.11


12/23

Electricity (Expense) /Savings

Description year 1 year 2

INCREASE/(DECREASE) IN ELECTRICITY CONSUMPTION ( 6.00 6.00

PER UNIT ELECTRICITY PRICE ASSUMED 9.70 10.86

Working hours per day 24.00 24.00

# OF WORKING DAYS PER YEAR 365 365

ELECTRICITY COST PER PLANT PER Year (Rs) 509,832 571,012


13/23

SALARIES AND WAGES- COST/SAVINGS

Descriptionyear 1 year 2

Increased / (Decreased) number of Personnel (Executive cadre) (5.00) (5.00)

Average Salary Per Executive (Per Month) 35,000 39,200

Fringe benefits Per Executive (Per Month) 3,000 3,360

Increased / (Decreased) number of Personnel (Non Executive cadre) (10.00) (10.00)

Average Salary Per Non Executive (Per Month) 8,000 8,960

Fringe benefits Per Non Executive (Per Month) 1,000 1,120

Net Incremental (Increase) / Decrease in Salary Expense Per Year ### ###


14/23

Project Loan Repayme

Principal (Rs.) 56,499,896 Years

1

2

3

4

5

6

7

8

9

10

Rs.

Year Quarter Repayment

1 Grace Period 56,499,896 -

2

2 Q1 56,499,896 1,569,442

2 Q2 54,930,455 1,569,442

2 Q3 53,361,013 1,569,442

2 Q4 51,791,572 1,569,442

3

3 Q1 50,222,130 1,569,442

3 Q2 48,652,688 1,569,442

3 Q3 47,083,247 1,569,4423 Q4 45,513,805 1,569,442

4

4 Q1 43,944,364 1,569,442

4 Q2 42,374,922 1,569,442

4 Q3 40,805,481 1,569,442

4 Q4 39,236,039 1,569,442

5

5 Q1 37,666,598 1,569,442

5 Q2 36,097,156 1,569,442

5 Q3 34,527,714 1,569,4425 Q4 32,958,273 1,569,442

6

6 Q1 31,388,831 1,569,442

6 Q2 29,819,390 1,569,442

6 Q3 28,249,948 1,569,442

6 Q4 26,680,507 1,569,442

7

Opening

Balance


15/23

7 Q1 25,111,065 1,569,442

7 Q2 23,541,623 1,569,442

7 Q3 21,972,182 1,569,442

7 Q4 20,402,740 1,569,442

8

8 Q1 18,833,299 1,569,442

8 Q2 17,263,857 1,569,4428 Q3 15,694,416 1,569,442

8 Q4 14,124,974 1,569,442

9

9 Q1 12,555,533 1,569,442

9 Q2 10,986,091 1,569,442

9 Q3 9,416,649 1,569,442

9 Q4 7,847,208 1,569,442

10

10 Q1 6,277,766 1,569,442

10 Q2 4,708,325 1,569,44210 Q3 3,138,883 1,569,442

10 Q4 1,569,442 1,569,442

Depreciation Sc

Rs.

Years Depreciation

1 54,499,896 2,724,995 51,774,901

2 51,774,901 2,588,745 49,186,156

3 49,186,156 2,459,308 46,726,849

4 46,726,849 2,336,342 44,390,506

5 44,390,506 2,219,525 42,170,981

6 42,170,981 2,108,549 40,062,432

7 40,062,432 2,003,122 38,059,310

8 38,059,310 1,902,966 36,156,345

9 36,156,345 1,807,817 34,348,527

10 34,348,527 1,717,426 32,631,101

Opening BookValue

Ending BookValue


16/23

t Schedule

xpected Rate %

15.00%

15.50%

16.00%

16.50%

17.00%

17.50%

18.00%

18.50%

19.00%

19.50%

Markup Expense

56,499,896 8,474,984 8,474,984

54,930,455 2,189,371

53,361,013 2,128,555

51,791,572 2,067,739

50,222,130 2,006,923 8,392,589

48,652,688 2,008,885

47,083,247 1,946,108

45,513,805 1,883,33043,944,364 1,820,552 7,658,875

42,374,922 1,812,705

40,805,481 1,747,966

39,236,039 1,683,226

37,666,598 1,618,487 6,862,383

36,097,156 1,600,830

34,527,714 1,534,129

32,958,273 1,467,42831,388,831 1,400,727 6,003,114

29,819,390 1,373,261

28,249,948 1,304,598

26,680,507 1,235,935

25,111,065 1,167,272 5,081,067

Closing

Balance

Total Markup

for the Year


17/23

23,541,623 1,129,998

21,972,182 1,059,373

20,402,740 988,748

18,833,299 918,123 4,096,242

17,263,857 871,040

15,694,416 798,45314,124,974 725,867

12,555,533 653,280 3,048,640

10,986,091 596,388

9,416,649 521,839

7,847,208 447,291

6,277,766 372,742 1,938,260

4,708,325 306,041

3,138,883 229,5311,569,442 153,021

- 76,510 765,103

edule


18/23

APPRAISAL SHEE

Years 0

Initial Outlay (56,499,896)

Terminal Value -

Additional Production Revenue -

Electricity (Cost) / Savings -

Wages (Cost) / Savings

Net Inflow / (Outflow) -

Annual Costs

Insurance Cost (4.5%) per Rs. 10 -

Repair & Maintenance Cost (3%) -

Financial Charges -

Total Annual Cost

Net Savings (per year) -

Income Tax

Annual Cash In/(Out)Flows for Payback (56,499,896)

Add: Annual Financial Charges

Annual Cash In/(Out)Flows for IRR AND NPV (56,499,896)

Profit/ (Loss) for ROI (Cash Inflows for Payb (56,499,896)

Terminal Value (Check the box if to be included) TRUE

ROI 40%

IRR 20.745%

NPV 22,902,124

Profitability Index @ average markup rate 1.16

Payback Period 9


19/23

Payback Period Schedule

Years Cash Flows

-1 8,693,063

2 8,504,303

3 7,608,954

4 6,620,581

5 5,534,137

6 4,343,844

7 3,043,092

8 1,624,324

9 78,91010 31,028,094

FALSE


20/23

1 2 3 4 5

-

1,200,000 1,224,000 1,238,880 1,242,346 1,231,707

509,832 571,012 639,533 716,277 802,231

(3,360,000) (3,763,200) (4,214,784) (4,720,558) (5,287,025)

(1,650,168) (1,968,188) (2,336,371) (2,761,935) (3,253,087)

245,250 260,946 247,898 235,503 223,728

1,634,997 1,831,197 2,050,940 2,297,053 2,572,699

8,474,984 8,392,589 7,658,875 6,862,383 6,003,114

(10,355,231) (10,484,731) (9,957,713) (9,394,939) (8,799,541)

8,705,063 8,516,543 7,621,342 6,633,004 5,546,454

(12,000) (12,240) (12,389) (12,423) (12,317)

8,693,063 8,504,303 7,608,954 6,620,581 5,534,137

8,474,984 8,392,589 7,658,875 6,862,383 6,003,114

17,168,047 16,896,891 15,267,828 13,482,964 11,537,251

5,968,068 5,915,558 5,149,646 4,284,238 3,314,612

Years 4 Months


21/23

- (56,499,896)

8,693,063 (47,806,833)

17,197,365 (39,302,531)

24,806,319 (31,693,577)

31,426,900 (25,072,996)

36,961,037 (19,538,860)

41,304,881 (15,195,016)

44,347,972 (12,151,924)

45,972,297 (10,527,599)

46,051,207 (10,448,690)77,079,301 20,579,405

Cumulative CashFlows

Investment to berecovered


22/23

6 7 8 9 10

###

1,203,820 1,155,017 1,081,032 976,910 836,908

898,498 1,006,318 1,127,076 1,262,325 1,413,804

(5,921,468) (6,632,044) (7,427,890) (8,319,236) (9,317,545)

### ### (5,219,782) (6,080,001) (7,066,832)

212,542 201,915 191,819 182,228 173,117

2,881,423 3,227,194 3,614,457 4,048,192 4,533,975

5,081,067 4,096,242 3,048,640 1,938,260 765,103

### ### (6,854,916) (6,168,680) (5,472,194)

4,355,882 ### 1,635,135 88,679 (1,594,638)

(12,038) (11,550) (10,810) (9,769) (8,369)

4,343,844 ### 1,624,324 78,910 31,028,094

5,081,067 4,096,242 3,048,640 1,938,260 765,103

9,424,911 ### 4,672,965 2,017,170 31,793,197

2,235,295 ### (278,641) (1,728,907) (3,320,433)


23/23

roi template final

Documents