Conducting Financial Analysis using EXCEL!
The following videos show you how to conduct financial analysis using EXCEL spreadsheet, we will use Example #1 explained in last page as the example. I have also included written instructions below for your reference.
Video #1 - Introduction to lighting worksheet and setting up 'Specification sheet"
In this video, I will introduce you to lighting worksheet and how to set up specification sheet. You can download lighting worksheet under "Learning Module 1".
- The lighting worksheet contains three sheets, first sheet is called "Specifications", the second sheet is called "Financial Comparison" and the last sheet is called "Environmental Impact Comparison", for the time being, let's ignore the last sheet. The bottom tabs in EXCEL allows you to switch to any sheet you want by clicking on it.
- In the "Specifications" sheet, we need to first enter case-specific information including company name such as "Wilderness Resort"; process such as "Outdoor Security Lighting Upgrade"; Date of data - "2015";
Under the title "Operation", we need to enter case-specific information such as number of fixture, hours on per day, days on per year, electricity price in $/kWh, etc; Note not every cell needs to be filled with information. The worksheet is meant to be a general form and one can certainly modify it to case-specific conditions. In addition, it is important to enter only "numbers" in the last column, text on units should be entered in the column under heading "Units".
Under the title 'Electrical Devices", we need to enter case-specific information for existing devices (such as CFL bulbs) and proposed devices (such as LED bulbs) including: bulbs per fixture; watts per bulb, etc;
For the time being, ignore the information under 'Environmental Impact".
There are some calculations that may need to be done under the "Specification" sheet. For example, "hours on per year" because this information will be needed when we calculate electricity consumption. We can use the formula function in EXCEL to do this. We know that "hours on per year" = "hours on per day" x "days on per year", we want EXCEL to do the calculation for us using Formula function, to do this:
(a) Click on the cell where the number for "hours on per year" should locate, in this case, cell "D12",
(b) then type in "="sign, click on the cell where data for "hours on per day" locates, in this case, cell "D10", notice, you should see "=D10" under formula bar as shown below:
(c) Then type in " * " for multiplication and click on the cell where data on "days on per year" locates which is "D11". You should see "=D10*D11" under formular bar and cell D12, as shown below:
(d) Hit 'Enter" key! Number "2920" shows up in cell D12 because 8 x 365 = 2920. EXCEL did the calculation for us! If we click on cell D12 now, we will see "=D10*D11" in formula bar as shown below. This tells us we have successful entered a formula of "D12=D10*D11" (which means "Data in D12" is calculated from "data in D10" multiply by "data in D11"). It is very important to only enter numbers (no text, no units) in the cells where calculation might be performed! EXCEL will treat cells with any letters/text as text and calculation using formula function can't work with text.
Video #2 - 'Specification sheet" continued (Another example of using EXCEL formula function)
In this video, we will continue working on the specification sheet. Another example of using formula function in EXCEL will be illustrated here.
Another calculation needed under the "Specification" sheet is the "Replacement bulbs per year per fixture". We learned that "replacement bulbs per year per fixture" = "Hours on per year"/"life of bulb" x "bulbs/fixture". Again let EXCEL do the calculation for us using Formula! Using "replacement bulbs per year per fixture" of CFL as an example,
(a) First, click on the cell where the number for "replacement bulbs per year per fixture" should locate, in this case, cell "D24",
(b) Then type in "="sign, click on the cell where data for "hours on per year" locates, in this case, cell "D12",
(c) Then type in"/" sign for division, click on the cell where data for "life of bulb" locates, in this case, cell "D23",
(d) Then type in "*" sign for multiplication, click on the cell where data for "bulbs/fixture" locates, in this case, cell "D19",
(e) Hit "Enter" key! Number "1.95" shows up in cell D24 because 2920/3000 x 2 = 1.95. EXCEL did the calculation for you! If we click on "1.95/cell D24" now, under the formula, we will see the equation/formula used to derive the number of 1.95 in cell D24 as shown below. This tells us we have successful entered a formula of "D24=D12/D23*D19" (which means that "data in D24" is calculated from "data in D12" divided by "data in D23" multiply "data in D19").
An Important Note on "Specification Sheet"
As a general rule, every piece of information that is used in the financial comparison should be found or derived from the specification sheet. If certain information was not included in the original Lighting Worksheet provided, we need to make sure we add that piece of information in the specification sheet. For example, if we propose to switch from CFLs to LEDs, there is going to be additional disposal cost associated with CFLs, so we need to add disposal cost of CFLs under the specification sheet.
Footnotes for specification sheet generally are not required. We may choose to add footnotes for our own reference for certain numbers if we think later on we may forget how those numbers are derived.