Solar Power Plant
Financial Modeling Primer
Table of Contents
Page
A Section 1: Industry Primer 3
A1 Energy & Solar Power Sector Overview 4
A2 Solar Power Plant Components and Development Process 8
A3 Project Costs and Investment Sources 11
A4 Operating Revenue and Cost Drivers 14
B Section 2: Financial Modeling Primer 17
B1 Model Map 18
B2 User Inputs 20
B3 Workings and Schedules 26
B4 Model Output 36
2
A Section 1: Industry Primer
3
A1 Energy & Solar Power Sector Overview
A2 Solar Power Plant Components and Development Process
A3 Project Costs and Investment Sources
A4 Operating Revenue and Cost Drivers
4
Energy Sector Overview
Sources of Energy
Non Renewable Renewable
Oil Solar
Natural Gas Wind
Coal Hydro-electric
Nuclear Geo-thermal
Wood / Bio fuel Bio-mass
5
Solar Power concept and evolution
Concept Evolution
Generation of electricity using sunlight
700 BC Magnifying glass to start fire
1839 Edmond Becquerel discovers the Photo Electric Effect
Sun
Albert Einstein publishes groundbreaking paper on
1905
Photo Electric Effect winning Nobel Prize in 1921
1954 Invention of the first practical silicon solar cell
Solar Cell
Several technology breakthroughs
Today Providing ~1% of total global electricity consumption
Electricity
6
Global Solar Power Map
Solar Power capacities are concentrated mostly in Europe, US, China and India
Source: Global Market Outlook For Solar Power / 2015 2019, SolarPower Europe
7
A1 Energy & Solar Power Sector Overview
A2 Solar Power Plant Components and Development Process
A3 Project Costs and Investment Sources
A4 Operating Revenue and Cost Drivers
8
Solar Power Plant
Solar Farm (Modules) Plant Controlling Station
Sun
Grid
Transformer &
Inverter
Switchgear
AC DC
9
Development Process
1 2 3 4 5 6
Site Selection Feasibility Study Financial Closure Pre-Construction Construction Commissioning
Land / soil details Technical Debt Funding Approvals Site Preparation Licensing
Feasibility
Solar Resources Equity Funding Designs Materials Supply Grid connectivity
Financial
Regulations Feasibility Financial closure Selection of EPC Excavation / Testing
& other vendors infrastructure
Accessibility / Cost / Benefit Handover
Connectivity Project Timeline Mounting /
Risks Mitigation Installation
Geotechnicals strategy
Civil Works
Project Management
10
A1 Energy & Solar Power Sector Overview
A2 Solar Power Plant Components and Development Process
A3 Project Costs and Investment Sources
A4 Operating Revenue and Cost Drivers
11
Project Cost Components
Land Cost Land Purchase Regulatory Costs
2
Grid Connection,
Mounting Structure /
Equipment Module Invertor Installation &
Frames
Commissioning
Civil Works Civil Structures Transmission Lines
Other Costs Project Management Administrative
12
Investment Sources
Promoter Project Owner, energy companies
Sponsor
Strategic Sponsor Developer, utility companies
Equity
Private Equity Infrastructure funds, PE funds, insurance, pension funds
Investor
Public Equity YeildCo listed on US/London stock exchanges
PROJECT
COST
Commercial Banks Banks & financing companies
Recourse
Development Banks Regional & global development agencies
Debt
Bonds Energy bonds quoted / unquoted
Non Recourse
Corporate Finance Energy bonds
13
A1 Energy & Solar Power Sector Overview
A2 Solar Power Plant Components and Development Process
A3 Project Costs and Investment Sources
A4 Operating Revenue and Cost Drivers
14
Revenue drivers & considerations
1 2 3
Sale of Electricity Energy Credits Auxiliary Consumption
Supplies to the plant itself /
Trading of SREC
related entities
Government Public Sector Electricity Distribution Companies
Merchants Corporates
PPA Tariff Duration of contract
15
Operating Costs of a Solar Power Plant
Operations &
Solar Farm maintenance, repair, utility costs
Maintenance
Administrative Staff, administrative expenses and miscellaneous expenses
Invertor Replacement Significant component for replacement of inverter components
Insurance Cost Asset cover, third party liability and other insurance premiums
16
B Section 2: Financial Modeling Primer
17
B1 Model Map
B2 User Inputs
B3 Workings and Schedules
B4 Model Output
18
Model Map
Title Contents Dashboard Global
Title tab Table of contents Graphical Outputs Model wide parameters
Working Calculation Tabs
Rev
Operating Revenue Calculations
WC Output Tabs
Working Capital Calculations
Exp
Model Assumptions Ratios
Operating Costs Calculations
Inputs Tax Financials
Tax Calculations Returns Sensitivities
ProjCost
Project Costs Calculations
Capex
Capital Expenditure
Funding
Capital Structure and Funding Schedules
19
B1 Model Map
B2 User Inputs
B3 Workings and Schedules
B4 Model Output
20
General Inputs
User input for
project start date
Select the Scenario Enter general assumptions of capacity, performance and utilization factors
from dropdown
21
Project Cost Inputs
Enter land price, area and regulatory costs
Enter the cost of the Equipment based on quotations
Enter the cost of Civil Works based on quotations
Enter the estimates of Pre-operating Expenses
Enter the estimates of Maintenance Capex
22
Project Cost Inputs
Enter the payment schedule
Enter the payment schedule, inflation & depreciation rates
Enter the payment schedule, inflation & depreciation rates
Enter the payment schedule, inflation & depreciation rates
23
Operating Inputs
Enter % Government and % Merchant Share of Sales
Enter Government / Merchant Tariff and its growth rate
Enter O&M costs & growth rate as per contract / quote
Enter administrative costs & growth rate
Enter inverter replacement costs & growth rate
Enter insurance costs & growth rate
24
Other Inputs
Enter the average debtor and creditor days
Enter the tax rate of the country
Enter the expected capital structure
Enter terms of debt funding as per term sheets with banks
Enter the details of short term debt terms with banks
Enter the expected dividend payout ratio
Enter the statutory reserve requirements
25
B1 Model Map
B2 User Inputs
B3 Workings and Schedules
B4 Model Output
26
ProjCost
I
J
K
A
B DxI
C JxK
3
AxB
AxBxC
1
L
M
N
D
DxL i
E DxM ii
F N X (1 + 2 + 3 + i + ii)
G 4
H
DxE
DxF 5
DxG 6
DxH
2 1+2+3+4+5+6
Interest on the loan during the construction
period (source tab: Funding
Component of the project cost due to increase in
prices of input resources (source tab: Capex)
27
Rev
A
B
C
D
E
B x E grown annually to C x E in 10 years
C x E grown annually to D x E in 15 years
Start at B x E reduce by degradation factors
Capacity Utilization Factor x A x 365 x 24
F
G
H F grown @ G
I
J
K I grown @ J
1 Annual Generation x Government Tariff
2 Annual Generation x Merchant Tariff
1+2
28
Exp
A
B
C
1 A x B grown @ C
D
E
2 D grown @ E
F
G
H
3 F x G grown @ H
I
J
K
L
4 I x J grown @ L every K years
1+2+3+4
29
WC
A
1
2
Previous periods closing balance
2
Debtor BEG +Revenue in Period Closing Balance
2/1xA
B
3
4
Previous periods closing balance
4
Debtor BEG +Revenue in Period Closing Balance
4/3xB
30
Capex
A
B
AxB
C
1 A x B grown @ C
Cumulative
D
Year number
2 Equal to cost at end of life (D)
3 Last year ending balance
4 1
5 2
3+4-5
E
6 3
7
8 6/Ex7
9 Last year ending balance
10 1
11 8
9 + 10 - 11
31
Funding
1 From Capex
3 A Ax1
4 B Remaining limit
5+6
5 Minimum of 3 & 4
6 Debt Interest Capitalized
7 2-5
32
Funding
1a
1b
A
2 1a + ?
Ax2
3 Interest for construction years only
Interest Paid post construction
4 Previous periods closing balance
5 3
6 Total interest during construction / B
4+5-6
C
7 D 1/D
8 C/7
9 11
10 8, except for last year when it is 9
11 Previous periods closing balance
12 1a + 1b
13 10
11 + 12 - 13
33
Funding
3 Previous periods closing balance
4 1
5 2, if any
6 3+4-5
7 From Financials
8 A Ax7
B Bx6
9 8, such that 10 < Target Balance
10 Previous periods closing balance
11 9
10 + 11
34
Funding
A
1 1
A-1 The macro code
pastes the values
2 From Financials in 3 which is
3 used for the
below schedules
4 3
5 If 3<0, raise equal amount of credit facility
The copying and
3 pasting continues
6 Full Cash Sweep till the sum of
B x average row 2 sum of
B row 3 becomes 0
of 4 & 7
7 Previous periods closing balance
8 5
9 6
7+8-9
Each time the user Sub copy_paste_macro()
changes the inputs to the
model or makes a change Check = difference of sum of 2 and 3 While [Link](Check").Value <> 0
in the model, the user Copy = data cells of 2 [Link](Copy").Copy
should click on this box Paste = data cells of 3 [Link](Paste").PasteSpecial xlPasteValues
which runs the macro and [Link]
estimates any revolving While-Wend loop is used to paste each data cell
credit facility required to of 2 into 3 till they become equal and hence Wend
Check becomes 0
meet near term cash
End Sub
shortfalls Name of the macro (sub) is copy_paste_macro
35
Built In Macro
Objective of The objective of the macro is to ensure that any negative cash shortfall during the life of the project
the Macro is attending to by raising short term overdraft through a revolving credit facility
Algorithm of 1. The macro checks the cash balance as per the balance sheet.
the macro
2. It copies the values into a new row
3. Revolving credit facility during any negative cash balance year is taken equal to that negative amount
4. Now, the interest on the revolving credit facility leads to additional negative cash balance
5. Repeat Step 1 to four till the sum of the cash balances as per the balance sheet is equal to the new row where the macro is
copying and pasting the cash balance
Coding of 1. The macro is coded using the VBA script
the macro
2. The while wend loop is used to recursively copy the contents of row number 2 (defined as Copy) to row number 3
(defined as Copy) . The naming is done using Name Manager in the Formulas Tab under Defined Names
3. The Red box titled Click to run macro is lined to the macro (right click and then Assign Macro
36
Activate the Developer Tab
1. Go to File
2. Click Options
3. Go To Customize Ribbon
4. Check the Developer
Option (as highlighted on
the left)
37
B1 Model Map
B2 User Inputs
B3 Workings and Schedules
B4 Model Output
38
Output Tabs
1 2 3 4 5
Financials Ratios Returns Sensitivities Dashboard
Income Statement Operating Ratios Project Returns Construction Linked Graphs
Revenue Growth, Scenarios
Balance Sheet EBITDA Margin, Investor Returns Project Costs
Net Profit Margin Two Factor Sensitivities Funding Structure
Cash Flow Statement Payback Periods Tariff vs. Project Revenues
Credit Ratios Costs Net Profit
Debt to Equity Total Assets
Debt Service Singe Factor Net Debt
Coverage Sensitivities Project Cash Flows
Interest Coverage Tariff Investor Cash Flows
Capacity Utilization
Liquidity Ratios Operating Costs
Current Ratio Project Costs
Quick Ratio Debt Interest Rate
Cash Ratio Tax Rate
Du Pont Analysis
39
Financials
From Rev
From Exp
From Capex
From Funding
From Funding
From Funding
From Tax
From Inputs
Profit Margin
From Funding
2 Previous Balance
3 1
2+3
40
Financials
From Capex
From WC
From Cash Flows
From Funding
From WC
From Funding
From Funding
From Funding
From Funding
From Income Statement
41
Financials
From Income Statement
From Income Statement
From Income Statement
From Balance Sheet
From Balance Sheet
From Capex
From Funding
From Funding
From Funding
From Income Statement
From Funding
From Funding
1
2 Previous Balance
3 1
2+3
From Funding
From Funding
42
Ratios
Annual Revenue Growth
Operating Profit / Operating Revenues
Net Profit / Operating Revenue
Debt Balance / Total Equity
Operating Profit / (Debt Interest + Repayment)
PBIT / Debt Interest
Current Assets / Current Liabilities
Cash + Receivables / Current Liabilities
Cash / Current Liabilities
Net Profit / Operating Revenue
Operating Revenues / Total Assets
Total Assets / Total Equity
43
Returns
Cash Flow Available for
Debt Service
No Terminal Value
Project IRR indicates the returns to the project irrespective of the capital structure
Equity IRR indicates the returns to the equity investors after the debtors have been fully repaid
Project Payback Period is the number of years it will take to cover up the total project investment
Equity Payback Period is the number of years it will take to cover up the equity investors investment
The Internal Rate of Return is calculated using the excel function =IRR(range of cashflows)
44
Scenario Analysis and Sensitivities
The model has three scenarios :
Construction Period = 1 years and no cost escalations in the Project Cost
Optimistic All debt raised during the first year and interest capitalized only during the first year
Revenues and debt repayment start from second year onwards
Construction Period = 2 years and cost escalations in Year 2 increases the Project Cost
Base All debt raised during the first two years and interest capitalized during the first two years
Revenues and debt repayment start from third year onwards
Construction Period = 3 years and cost escalations in Year 2 & 3 increases the Project Cost
Pessimistic All debt raised during the first three years and interest capitalized during the first three year
Revenues and debt repayment start from fourth year onwards
45
Sensitivities
User selects the desired scenario in the tab Inputs
Total Project Cost corresponding to the scenario
The project and equity returns are summarized here
Using Data Tables, the change in
Project IRR is summarized in the
sensitivity tables where the rows
and columns correspond to the
change in the underlying factors
46
Sensitivities
Using Data Tables, the change in Project IRR is summarized in the sensitivity tables where the rows correspond to the change in the underlying factors
47
Data Tables
The sensitivities are run using Data Tables:
4 Click on Data Tab on the top
1 Create the table selected below, linking the top left cell to the Equity IRR cell in sheet Returns
2 Punch the range of % increases / decreases on first row and first column of the table 5 Select What-if-Analysis
3 Select the Table
6 Click on Data Table
7 Select the variables for the row and column
input cells
Cell G45
The input parameters are put equal to zero and linked to
their corresponding input cells
For e.g. below is a snapshot of how the sensitivity
parameter is linked to the corresponding input cell
48
