Video #3 - "Financial Comparison" sheet (I)
In this video, we will work on financial comparison sheet.
After completing the specification sheet, we are ready to do the financial comparison between existing system (i.e., CFL bulbs) and proposed system (i.e. LED bulbs), listed under the row headings. In terms of COST CATEGORIES, it includes CAPITAL COSTS and OPERATING COST (annual). Let's work on capital cost as an example:
Notice, capital cost for "Existing system" is always "Zero" since existing system is already in place (remember capital cost is the initial cost of putting in a system)
Capital costs for a proposed system include "Equipment", "Delivery and Installation", and "Other". In the case of Lighting Example 1, to upgrade to proposed LEDs, cost of LED bulbs would be equipment cost. LED bulbs need to be installed, so that cost should be listed under "Delivery and installation". If there is any other cost associated, we should put it under "Other". In previous page, we have explained that cost of purchasing bulbs is "number of fixtures (280) x bulbs/fixture (2) x cost per bulb ($13). Again we would like to use the "Formula" function in EXCEL to do the calculation. How? just like we did before with the specification sheet!
(a) First, click on the cell where the number for "Equipment cost of proposed system" should locate, in this case, cell "D6",
(b) Then type in "="sign, click on the cell where data for "number of fixtures" locates, in this case, it is under "Specification Sheet", so we click on "specification sheet", it is in cell "D8", so we click on cell "D8"
(c) Then type in"*" sign, click on the cell where data for "bulbs per fixture" of proposed system locates, in this case, cell "D27",
(d) Then type in "*" sign, click on the cell where data for "cost per bulb" locates, in this case, cell "D31", You should see "=Specification!D8*Specification!D27*Specification!D30" under formula bar. Notice it tells us which sheet each of the cell belongs to.
(e) Hit "Enter" key! Number "$7,280" shows up in cell D6 of the "Financial comparison" sheet because 280 x 2 x $13 = $7,280. EXCEL did the calculation for us even when data are in different sheets! If we click on cell D6 now, we will see "=Specifications!D8*Specifications!D28*Specifications!D31" in formula bar as shown below. This tells us we have successful entered a formula of "D6=Specifications!D8*Specifications!D28*Specifications!D31" (which means that "Data in D6" on 'Financial Comparison" sheet is calculated from "data in specifications sheet D8" times "data in specifications sheet D28" times "data in specifications sheet D31").
(f) One great advantage of using EXCEL formula function for calculation is that when there are changes in the specifications sheet, as long as the formula entered still holds true, the numbers on the financial comparison sheet will automatically be updated. This is a great feature since many data on the specification sheet are subject to change including price for electricity (or energy in general), price for labor and equipment, etc.
Video #4 -"Financial Comparison" sheet (II): Adding footnotes, textbox, add/delete a row/column, increase/decrease decimal places"
In this video we will work on adding footnotes and discuss a few details on EXCEL (including adding textbox, adding/deleting a row/column, increasing/decreasing decimal places showing up)
To make our presentation professional and also serve as a note to ourselves, we need to add footnotes. In another word, we need to tell the reader how this "$7,280" came about. To do this, we just need to:
(i) type in a letter for example "a" next to the number;
(ii) under the text box entitled "Footnotes", type in how we calculated "7,280" such as "a - 280 fixtures x 2 bulbs/fixture x $13/bulbs = $7,280".
Then, we should use the same approach to complete rest of the capital cost calculation and annual operating cost calculation with footnotes included for each number.
Now, let's talk about a few more details on EXCEL that you may have questions about:
1) How do I change decimal places in EXCEL? In EXCEL, calculation using formula carries all decimal places at all time (so it is very accurate), but we can choose how many decimal places to show up. To do this, under "Home" tab, go to the number tab on the top of the screen, on the right side, click on ".00-->.0" to show fewer decimal places and click on ".0 -->.00" to show more decimal places".
2) How do I add textbox/footnotes in EXCEL? Under "Insert", click on "Textbox", the dropdown menu allows you to choose either "horizontal textbox" or "vertical textbox", click on the choice you desire, then draw on the screen with your mouse a rectangular shaped textbox, type inside the textbox. You can also move the textbox to where you want it to be by dragging and moving on the edge of the textbox.
3) How do I add/delete a row/column in EXCEL? To add/delete a row, move your mouse to where you want the additional row to be or where the row to be deleted is, click on the very left side of the row till you see the cursor becomes an little arrow pointing to right, left click on your mouse, this will highlight an entire row, right click to show the dropdown menu. Choose "insert" to add a row; choose "delete" to delete a row. To add/delete a column, move your mouse to where you want the additional column to be or where the row to be deleted is, click on the very top of the column till you see the cursor becomes an little arrow pointing down, left click on your mouse, this will highlight an entire column, right click to show the dropdown menu. Choose "insert" to add a column; or "delete" to delete a column. Note that adding/deleting a row or column in the above way will not affect the formula that connects data on the "Specifications" sheet with the "Financial Comparison" sheet. EXCEL automatically updates the new cell numbers in the formula (How Smart!)
4) How do I add a border to a cell/table in EXCEL? First highlight the cell or tables where you want the border to be around (and/or within), under "Home", under "font" tab, click on the little triangle next to a square, a dropdown menu will show up and you can pick the type of "border" you desire.
Completed Capital Costs and Operating Costs of Example #1 is shown below.