ZeePedia

SPREADSHEETS

<< DATA TYPES & OPERATORS
FLOW CONTROL & LOOPS >>
img
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 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
img
Introduction to Computing ­ CS101
VU
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
img
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
img
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 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
img
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
img
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 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
img
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:
  1. INTRODUCTION
  2. EVOLUTION OF COMPUTING
  3. World Wide Web, Web’s structure, genesis, its evolution
  4. Types of Computers, Components, Parts of Computers
  5. List of Parts of Computers
  6. Develop your Personal Web Page: HTML
  7. Microprocessor, Bus interface unit, Data & instruction cache memory, ALU
  8. Number systems, binary numbers, NOT, AND, OR and XOR logic operations
  9. structure of HTML tags, types of lists in web development
  10. COMPUTER SOFTWARE: Operating Systems, Device Drivers, Trialware
  11. Operating System: functions, components, types of operating systems
  12. Forms on Web pages, Components of Forms, building interactive Forms
  13. APPLICATION SOFTWARE: Scientific, engineering, graphics, Business, Productivity, Entertainment, Educational Software
  14. WORD PROCESSING: Common functions of word processors, desktop publishing
  15. Interactivity to Forms, JavaScript, server-side scripts
  16. ALGORITHMS
  17. ALGORITHMS: Pseudo code, Flowcharts
  18. JavaScript and client-side scripting, objects in JavaScript
  19. Low, High-Level, interpreted, compiled, structured & object-oriented programming languages
  20. Software Design and Development Methodologies
  21. DATA TYPES & OPERATORS
  22. SPREADSHEETS
  23. FLOW CONTROL & LOOPS
  24. DESIGN HEURISTICS. Rule of thumb learned through trial & error
  25. WEB DESIGN FOR USABILITY
  26. ARRAYS
  27. COMPUTER NETWORKS: types of networks, networking topologies and protocols
  28. THE INTERNET
  29. Variables: Local and Global Variables
  30. Internet Services: FTP, Telnet, Web, eMail, Instant messaging, VoIP
  31. DEVELOPING PRESENTATIONS: Effective Multimedia Presentations
  32. Event Handlers
  33. GRAPHICS & ANIMATION
  34. INTELLIGENT SYSTEMS: techniques for designing Artificial Intelligent Systems
  35. Mathematical Functions in JavaScript
  36. DATA MANAGEMENT
  37. DATABASE SOFTWARE: Data Security, Data Integrity, Integrity, Accessibility, DBMS
  38. String Manipulations:
  39. CYBER CRIME
  40. Social Implications of Computing
  41. IMAGES & ANIMATION
  42. THE COMPUTING PROFESSION
  43. THE FUTURE OF COMPUTING
  44. PROGRAMMING METHODOLOGY
  45. REVIEW & WRAP-UP of Introduction to Computing