

Introduction
to Computing CS101
VU
LESSON
22
SPREADSHEETS
Today's
Lecture:
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
The
information provided in this
business plan is confidential.
Please do not disclose it
without
checking
with me first.
Thanks.
Sales
Forecast
50
40
30
20
10
0
1
2
3
4
5
Year of
Operation
34
All
currency figures are in
thousands of US Dollars
5th
Year
1st
Year
2nd
Year
3rd
Year
4th
Year
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
3,330
9,910
23,575
45,900
420
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
12,882
23,220
Total
2,208
5,935
504
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
840
2,610
4,670
6,750
9,225
Costs
for the Corporate
Office
Corporate
40x3
120
42x4
168
44x6
264
46x8
368
48x10
480
120
264
368
480
Total
168
(1,044)
Profit
(1,656)
(959)
3,575
12,975
249%
50%
10%
15%
P/S
28%
NPV Discount
Rate
17%
40
SpreaNshe@ s
hat Discount
Rate
dPV et
t
5,125
IRR
68%
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)
140
Introduction
to Computing CS101
VU
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
22.2
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
=A1 +
4
Connecting
Two Cells
And
this
Let's
call
one, A2
this cell
A1
Contents of
the
current
cell are
displayed
here
This is
the
current
cell
The
address of the
current
cell is
displayed as a
letter(column)number(row)
pair
141
Introduction
to Computing CS101
VU
All currency figures are in
thousands of US Dollars
1st
Year
2nd
Year
3rd
Year
4th
Year
5th
Year
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
420
3,330
9,910
23,575
45,900
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
504
2,208
5,935
12,882
23,220
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
840
2,610
4,670
6,750
9,225
Costs for the
Corporate Office
Corporate
40x3
120
42x4
168
44x6
264
46x8
368
48x10
480
Total
120
168
264
368
480
Profit
(1,044)
(1,656)
(959)
3,575
12,975
P/S
249%
50%
10%
15%
28%
17%
NPV Discount
Rate
5,125
NPV @ that Discount
Rate
IRR
68%
Distribution of
Expenses Required for
Running a Call Center in the
US
Software
3.6%
Hardware
9.2%
Telecom
Charges
9.3%
Salary &
Benefits
57.2%
Recruitment &
Training
4.6%
Building
Rent
4.4%
Other
11.7%
100.0%
142
Introduction
to Computing CS101
VU
60.0%
Bar
charts work well
for
comparing several
discrete
data
categories with
one
40.0%
another or
showing a trend
over several
time
i
t
20.0%
0.0%
60.0%
40.0%
20.0%
Line
charts are also work
well
for
displaying data trends
over
time.
They're better than
bar
0.0%
charts if
there are a large
number of
data points or if
more
than one
congruent
Pie charts
are
great
for
showing
parts
of a whole
that
are
generally
expressed
in
percentages.
They
work
best
for a
22.3
Goal Seek
small
number
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
Bhola
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 Add
ins
(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
143
Introduction
to Computing CS101
VU
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
144
Introduction
to Computing CS101
VU
·
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
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
145
Introduction
to Computing CS101
VU
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
146
Table of Contents:

