Hints for this Assignment:

- Review the project descriptions in the textbook: Financial (Chapter 4),

(Chapter 6). Also, examine the project data and parameters.
- I have added additional assumptions for the "investment factor," "oper
cost factor" (in Red color). These will be useful for conducting (1) sensiti
- Use formulas to link "market prices" with "efficiency/shadow prices." T
Analysis to adjust the values based on the original assumptions.
- Complete the remaining cells of NFG1 (this spreadsheet is directly linke
are correct.
- Use the "Move or Copy" function to duplicate Data1 and NFG1, naming
- In spreadsheet NFG2 (or NFG3), select all (Ctrl + A), and then use "Find
Data1 with Data2 (or Data3).
- On the "Data2" or "Data3" spreadsheets, modify some assumptions. Fo
taxes, or the cost of pollution, or increase costs such as investment or op
- Insert another spreadsheet to summarize the results (NPVs, IRRs, etc.)
- Compare the results, and provide your comments or recommendations
 ial (Chapter 4), Economic (Chapter 5), and Referent Groups
ers.
nt factor," "operating cost factor," "revenue factor," and "external
cting (1) sensitivity analysis and (2) exploring new options.
hadow prices." Then apply the "Goal Seek" feature from What-If
mptions.
t is directly linked to Data1). Double-check and ensure the answers
d NFG1, naming them Data2, NFG2, Data3, NFG3, and so on.
d then use "Find and Replace" (Ctrl + H) to replace all instances of

assumptions. For example, you could reduce import taxes, income

nvestment or operating expenses.
NPVs, IRRs, etc.) for the different scenarios and options.
commendations.
KEY VARIABLES Market Pricing Efficiency Pricing Market Pricing Efficiency Pricing
Investment Costs No. Price Cost Price Cost Operating Costs No. Price Cost % Price Cost
(i) Fixed Investment (units) ($) ($) Rent on land (Ha) 100 30.0 3,000 Rent op. cost 0% 0.0 0
Farm equipment (units) 4 - - Fuel (litres) 2,500 - Fuel tax 10% -
Vehicles (units) 3 30,000 90,000 25,000 75,000 Seeds (Kg) 250 20.0 5,000 Seed subsidy 25% 26.7 6,667
Buildings (m2) 250 1,000 250,000 1,000 250,000 Fertilizers (tonnes) 3 - Fertilizer Subsidy 30% -
TOTAL 325,000 Insecticides (litres) 3,000 - Insecticide subsidy 20% -
Investment factor 1 Water (ML) 900 20.0 18,000 Water subsidy 40% 33.3 30,000
(ii) Working Capital Spares 12 - Spares duty 15% -
Fertilizer stocks (tons) 2 0 - 0.00 - Casual labor (days) 100 60.0 6,000 Labour op. cost 20% 12.0 1,200
Insecticide stocks (Litres) 2500 0 - 0.00 - Administration (/month) 12 1000 12,000 Administration (/month) 12,000
Equipment spare parts (unit 10 0 - 0 - Insurance (p.a.) 1 8,263.5 8,263 Insurance (p.a.) 8,263.5 8,263
Fuel stocks (Litres) 500 0 - 0.00 - Management (/month) 12 3,000 36,000 Management 3,000 36,000
TOTAL - - Miscellaneous 1 7,700 7,700 Miscellaneous 7,700 7,700
(iii) Salvage Value 10% - 32,500 TOTAL (Market prices) TOTAL (Shadow Prices) 101,830
Depreciation Life(yrs) Amount p.a. Revenues 1 Operating costs factor
Farm equipment (units) 10 - Apples (tons) 100 1,000 100,000
Vehicles (units) 5 18,000 Peaches (tons) 90 1,250 112,500 External Costs 10,000
Buildings (m2) 20 12,500 Pears (tons) 75 1,500 112,500 External cost factor 1
Financing Amount Interest Life (yrs) TOTAL
Loan 700,000 3.5% 10 Revenue factor 1 Import duties
Overdraft 40,000 5.0% 4 Capacity Output Equipment 10%
Discount rates 5.0% 10.0% 15.0% Year 2016 2017 2018 2019+ Vehicles 20%
Tax rate on profits 25.0% % 25% 50% 75% 100% Conversion factor 1000
 OPTION 1: ORIGINAL ASSUMPTIONS AS DESCRIBE
1) PART 1: FINANCIAL ANALYSIS

INDICES
Year
Production capacity
Operating cost capacity

REVENUES/OPERATING COSTS
Year
Revenues
Operating costs

DEPRECIATION SCHEDULE
Year
Equipments
Vehicles
Buildings
Total depreciation

LOAN SCHEDULE (INVESTMENT)

Year
Beginning balance
Accrued interest
Repayments
Principal
Interest
Ending balance

LOAN SCHEDULE (OVERDRAFT)

Year
Beginning balance
Accrued interest
Repayments
Principal
Interest
Ending balance

Debt Cash Flows

Year
Loan
Repayments
Debt cash flows

WORKING CAPITAL
Year
Working caapital
Changes in working capital

INCOME STATEMENT
Year
Revenues
Operating costs
EBITDA
Depreciation
EBIT
Interest expenses
EBT
Income tax

CASH FLOW STATEMENT (TIPV)

Year
Cash inflows/Receipts
Revenues
Liquidation/Salvage value
Cash outflows/Expenditures
Initial investment/Capital outlay
Changes/Investments in working capital
Operating costs
Net cash flows (before tax)
Income taxes
Net cash flow (after tax)/NCF(TIPV)

CASH FLOW STATEMENT (EPV)

Year
NCF(TIPV)
Debt cash flows
Net cash flows (EPV)

Year
Bankers' net cash flows

Financial discount rates

NPV(Market)
NPV(TIPV)
 NPV(EPV)
NPV(Bankers)
NPV(Direct tax)
Check

Capital sources
Long-term loan
Short-term loan
Equity
Total
WACC

FINANCIAL DISCOUNT RATES

Year
NCF(TIPV)
Project values
Long-term loan
Short-term loan
Equity
D1/E
D2/E
%D1
%D2
%E
re
WACC
Discount factors of EPV (i.e., use re)
Discount factors of TIPV (i.e., use WACC)

PV(EPV) = NCF_EPV * DF_EPV

PV(TIPV) = NCF_TIPV * DF_TIPV

NPV(EPV)
NPV(TIPV)

IRR(EPV)
IRR(TIPV)

2) ECONOMIC/EFFICIENCY ANALYSIS

ECONOMIC BENEFITS/OPERATING COSTS

Year
 Revenues (shadow prices = market prices)
Operating costs (shadow prices ¹ market prices)

WORKING CAPITAL
Year
Working capital (shadow prices ¹ market prices)
Changes in working capital

ECONOMIC CASH FLOWS

Year
Benefits
Revenues
Liquidation values (shadow price)
Market costs
Initial investments (shadow prices)
Changes in working capital
Operating costs
Nonmarket costs (pollution)
Economic net benefits

Social discount rates

NPV (Economy)
IRR (Economy)

3) DISTRIBUTIONAL ANALYSIS

ECONOMIC NET BENEFITS OF REFERENT GROUPS

Year
Method 1: (A + B + C + D) - (B + D)
Method 2: Net benefits for each group
Government
- Import taxes
- Indirect taxes
- Direct taxes (Income taxes)
- Subsidies
Land owner
Unskilled labors
Local communities
Total net benefits of all referent groups
Check for consistency

Social discount rates

Government
 Direct taxes
Indirect taxes
Import taxes
Subsidies
Land owner
Unskilled labors
Local communities
Referent groups
Non-referent groups
UMPTIONS AS DESCRIBED IN TEXTBOOK

2015 2016 2017 2018 2019

0 1 2 3 4
25% 50% 75% 100%
25% 50% 75% 100%

0 1 2 3 4

0 1 2 3 4
0.0 0.0 0.0 0.0
18.0 18.0 18.0 18.0
12.5 12.5 12.5 12.5

0 1 2 3 4
700.0 0.0 0.0 0.0
24.5 0.0 0.0 0.0
84.2 84.2 84.2 84.2

24.5 22.4 20.3 18.0

700

2015 2016 2017 2018 2019

0 1 2 3 4
40 40 40 40
2.0 2.0 2.0
2.0 2.0 2.0
0 0 0
2.0 2.0 2.0
40 40 40 40

0 1 2 3 4
700 40
 700 40.0 0.0 0.0 0.0

0 1 2 3 4
0.0 0.0 0.0 0.0

0 1 2 3 4
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
24.5 2.0 2.0 2.0
-24.5 -2.0 -2.0 -2.0

0 1 2 3 4
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0 0 0 0 0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

0 1 2 3 4
0.0 0.0 0.0 0.0 0.0
700.0 40.0 0.0 0.0 0.0

0 1 2 3 4
-700.0 -40.0 0.0 0.0 0.0

5% 10% 15%
0.0 0.0 0.0
-738.1 -736.4 -734.8
0.0 0.0 0.0
-738.1 -736.4 -734.8

Values Cost of capital Weight

700000 3.5% 17.50
40000 5.0% 1.00
-700000 10% -17.50
40000
-108.7%

0 1 2 3 4
0.0 0.0 0.0 0.0 0.0

700 0 0 0 0
0 40 40 40 40
-700.0 -40.0 -40.0 -40.0 -40.0
-1.0 0.0 0.0 0.0 0.0
0.0 -1.0 -1.0 -1.0 -1.0
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

1
1 #DIV/0! #DIV/0! #DIV/0! #DIV/0!

0.0 0.0 0.0 0.0 0.0

0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0!

0.0
#DIV/0!

Err:523
Err:523

0 1 2 3 4
 0 1 2 3 4
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0

0 1 2 3 4
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

5% 10% 15%
12.2 4.8 2.0

NT GROUPS
0 1 2 3 4
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0
-3.4 -6.8 -10.3 -13.7

0.0 0.0 0.0 0.0

0.0 -3.4 -6.8 -10.3 -13.7
0.0 3.4 6.8 10.3 13.7

5% 10% 15%
-151.4 -98.8 -69.2
 0.0 0.0 0.0
0.0 0.0 0.0
0.0 0.0 0.0
-151.4 -98.8 -69.2
0.0 0.0 0.0
0.0 0.0 0.0
0.0 0.0 0.0
-151.4 -98.8 -69.2
163.7 103.7 71.2
 2020 2021 2022 2023 2024
5 6 7 8 9
100% 100% 100% 100% 100%
100% 100% 100% 100% 100%

5 6 7 8 9

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0
18.0
12.5 12.5 12.5 12.5 12.5

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
84.2 84.2 84.2 84.2 84.2

15.7 13.3 10.8 8.3 5.6

2020
5 6 7 8 9
40
2.0
42
40
2.0
0

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
2.0 0.0 0.0 0.0 0.0
-2.0 0.0 0.0 0.0 0.0

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0 0 0 0 0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0
 5 6 7 8 9
0.0 0.0 0.0 0.0 0.0

0 0 0 0 0
0 0 0 0 0
0.0 0.0 0.0 0.0 0.0
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

0.0 0.0 0.0 0.0 0.0

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

5 6 7 8 9
 5 6 7 8 9
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

5 6 7 8 9
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0
-13.7 -13.7 -13.7 -13.7 -13.7

0.0 0.0 0.0 0.0 0.0

-13.7 -13.7 -13.7 -13.7 -13.7
13.7 13.7 13.7 13.7 13.7
 2025 2026 2027 2028 2029
10 11 12 13 14
100% 100% 100% 100% 100%
100% 100% 100% 100% 100%

10 11 12 13 14

10 11 12 13 14
0.0

12.5 12.5 12.5 12.5 12.5

10 11 12 13 14
0.0
0.0
84.2

2.8

10 11 12 13 14

10 11 12 13 14
0.0

10 11 12 13 14
0.0 0.0 0.0 0.0 0.0

10 11 12 13 14
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

10 11 12 13 14
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0 0 0 0 0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

10 11 12 13 14
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

10 11 12 13 14
0.0
 10 11 12 13 14
0.0 0.0 0.0 0.0 0.0

0 0 0 0 0
0 0 0 0 0
0.0 0.0 0.0 0.0 0.0
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

0.0 0.0 0.0 0.0 0.0

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

10 11 12 13 14
 10 11 12 13 14
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

10 11 12 13 14
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

10 11 12 13 14
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0
-13.7 -13.7 -13.7 -13.7 -13.7

0.0 0.0 0.0 0.0 0.0

-13.7 -13.7 -13.7 -13.7 -13.7
13.7 13.7 13.7 13.7 13.7
 2030 2031 2032 2033 2034
15 16 17 18 19
100% 100% 100% 100% 100%
100% 100% 100% 100% 100%

15 16 17 18 19

15 16 17 18 19

12.5 12.5 12.5 12.5 12.5

15 16 17 18 19

15 16 17 18 19

15 16 17 18 19
 15 16 17 18 19
0.0 0.0 0.0 0.0 0.0

15 16 17 18 19
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

15 16 17 18 19
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0 0 0 0 0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

15 16 17 18 19
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

15 16 17 18 19
 15 16 17 18 19
0.0 0.0 0.0 0.0 0.0

0 0 0 0 0
0 0 0 0 0
0.0 0.0 0.0 0.0 0.0
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

0.0 0.0 0.0 0.0 0.0

#DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!

15 16 17 18 19
 15 16 17 18 19
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

15 16 17 18 19
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

15 16 17 18 19
0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0

0.0 0.0 0.0 0.0 0.0
-13.7 -13.7 -13.7 -13.7 -13.7

0.0 0.0 0.0 0.0 0.0

-13.7 -13.7 -13.7 -13.7 -13.7
13.7 13.7 13.7 13.7 13.7
 2035
20
100%
100%

20

20

12.5

20

20

20
20

20
0.0
0.0
0.0
0.0
0.0
0.0
0.0

20
0.0
0.0

0.0

0
0.0
0.0
0.0

20
0.0
0.0

20
 20
0.0

#DIV/0!

0.0
#DIV/0!

20
 20

0.0

20
32.5
0.0
32.5
0.0

0.0
0.0

32.5

20
32.5

0.0
0.0
-13.7

0.0
-13.7
46.2