|| Home|| Book || Featured PF Models || Featured Corp Models || Featured Acq Models || PF Exercises || Corp Exercises || Download to Excel || Scenario Analysis || Tornado Diagrams || Monte Carlo || Flexible Graphs || Real Estate || Acq Exercises || Macros || Reading PDF || Waterfall Graphs || Circular Ref || Depreciation || Financial Ratio || Value Driver || Renewable || Testimony || Pricing Case Studies ||

This website is now obsolete because of being extinguished by horrible wikispaces. Please go to http://edbodmer.com for a much improved website.

Solar is different from other technologies in that you can perform a whole lot of analysis from the internet on specific projects in different locations. I have tried to document the process of resource analysis, acquiring cost data and financial analysis using public data. The solar resource analysis explains how to acquire and analyse data for computing solar output. I have updated the resource analysis as there are better sources using an EU funded website. In the resource section I have tried to take the mistery out of computing P90, P75 etc. The resource analysis section address issues with fundamental calculations of solar yield, performance ratios and temperature coefficients. In addition, a database that contains actual historic production data on operating solar projects is described. The first lesson set guides you through hourly data on irradiation and then project the yield (or capacity factor) as well as distributions and probabilities. Subsequent lesson sets involve creating an analysing financial models. One deals with making a single project and another on making a portfolio of projects in a rooftop analysis.

To introduce issues associated with solar analysis, the file below is a database that collects information on the cost of solar panels over time.

Lesson Set 1: Solar Resource Analysis

The first lesson set walks through construction of a solar project finance model including analysis of the solar resource, meaning the amount of solar energy that is available at different places in the globe. The capacity of a panel is defined by something called kWp or MWp where the p represents peak and the measured instantaneous capacity is measured at a term called Standard Testing Conditions or STC. Standard testing conditions are how much power would be produced by a panel if there was solar energy touching the panel of 1,000 W/meter squared (measured with a flash test) and the temprature is 25 degrees Celsius.

If the average amount of energy over the year is 150 W/meter squared and the temperature is always 25 degrees and there are no losses from wiring, snow, shading, dust etc., then the capacity factor would be 15% and the yield would be 150 x 8760/1,000 or 1,314 kWh/kW. If there were no clouds or dust and the sun did not have sun spots, then the amount of solar power (extra terrestrial that sounds like science fiction) could be computed direct from longitude and latitude. But clouds and other things complicate the calculation. These conditions can be measured with satellites that are going around the globe and measuring all sorts of atmospheric conditions. Because of all of this, you can click on a google map type picture and then get the amount of solar power per kW per meter squared in different places.

The discussion below shows how to take various sources of solar power and then adjust them by the performance ratio (temprature, losses etc.) and then compute the solar yield in a crude but reasonable manner. Once the solar power is computed I also demonstrate how to compute things like P90, P95 etc. if you have historic data. (Now let me complain. I would so much rather have a set of historic data than clicking on some kind of cool google map type thing if I am evaluating the risks of solar resources). I have also shown some data on historic solar data that is a good place to start. This was provided by NREL but they decided to stop providing the data. Some websites where you can find solar resource data include Retscreen, JRC, PVWATT and NREL. Links include:




Zipped files:
http://rredc.nrel.gov/solar/old_data/nsrdb/1991-2010/NCDCStationData/Individual Files: http://rredc.nrel.gov/solar/old_data/nsrdb/1991-2010/hourly/list_by_state.html

The videos below describe how to use the EU website including how to download data from PDF files and evaluate the implied performance ratio and the capacity factor in different places.

Hourly Data from Weather Stations:


If you work through the videos and files in the worksheet, you should be above to find the yield or capacity factor for your project and understand the performance ratio. If you want to receive credit for working through the solar analysis, you should be able to (1) find a base yield from looking at the websites; (2) review long-term hourly solar data and compute the P90 and P99 that arises from variation in solar irradiation from year to year (due to clouds and dust); (3) add uncertainty related to the performance ratio and use the mean squared error to develop final P90, P99 etc.; and, (4) examine historic data for actual projects and understand the difference between actual observed variation and variation that is possible before the project begins. A separate spreadsheet is provided if you want to evaluate your skills.

Files associated with Lesson Set 1: Solar Resource Analysis

The first step in the lesson set is evaluating solar resource. The files first set of files involve putting solar data together in a file. The basis for computing P99 etc is the NREL website and download the zipped file for a location (sorry that this is only U.S. data). The first set are files work with detailed raw data from NREL that has hourly data from 1991-2010. If you try this, go to a file and then run the MOVE macro and put each of the in a file. You can do this by opening the template file below and pressing SHIFT, CNTL, X. In addition to files that work through the solar data I have included a database of actual solar projects that is called the solar database. This database allows you to evaluate P90, P99 etc. on the basis of actual data.

Lesson Set 2: Standard Solar Project Finance Model for Grid Mounted Systems

Files associated with Lesson Set 2: Solar Project Finance Model

The second lesson set involves a step-by-step project finance analysis of a solar project. Building a solar project finance model is not very different than some of the other lesson sets that are described in the page name "Project Finance Exercises". The lesson set therefore focuses on incorporating solar resource analysis into a project finance model.

Lesson Set 3: Levelised Cost of Electricity and Currency Analysis in Solar Project Finance

Lesson 5: A-Z Rooftop Solar Model and Building Portfolios

This lesson set walks you through a rooftop analysis beginning with establishing a portfolio from multiple installations to evaluating financing and the ability of the project to repay debt. Parts of the model and lesson are pretty standard but there are quite a few tricky issues. If you make it all the way through all of the lessons you may learn something about both modelling and finance. As different solar facilities on rooftops are assumed to be constructed at different times, the model involves evaluation of how to compute cash flow and production items when rates change over the lifetime of a project such as degradation. This set of videos and files also introduce you various other tricky modelling issues including using sumproduct for S-curves with asset portfolios, inflating in alternative periods, accounts receivable and bad debt when outstanding accounts receivable are longer than a month, allocation to interest during construction and difficult financing issues in a monthly analysis where cash flows can be positive or negative. Video links associated with the lessons are shown below. The second section includes the file with different sheets that should be used together with the videos. If you really fill out each sheet and want your name on my website so that you can tell your boss or future employer that you have completed the lesson, I will do this for a modest fee.

Videos associated with Lesson Set 5: Rooftop Solar Model


Sheet Name in Course File

Video Links

Rooftop Model Overview -- Introduction to Course File, Videos and Exercises



Rooftop Model Inputs -- Structure and Organisation



Setting-up Portfolio Equations in Rooftop Model

Portfolio Set-up Exercise


Computing Capacity and Generation for Solar Facilities

Production Exercise


Degradation Part 1 - Vintage Array

Degradation Example


Degradation Part 2 - User Defined Array Function

Array Functions


Degradation Part 3 - Application of Function in Model

Degradation Calculation


Calculation of Capital Expenditures and SUMPRODUCT Trick

Capital Expenditures


Revenues, Periodic Inflation, Accrued Bad Debts

Revenue Calculation


Working Capital in Monthly Model with Forward Calculations and OFFSET

Accounts Receivable


Operating and Maintenance with Function to Compute by Age

Operating and Maintenance


Compute Cash Flow, Depreciation and IRR and Operating Taxes

Pre-tax and After Tax Cash Flow


Overview of Financing



Financing 1: Cash Flow and Balance Sheet



Financing 2: IDC and Fees with Financial Statements



IDC Accounting and Cash Balance Issues



Scenario Analysis

Scenario Exercise


File associated with Lesson Set 4: Rooftop Solar Model

The file below walks through a solar rooftop model where the major issues involve creating a portfolio with different characteristics including start dates, end dates degradation profiles and operating expenses. It includes detail methods for computing working and financing. There is a set of 15 videos that constitutes a course associated with both basic and advanced issues associated with the rooftop model. Exercises associated with the rooftop model that you can complete are included in a single comprehensive file which is the first file named "rooftop solar course file" listed below. The file includes many pages that work through the steps of completing a model. The completed exercise discussed in the video is on one sheet named the monthly consolidated model. The exercise is shown on a later sheet (with a yellow tab). Key take away points are shown at the top of exercise page that you can work through. If you make an effort to fill in the entire model you should be proud of yourself and I will send you an official badge. I have included the generic macros that has SHIFT,CNTL,R and SHIFT,CNTL,C which are used a lot in the videos.

Videos associated with Lesson Set 1: Resource Analysis and Financing of Solar


Excel File

Video Link

Chapter Reference

Page Reference

Finding Solar Irriatiation Data

Solar Resource Analysis


Working with Hourly Data

NREL Template


Computing P90, P75, P50 etc. from Irriadiation

P90, P95, etc.


Computing Capacity Factors from Irridiation

Net Yield Analysis


Incorporating Performance Ratio Uncertaintity

P90, P95, etc. Multiple Factors


Timing in Solar Project Finance Model

Timing in Solar Model


Yield, Degradation and Capacity in Solar Model

Yield and Capacity in Solar Model


Operating Analysis in Solar Model

Operations Analysis


Working Capital In Solar Model

Solar Working Capital


Financial Related Inputs and Financial Model Structure

Solar Financing Inputs


Financing Equations

Solar Financing Equations


Cash Flow Equations

Solar Cash Flow Equations


Presentation of Annual Data and Debt Cash Flow

DSRA in Solar Model

Preparing Balance Sheet and Audit Checks

Resolving Circular References in Solar Model

Creating Tornado Diagrams in Solar Model

Adding Inflation to a Project Finance Model

Including Depreciation on New Expenditures

Using Models to Create Templates

Working Capital in Project Finance

Scenario Analysis

Tornado Analysis

Tax Equity and Flip Structures

Tax Structuring Model