*Tables and Images for these lectures are not available yet.
Spreadsheets
It was the first among the four
lectures that we plan to have on productivity software
We learnt about what we mean by word processing and also desktop
publishing
We also discussed the usage of various functions provided by common
Second among the four lectures that we plan to have on productivity
software
This 2nd Lesson is on spreadsheets
We’ll learn about why we are interested in spreadsheets
We’ll discuss the several common functions provided by popular
spreadsheet SW programs
22.1 Business Plan for a New Software Development Company
Spreadsheets
Electronic replacement for ledgers
Used for automating engineering, scientific, but in majority of cases,
business calculations
A spreadsheet  VisiCalc  was the first popular application on PC’s.
It helped in popularizing PC’s by making the task of
financialforecasting much simpler, allowing
individuals to do forecasts which previously were performed by a whole
team of financial wizard
What Can They Do? (1)
Billing Schedule
Lahore 20x42x0.5 420 30x96 2,880
40x169 6,760 50x317 15,850 60x490 29,400
Dubai 60x15x0.5 450 70x35 2,450 80x45 3,600 90x50 4,500
Islamabad 40x25x0.5 700 50x60 3,000 60x100 6,000
Karachi 50x45x0.5 1,125 60x100 6,000
Total
Costs for the Development Workforce
Lahore 15x42x0.8 504 17x96 1,632
20x169 3,380 24x315 7,608 28x490 13,720
Dubai 48x15x0.8 576 57x35 1,995 66x45 2,970 78x50 3,900
Islamabad 20x35x0.8 560 24x60 1,440 28x100 2,800
Karachi 24x45x0.8 864 28x100 2,800
Total
Costs for the Sales and Support Workforce
Singapore 120x2 240 110x3 390 110x4
440 110x5 550 125x5 625
Wash., DC 200x3 600 180x10 1,800 180x20 3,600 180x30 5,400 190x40 7,600
Chicago 210x2 420 200x3 630 200x4 800 200x5 1,000
Total
Costs for the Corporate Office
Corporate 40x3 120 42x4 168 44x6 264
46x8 368 48x10 480
Total
Profit
P/S
NPV Discount Rate
NPV @ that Discount Rate
IRR
17%
5,125
68%
28%
420 3,330
12,882 23,220
9,910 23,575 45,900
120 168 264 368 480
249% 50% 10% 15%
5th Year
(1,044) (1,656) (959) 3,575 12,975
1st Year
840
2nd Year 3rd Year 4th Year
2,610 4,670 6,750 9,225
504 2,208 5,935
4
Can perform calculations
repeatedly, accurately, rapidly
Can handle a large number of parameters, variables
Make it easy to analyze whatif scenarios for determining changes in
forecasts w.r.t. change in
parameters
What Can They Do? (2)
Are easy to interface with other
productivity SW packages
Easy to store, recall, modify
Make it is easy to produce graphs:
Graphs reveal the knowledge contained in data with greater clarity and
ease as compared with data
arranged in rows and columns
Modern spreadsheet programs can be used to display data in a variety of
graphical formats
The Structure of A Spreadsheet
Collection of cells arranged in
rows and columns
Each cell can contain one of the following:Numbers
Text
Formulas
These cells display either the number or text that was entered in them
or the value that is found by
executing the formula
Connecting Two Cells
=A1 + 4
All currency figures are in thousands
of US Dollars
Billing Schedule
Lahore 20x42x0.5 420 30x96 2,880
40x169 6,760 50x317 15,850 60x490 29,400
Dubai 60x15x0.5 450 70x35 2,450 80x45 3,600 90x50 4,500
Islamabad 40x25x0.5 700 50x60 3,000 60x100 6,000
Karachi 50x45x0.5 1,125 60x100 6,000
Total
Costs for the Development Workforce
Lahore 15x42x0.8 504 17x96 1,632
20x169 3,380 24x315 7,608 28x490 13,720
Dubai 48x15x0.8 576 57x35 1,995 66x45 2,970 78x50 3,900
Islamabad 20x35x0.8 560 24x60 1,440 28x100 2,800
Karachi 24x45x0.8 864 28x100 2,800
Total
Costs for the Sales and Support Workforce
Singapore 120x2 240 110x3 390 110x4
440 110x5 550 125x5 625
Wash., DC 200x3 600 180x10 1,800 180x20 3,600 180x30 5,400 190x40 7,600
Chicago 210x2 420 200x3 630 200x4 800 200x5 1,000
Total
Costs for the Corporate Office
Corporate 40x3 120 42x4 168 44x6 264
46x8 368 48x10 480
Total
Profit
P/S
NPV Discount Rate
NPV @ that Discount Rate
IRR
1st Year
840
2nd Year 3rd Year 4th Year
2,610 4,670 6,750
504 2,208
249% 50% 10% 15%
5th Year
(1,044) (1,656) (959) 3,575 12,975
45,900
120 168 264 368 480
9,225
5,935
17%
5,125
68%
28%
420 3,330
12,882 23,220
9,910 23,575
Distribution of Expenses Required for
Running a Call Center in the US
100.0%
Other 11.7%
Building Rent 4.4%
Recruitment & Training 4.6%
Salary & Benefits 57.2%
Telecom Charges 9.3%
Hardware 9.2%
Software 3.6%
Goal Seek
Goal Seek in Excel
When you use the Goal Seek
command, Excel changes the value in one cell until the value in a second
cell reaches a number that you desire. For instance, if you had a
spreadsheet that calculated profit for the
Kim eService from a variety of inputs, including employee numbers,
expenses, products sold, price of
products, you might use goal seek to define your breakeven price of
products. You would tell the
computer to change price of products until Profit was zero (breakeven),
and you would do that using
Tools, Goal Seek.
To use Goal Seek, go to the Tools command. If Goal seek . . . is not an
option, you must first go to Addins
(also under Tools), and select Goal Seek. Once Goal Seek is loaded,
choose it under Tools.
In Goal Seek there will be three boxes to fill in.
The first says "Set cell." Enter the cell address (or click on the cell)
of the cell whose value you want to
fix or set to a specific number (i.e. Profit cell). This cell must
contain a formula or function. Otherwise it
will not be linked to the cell you will be changing to obtain zero
profit.
The second says "To value." Enter the appropriate value you wish to see
in that "Set" cell (i.e. 0 if you
want the Profit to come out zero).
The third says "By changing cell." Enter or click on the cell you want
Goal Seek to change to obtain the
zero profit. (i.e. milk price). This cell must not be a formula or
function. Then click "okay."
At this point Goal Seek will show you the answer. For instance, Profit
will now be zero and the Milk
Price cell will have changed to another price (maybe 11.86) to make
Profit=0. You can accept the
change or you can cancel the Goal Seek and return to the previous
numbers. Often you just want to take
note of the new numbers and cancel. If you accept and change your mind,
click Undo.
Things that you must remember!!
Make sure the "Set Cell" cell is a
formula or function or cell reference.
Make sure you have set that sell to a reasonable number.
Make sure the "By Changing Cell" cell is a number or blank, and not a
formula, function or cell
reference like =C5.
Make sure there is a link by formulas between the two cells you entered
in the Goal Seek. However
complicated the link might be, they must be related for the Set cell to
be changed by the Change cell.
Finally, make sure your formula in the "Set Cell" cell is correct (as
well as all others).
Simple Example
0.0%
20.0%
40.0%
60.0%
0.0%
20.0%
40.0%
60.0%
Assume the following cells. We
will use Goal Seek to find a number to make the sum=150.
A2 = 25
A3 = 40
A4 = SUM(A2:A3) which is showing 65
In Goal Seek:
Set Cell: click on A4
To Value: enter 150
By Changing Cell: click on A3
The sum in A4 should now be 150, and A3 should have become 125 for that
to happen.
Solving Equation: f(x) = x2 + 2x + 1 =
0
•
Write the formula in a cell e.g. A2
•
Select the
goal seek
ti
Hence to get the value of the
given function as 0 the value of x should be 1
Which is the solution of the equation
f(x) = x2
+ 2x + 1 = 0
links
Following are some urls for the goal seek
;
http://www.oootraining.com/QwikAndDirty/QwikAndDirtyExcelWeb/
DataAnalysis/Using_Goal_Seek/
Using_Goal_Seek.htm
The Best Feature: Undo
Allows you to
recover from your mistakes
Allows you to experiment without risk
Getting OnScreen Help
All spreadsheets generally have some form of builtin help mechanism
To me, it seems like that many of those helpsystems are designed to be
“notveryhelpful”: they make
finding answers to simple questions quite difficult
Nevertheless, do try them when you are searching for answers
I’ll now demonstrate the use of spreadsheets with the help of several
examples
Formulas
Sorting
Conditional formatting
Graphs
Goal seek
•
In the ‘set cell’
input field write the
cell number that
needs to be
changed I.e. a2
•
In the ‘to value’
field enter the
value we want the
cell a2 to have i.e.
0
•
This shows the that the target was to have 0
value but
excel could calculate for 0.0004 value
•
On pressing Ok we will get>
•
Here the value of a1 is 0 97 which is almost
equal
Today’s Lesson was the …
Second among the four lectures
that we plan to have on productivity software
This 2nd Lesson was on spreadsheets
We learnt about what we mean by spreadsheets
We discussed the usage of various functions provided by common
spreadsheets
Focus of the Next Productivity SW Lecture: Presentations
To become familiar with the basics of multimedia presentations
To become able to develop simple presentation with the help of
presentation software
