Spreadsheet Tips

From Appropedia
Jump to navigation Jump to search

QASlogo.png This page was developed by the Queen's University Applied Sustainability Research Group. QASlogo.png


Spreadsheets are often used for financial,economic or environmental analysis. These tips are especially helpful if used to make open source calculators as in the Queens Green IT Project.

Microsoft Excel 2007[edit | edit source]

In general, ensure that formatting is logical and professional.

  • Use a colour scheme that can be printed with equal legibility in either full colour or black and white.
  • Spell check the document by clicking 'Review' in the tab, and 'Check spelling' in the ribbon.
  • Avoid including calculations on the 'Inputs' sheet whenever possible. These can be complicated and intimidating for a user unfamiliar with Excel.
  • Link relevant cells as much as possible; avoid redundancies by linking to cells on different worksheet where possible as well to reduce the number of variables that appear in each calculation.

Other General Resources:
Financial Modelling in Microsoft Excel
Excel Basics
Excel Help Forum

Printing[edit | edit source]

Spreadsheets are usually viewed on-screen, as this allows them to be manipulated while taking full advantage of their capabilities. If you do choose to print the document, a few things that you should consider include are listed below. Note that these features will have to be set for each sheet in a workbook.

Print preview[edit | edit source]

To view the document as it will print, complete with headers and footers, appropriate scaling, defined print areas, etc., click the Windows button in the top left corner of the Excel window, click 'Print', then 'Print preview'. Scroll between printed pages to view the entire document.

Paper size[edit | edit source]

Try to organize the document in a way that it can be printed reasonably on legal or letter paper (or similar). This allows easy storage of the document, as well as simple presentation of the results in a binder presentation or similar. To select the size of paper the sheet will be printed on, go to the 'Page layout' tab, click 'Size' and select the dimensions to which you wish to print.

Setting print areas[edit | edit source]

To set the print area for your document, select the portion of the document you would like to print in one rectangle/square selection, dragging from top left corner to bottom right corner of your selection area. Click the 'Page layout' tab, and select 'Print area' > 'Set print area'. The print area is now set for this sheet based on your selection. To adjust the print area, click the 'View' tab, and click 'Page break preview' on the left side of the ribbon. Move the blue lines to incorporate as much of the content as you would like to print (solid lines), taking note of page breaks (dotted lines) where information will be split over two or more pages. To remove a page break, click and drag the dotted line to the solid line at the extent of the page.

Scaling the document on the printed page[edit | edit source]

Once print areas are defined, the document will need to be scaled to fit a page, similar to zooming in/out to see the information on a screen. To check scaling, click 'Print preview' under 'Print' in the Windows button (top left corner of the open Excel window). To adjust scaling, click the 'Page layout' tab, and adjust 'Scale' using the up/down arrows, or by entering a scaling factor as a percentage. Confirm the scaling factor was correct by checking the print preview again.

Adding headers and footers[edit | edit source]

Click the 'View' tab, and select 'Page layout' to view what the page will look like when printed. Note that this view does not necessarily account for the limits set on print area for the document. Click in the header and/or footer space on the page to add content, noting that both the header and footer are divided into three regions - left, middle and right. Text that automatically updates can be added to the document, such as page numbers, file names or date and time signatures, with the particular options becoming available when an editable region of the header or footer is selected.

  • If including an automatically-updating date, be sure to define it as the 'Last saved: dd/mm/yy', 'Printed: dd/mm/yy', or similar, rather than simply 'dd/mm/yy' which can be ambiguous.
  • If including a page number, consider defining it as 'Page &[page] of &[pages]' so it reads 'Page 1 of 3' rather than simply '1'
  • If including a file name, be sure that it is something meaningful to the reader by including a descriptive title, and perhaps a version code, in the file name.

Functions[edit | edit source]

Calculating internal rate of return (IRR)[edit | edit source]

Internal rate of return (IRR) is a financial metric that can be used to determine the economic viability of a project, or the relative economic strength of two or more competing options. It is the rate at which all annual net cash flows must be discounted to yield a net present value of $0. When calculating its value, be sure to define the acronym (IRR), as not everyone is familiar with this abbreviation. An example calculation is included here.

1. Determine the total annual cash outflow (investment) for years 0 (baseline) through 5 in cells G95:L95.

2. Determine the total annual cash inflow (savings) for years 0 (baseline) through 5 in cells G96:L96.

3. Determine the total annual net cash flow (inflow - outflow) for years 0 (baseline) through 5 in cells G97:L97.

4. Place the text 'Internal rate of return (IRR)' in cell G104.

5. Use the built-in IRR calculator, by entering '=IRR(G97:L97,x)' into the destination cell, where G97:L97 is the range of annual net cash flows, and x is an initial guess at the value of the IRR in decimal form; typically an estimate of x = 0.1 is reasonable.

Calculating net preset value (NPV)[edit | edit source]

Net present value (NPV) is a financial metric that can be used to determine the economic viability of a project, or the relative economic strength of two or more competing options. It is the net value of the option at the end of its life or study period (in the case of the ECM analyses, this period ends in year 5), as discounted by a rate which accounts for the opportunity cost of choosing to invest in one particular option. For the ECM analyses, these rates are determined from the risk-free rate (RFR) of return provided by the Canadian government via Treasury bills ('T-bills'). An example calculation is included here:

1. Determine the annual net cash flow (inflow - outflow) for years 1 through 5 in cells G97:L97 (see above for more information).

2. Divide the annual net cash flow for year 0 (baseline year) by (1 + i_0) where i_0 is the RFR for year 0 (typically 0%).

3. Divide the annual net cash flow for year 1 by {(1 + i_1)*(1 + i_0)} where i_1 is the RFR for year 1.

4. Divide the annual net cash flow for year 2 by {(1 + i_2)*(1 + i_1)*(1 + i_0)} where i_2 is the RFR for year 2.

5. Divide the annual net cash flow for year 3 by {(1 + i_3)*(1 + i_2)*(1 + i_1)*(1 + i_0)} where i_3 is the RFR for year 3.

6. Divide the annual net cash flow for year 4 by {(1 + i_4)*(1 + i_3)*(1 + 1_2)*(1 + i_1)*(1 + i_0)} where i_4 is the RFR for year 4.

7. Divide the annual net cash flow for year 5 by {(1 + i_5)*(1 + i_4)*(1 + i_3)*(1 + 1_2)*(1 + i_1)*(1 + 1_0)} where i_5 is the RFR for year 5.

8. The sum of the discounted annual net cash flows for years 0-5 is the net present value (NPV) of the project.

Payback period[edit | edit source]

The payback period is the length of time, typically expressed in years for larger projects, it will take for the project to 'break even' -- to save the company as much money as it cost to implement a new practice or technology, for example. Assuming payback to be linear -- that is, the same savings is realized each year after the initial investment -- the payback period can be determined by dividing the absolute values of the initial investment (cash outflow in year 0) by the annual net cast flow (net cash flow for year 1, which is equal to that of year 2, 3, etc. in a linear payback model) for that investment. The absolute value portion of the calculation takes the positive/negative sign of different cash flows into account, to report a final number of 2.3 years, for example (rather than -2.3 years, which has the same effective meaning, but does not appear so). An example calculation looks like this:

1. Report the total cash outflow for year 0 (baseline year) in cell G95.

2. Report the annual net cash flow for year 1 (first year of operation, and thus savings) in cell H97.

3. In cell G105, calculate the payback period by entering '=abs(G95/H97)'.

Hyperlinking[edit | edit source]

Hyperlinking is a great way to direct a user to a specific portion of the document in which they are working. It is great for referring a user to a reference, source, assumption, disclaimer, or next step.

To link to a sheet in the current document, select the cell(s) which you would like to direct the user, click the 'Insert' tab then click 'Hyperlink'. Select 'Place in this document' on the left side, and select the sheet you would like to link to.

To link to a particular cell in the current document (on any page), the destination cell must be named. Select the destination cell, and enter a name for that cell in the Name Box, at the top of the document window, below the ribbon, and to the left of the formula bar. Be sure to pick a descriptive name unique to that cell -- each cell must have a unique name. Now, the link is ready to be inserted. Click the cell from which you will link to the destination cell, click the 'Insert' tab then click 'Hyperlink' and select 'Place in this document'. Scroll the list until you find the name of your destination cell, select the name and click 'Ok.' To organize or change the name of a cell, click the 'Formulas' tab, and select 'Name manager' from the ribbon.

To link to an external webpage, click the cell you want to link from, select 'Insert' then 'Hyperlink' from the ribbon. Chose 'Existing file or webpage' in the window that opens and enter the URL for the destination.

Microsoft Excel 2000/2003[edit | edit source]

Open Office[edit | edit source]