Exercises for Course

This page has exercises and other files (utility files and database files) that I use for renewable energy courses. The files to download below include both a completed case and a case with selected blank equations. I have tried to create practical exercises where you can get your hands dirty and see how some of the key concepts work. In creating the exercises with excel files, I have included the titles for the models so you do not have to waste your time typing data. I have left blanks on selected equations that I think will be helpful in understanding the concepts. For example, in the first exercise, I think it is important to understand that when comparing the economics of different technologies, that the real cost and not the nominal levelized cost is used. To see how this works, you fill in equations for a very simple financial model with and without inflation. The exercises below cover both economic issues like the economics of storage and hydrogen as well as the financial issues. The financial issues cover issues that include creation of a simple financial model to demonstrate the proof of levelised equations; equations that demonstrate the importance of debt sizing and debt structuring; exercises that show how risks as measured with P90, P99 etc. are computed and can affect the debt structuring.

.

Excel Utilities to Make Excel Exercise Work More Smoothly and Reading PDF

I have included two files that are used to make the excel exercises work more smoothly. The first is a file called Read PDF which allows you to grab data from PDF files and then convert the data to excel files. To run this file you copy stuff from the pdf file and then operate the macro with SHIFT, CNTL, A. When you copy data from a PDF to excel, make sure that you copy and paste special as UNICODE text. There are different formats that you can use to resolve the PDF. If you are reading from the Lazard LCOE stuff you can use the first green box. If you are reading from the PVGIS you can use the second green box.

The second file is a file that has a whole lot of macros to prevent you from wasting time on formatting and copying formulas to the right. This file is called GENERIC MACROS. I have revised the generic macro file in the link below so that you are not prevented to open it because of something called the auto open — a macro that operates when you open the file and excel considers dangerous. When working through the exercises, it would help a lot if you have this file open and enable the macros. The big things that this GENERIC MACRO allows are to press SHIFT, CNTL, R to copy to the right and also CNTL, ALT, C to open the window that has a whole lot of formatting options. There are a lot more utility macros in the GENERIC MACROS file. You can go to https://edbodmer.com/excel-utilities-and-backpack/generic-macros-file/ and see some of the other stuff including a few user defined functions.

.

Read PDF to Excel File that Allows you to Format Data After Copying from PDF File (Press Shift, Cntl, A)

.

Generic Macro File for Copying to Right (SHIFT, CNTL, R), Formatting (CNTL, ALT, C) and Other Macros (UDFs)

.

Files to Download with Blank and Completed Exercises

In presenting the exercise files below, I first put the exercise file with blanks to download and then I put the file with the completed exercise. In working through the course, we will use the blank file for each session so that you can get your hands dirty and see how things work. The exercise file with blanks is the first file. In each file I have included a little comment button the explains the objectives of the particular exercise. There are five sets of blank and completed exercises. The first part works through LOCE issues and basic financial modelling. The second part addresses risk issues and things like calculating and then applying P90 etc. The third exercise deals with nuances and upsides from project finance analysis covering things like development fees, re-financing and selling of projects. The fourth exercise covers basic and then more advanced issues associated with batteries and storage. The fifth and final exercise covers hydrogen from the purchase of energy to produce hydrogen to downstream costs to the costs of vehicles and other uses of hydrogen.

,

Part 1: Exercise on LCOE and on Financial Model

The first exercise demonstrates calculation of LCOE using the PMT function and the exercises demonstrate how a correct calculation of LCOE can be proven with a project finance model. Reconciliation of the LCOE with a Simple Project Finance Model demonstrates the importance of calculating Real LCOE, that is the implied price that inflates over time. In the exercise , you work through some yield calculations for solar and some power curve calculations for wind. Then you start with some equations from a real financial model. You also work through the equity IRR target to derive the project IRR. To fill in the exercises, download the second file. The first file has the completed exercises. The exercise for computing the LCOE is illustrated in the screenshot below. You will fill in some of the blanks to understand how to compute LCOE. A second sheet will take you through a basic financial model and project returns. Other sheets will address resource issues and debt sizing to compute equity IRR.

The exercise files to download are below. The file attached to the top blue button should be downloaded for the class — it has the blanks with yellow. If you want to see the completed exercise (I could have made mistakes in my own exercise) you can download the file attached to the second button.

.

Renewable Course Exercise Part 1 Blank - Excel File with Equiations to Fill for LCOE, Resource Analysis and Basic Financial Model

.

Renewable Exercise Course Part 1 Filled - Excel File with Completed Equiations - LCOE, Resource Analysis, Basic Financial Model

.

Part 2: Exercise on Risk Analysis Illustrating Calculation of P Values and Judgement in the P Values

The second exercise addresses computations for risk analysis. This exercise demonstrates how to compute P values using NORMSINV and use mean squared error to add up standard deviation from different (independent) sources. It also includes a financial model that demonstrates how the risk analysis affects the returns to equity investors through debt sizing. The type of exercise for you to complete is illustrated in the screenshot below.

.

.

As with the first exercise, the button attached to the first file is the file with equations for you to fill in. The second button is attached to the file with the completed exercise.

.

Renewable Exercise Part 2 Blank - Excel File with Blank Equations for Risk Analysis with Debt Sizing Analysis

.

Renewable Exercise Part 2 - Excel File with Completed Equations for Risk Analysis with Debt Sizing Analysis

.

.

Part 3: Nuances and Upside in Project Finance

The third exercise has more nuanced issues for project finance analysis. The general idea of this exercise is to demonstrate that the IRR in the initial analysis is just a first step. Because the risk of renewable projects declines, it is possible to realize upsides. These upsides include use of development fees, use of re-financing and use of the sale of a plant to investors who are interested in paying for safe investments. This third exercise also includes the true definition of IRR, the theory of development risk and development fee; upside option from re-finance; the upside option from selling a project; computing performance measures and equity bridge loans. As with the first exercise, the first file is completed and the second exercise has equations that you should fill in.

.

.

As with the first exercise, the button attached to the first file is the file with equations for you to fill in. The second button is attached to the file with the completed exercise.

.

Part 3 - Excel File with Blank Equations for Project Finance Issues including Development Fee, Re-financing, Plant Sale

.

Part 3 - Excel File with Filled Equations for Project Finance Issues including Development Fee, Re-financing, Plant Sale

.

Part 4: Storage Analysis Exercises

The fourth set of files works through exercises for electricity storage and renewable energy. This includes a simple exercise with levelised cost of storage. Next you gradually work through more and more complex exercises that include things like depth of discharge and round trip efficiency. The exercise includes analysis of and then an exercise where you have to work through hour by hour charge and discharge of batteries As with the first exercise, the first file is completed and the second exercise has equations that you should fill in.

.

.

As with the first exercise, the button attached to the first file is the file with equations for you to fill in. The second button is attached to the file with the completed exercise.

.

Renewable Exercise Part 4 Blank - Excel File with Equations for Storage Analysis and Battery to Complete

.

Renewable Exercise Part 4 - Excel File with Completed Equations for Storage Analysis with Battery Characteristics

.

In addition to the above exercises, I have included a file in this section that demonstrates how to work with hourly solar data from PVGIS. In this file I use hourly data that you can download and demonstrate how the hourly data conforms to the summary data presented in the PVGIS website. In the file, you can see that the annual kWh per meter squared conforms to the sum of the hourly data over many years. In the file I also show how to work with dates so that you can quickly perform the analysis. The screenshot below the button for the example file shows that how to set dates after downloading the data. You should first establish the correct starting point that depends on GMT and then add 1/24 to the rest of the dates. As illustrated in the screenshot, this method of adding 1/24 to the date gets the data and the time.

.

Excel File that Demonstrates how to Analyse and Reconcile Hourly Data from the PVGIS Website

.

The next screen shot illustrates how to fill in the rows for dates and time in a few seconds. You can use the formula =if(prior=24,1,prior+1) for the hour in the day and also use the SHIFT, CNTL 2 to get the time.

.

.

Part 5 – Exercise on Hydrogen Analysis

.

The fifth exercise addresses economic issues related to use of hydrogen for different types of energy production and the use of renewable energy to produce the hydrogen. I have put the hydrogen analysis as the final part because hydrogen production involves (1) understanding of levelised cost of different resources (2) financing and computation of the real cost of capital; (3) evaluation of storage; and (4) understanding of the capital and operating cost of different technologies including electrolyzers; steam methane reactors (SMR); ammonia synthesis; electric and fuel cell vehicles and as well as other costs such as distribution of hydrogen.

Sometimes it is so much more difficult to demonstrate things in simple terms than with large complex models. The problem is that you have to go through the complex models to finally arrive at the simple cases. Two examples below are the horrible BS I hear so much about how expensive hydrogen is to store. I may be more expensive than natural gas, but the comparison is not close when you compare to electricity storage with a battery. This is illustrated below.

This includes a analysis of each of the segments of cost from the renewable energy to the electrolyzer to storage and distribution to the cost of ownership of autos As with the first exercise, the first file is completed and the second exercise has equations that you should fill in.

.

.

.As with the first exercise, the button attached to the first file is the file with equations for you to fill in. The second button is attached to the file with the completed exercise.

.

Renewable Exercise Part 5 Blank - Excel File with Equations for Hydrogen Analysi - Vechiles, Electrolyzer, Storage etc.

.

Renewable Part 5 - Excel File with Completed Equations for Hydrogen Analysis Including Vechiles, Electrolyzer, Storage etc.

.

.

.

Slides for Renewable Energy Course

I have included power point slides that I very briefly review in the course. The slides cover the same subjects that are in the exercise files and provide documentation. The first power point attached to the button below addresses the general subjects that I cover.

.

Power Point Slides for Renewable Energy Course Part 1 - Overview of Issues in the Course

.

Power Point Slides for Renewable Energy Course Part 2 - Presentation of Various Issues for Solar Power

.

Power Point Slides for Renewable Energy Course Part 3 - Presentation of Various Issues for Wind Power

.

Power Point Slides for Renewable Energy Course Part 4 - Presentation of Various Issues for Energy Storage and Batteries

.

Power Point Slides for Renewable Energy Course Part 5 - Presentation of Various Issues for Hydrogen and Vehicles

.

.

.

Databases

There are a whole lot of databases that we may use. Some are for financial data like returns. Other files are for data that has volatility. I have included selected updated databases that demonstrate some concepts in the course. The first is a database from PVINSIGHTS.com. Other files are for commodity prices and for interest rates.

Solar Panel Database File from PVINSHGHTS.COM with History of Panel Prices and Polysilicon Prices

.

.

Database for Commodity Prices with Option to Update - Comes from World Bank Pinksheet File

.

Database for Interest Rates with Option to Update - Comes from Federal Reserve Database File