SPREADSHEETS

<< DATA TYPES & OPERATORS

FLOW CONTROL & LOOPS >>

Introduction to Computing CS101

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

Year of Operation

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%

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 financial-forecasting 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

Can perform calculations repeatedly, accurately, rapidly

Can handle a large number of parameters, variables

Make it easy to analyze what-if 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

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

60.0%

Bar charts work well for

comparing several discrete

data categories with one

40.0%

another or showing a trend

over several time

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 break-even price of products. You would tell the

computer to change price of products until Profit was zero (break-even), 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

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

144

Introduction to Computing CS101

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.

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 On-Screen Help

All spreadsheets generally have some form of built-in help mechanism

To me, it seems like that many of those help-systems are designed to be "not-very-helpful": 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

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: