Spreadsheets

by Admin
Updated: May 16, 2019

A quick and easy introduction to spreadsheets which are extremely useful in financial applications

Spreadsheets in their most basic form show numerical data in a grid where each cell can contain either data (such as a number or text) or some kind of calculation (in which case the result, as a number or text, is displayed). They are extremely useful in financial applications, particularly what if scenarios where laborious calculations would otherwise have to be repeated multiple times.

“Spread-sheets” were originally large sheets of paper spread on a desk providing space to write all the numbers involved in a lengthy calculation. The first computerized spreadsheet program was VisiCalc on the Apple II in 1979. It overcame the limitations of small low-resolution displays with the computer’s ability to scroll.

Sandbox

Here is a simple spreadsheet you can play with. Basic functionality is obvious, e.g. =a2+b2 and more complicated functionality can be achieved using Javascript expressions, e.g. 32 is achieved using =Math.pow(3,2) instead of =3^2.

This sandbox spreadsheet uses localStorage to save your work, i.e. it won’t be lost if you navigate away from the page but no cookies are used and nothing is sent from your browser.

Example

Type or load the contents of the table below into the sandbox spreadsheet above for a simple working example.

ABCD
1170
2Taxi-20=c1+b2=c2>0?"ok":"Warning!"
3Dinner=-60*2=c2+b3=c3>0?"ok":"Warning!"
4Movie=-9*2=c3+b4=c4>0?"ok":"Warning!"
5Taxi-20=c4+b5=c5>0?"ok":"Warning!"

This example uses data & formulae to plan a nice evening out, starting with a fixed amount of cash and showing a warning if the cash runs out.

Column A contains only text. Column B contains numbers and numerical calculations. Column C contains an algebraic formula for updating the sum of cash + expenditure. Column D contains some if-then-else logic written in Javascript because that’s what the sandbox spreadsheet uses.

Note that =IF(C2>0,"Ok","Warning!") is what normal spreadsheets would typically need in Column D.

Tips & tricks

The ENTER key: Most spreadsheets will by default move the focus down a cell when you press the Enter key. If (like me) you find this annoying, you can usually disable it. For example, in LibreOffice, Tools » Options » LibreOffice Calc » General » Press Enter to move selection = unchecked.

Offset: If you’re using a running total type of formula like the one in the example and you change the order of the rows (cut, insert & paste), your formulas will get broken. Most normal spreadsheet programs allow the use of offset cell references which don’t get broken, e.g. =c1+b2 in cell c2 becomes =OFFSET(c2,-1,)+b2.

Bad practices

Spreadsheets have many built-in functions. It is bad practice to use functions you don’t fully understand.

Spreadsheets are tools. Sending them out to staff as reports, to customers as order forms etc. is bad practice.

Spreadsheets can be very powerful, but sharing them outside your workgroup and still requiring everyone to have the latest version of the software you use is bad practice. If you must do this, keep the sheets as simple as possible and use Open Document ods format.

More info

“A Spreadsheet Way of Knowledge” at medium.com

The following are links to popular spreadsheet programs you can use online and/or install on your device:

Google Sheets Microsoft Excel Online Apache OpenOffice LibreOffice

Internal links

Cash flow Charting for fun and profit Sharing to get things for free Humor Go to Articles
Agree? Disagree? Questions? Answers?
Please post a comment...
Log in