Pear Company Sales - February 2019

Sales Person Week 1 Week 2 Week 3 Week 4 Totals

S. Sam $ 9,550 $ 9,230 $ 8,500 $ 8,965 $36,245
E. Brown $ 5,975 $ 6,900 $ 8,500 $ 10,100 $31,475
W. Dani $ 7,825 $ 8,580 $ 9,910 $ 7,512 $33,827
M. Mayer $ 9,560 $ 10,150 $ 11,200 $ 9,795 $40,705
S. Sandberg $ 8,800 $ 7,645 $ 9,250 $ 8,304 $33,999
A. John $ 7,892 $ 9,695 $ 9,520 $ 10,252 $37,359

Total Sales: $213,610

Average Sales: $8,900

Max Sales: $11,200

Min Sales: $5,975

Count of Sales: $24

 Monthly Goal: $34,000.00

Was Goal Met? Bonus Status

Yes $1,812
No No Bonus
No No Bonus
Yes $2,035
No No Bonus
Yes $1,868

Department Bonus of $850 per person

No Bonus This Time!

Department as a whole must have

total sales of at least $200,000
AND average $9000 or more per
week.
Formula Definition/Purpose

ROUND Rounds a number to specified decimal points

ROUNDUP It rounds a number up

ROUNDDOWN It rounds a number down

MROUND This formula rounds to nearest multiple of specified number.

SYNTAX What Does the SYNTAX Mean? Input Number

Where “number” is the number that you

ROUND(number, num_digits) want to round, and “num_digits” is the
number of digits to which you want to
round the number argument. 5.144523

Where “Number” is any real number that

ROUNDUP(number, num_digits) you want rounded up, and “Num_digits” is
the number of digits to which you want to
round number. 5.144523

Where “Number” is any real number that

ROUNDDOWN(number, num_digits) you want rounded down, and “Num_digits”
is the number of digits to which you want to
round number. 5.164523

Where “Number” is the value to round and

MROUND(number, multiple) “Multiple” is the multiple to which you want
to round the number. 1,255,050
Output Number TRY IT!

5.1 5.14

5.2 5.15

5.1 5.16

1,260,000 1300000
Number 1 2 Number 2

Result 4
2

Karim Faz:
Hey Check this out.. Type " CTRL + ` " on your
keyboards!!! Welcome to the "Show Formulas" view,
we can see all formulas on this sheet. This may be
overwhelming ill show you another trick in a second…
 " on your
ulas" view,
is may be
a second…
Result 4
Karim Faz:
Try hitting "F2" on your keyboard.

This shows the formula for the selected

cell ONLY. (the one with the green box)

*you may need to hold "fn" on your

keyboard if F2 alone does not work.*
 Karim Faz:
hitting "F2" on your keyboard.

ows the formula for the selected

LY. (the one with the green box)

may need to hold "fn" on your

ard if F2 alone does not work.*
 Employee Lookup
Emp ID Last Name First Name Dept Pay Rate
10000 Not in Table Not in Table Not in Table Not in Table

Employee Information - Master List

Emp ID Last Name First Name Dept E-mail Phone Ext Location Hire Date
1054 Smith Howard AT howards 148 Building 1 15-Apr-04
1056 Gonzales Joe AT joeg 121 Building 1 24-Oct-92
1067 Scote Gail AT gails 123 Building 1 19-Sep-00
1075 Kane Sheryl AD sherylk 126 Building 2 07-Aug-05
1078 Hapsbuch Kendrick AC kendrickh 101 Building 2 01-Apr-99
1152 Henders Mark AD markh 118 Building 2 21-Jan-03
1196 Atherton Katie HR katiea 289 Building 3 01-Apr-11
1284 Bellwood Frank MK frankb 124 Building 1 04-Jan-98
1290 Cooper Linda AD lindac 113 Building 2 03-Jan-98
1293 Cronwith Brent HR brentc 205 Building 3 14-Sep-97
1299 Simpson Sandrae MF sandraes 127 Building 1 21-Dec-02
1302 Smith Randy MK randys 139 Building 1 06-Aug-97
1310 Smith Ellen MF ellens 137 Building 1 04-Oct-99
1329 Vuanuo Tuome AC tuomev 151 Building 2 22-Feb-02
1333 Szcznyck Tadeuz HR tadeuzs 122 Building 3 16-Apr-03
1368 Wu Tammy AD tammyw 132 Building 2 10-Mar-96
1509 Miller Pam AT pamk 135 Building 1 19-Jun-98
1516 Smith Tom AC tomb 105 Building 2 06-Mar-98
1529 Kellerman Tommie MK tomk 129 Building 1 28-Jan-00
1656 Kourios Theo MF theok 149 Building 1 13-Dec-00
1672 Dixon-Waite Sherrie MF sherried 114 Building 1 16-Apr-03
1673 Boughton Frank AD fboughton 112 Building 2 25-Mar-05
1676 Miller Janet MK janetm 115 Building 1 26-Oct-94
1721 Alstain Isolde HR isoldea 102 Building 3 06-Aug-03
1723 Sammler Mark MK marks 145 Building 1 10-Feb-91
1758 Brwyne Melia AC meliab 107 Building 2 18-Mar-95
1792 Barton Eileen AT eileenb 111 Building 1 24-Dec-03
1814 Al-Sabah Daoud HR daouda 103 Building 3 04-Mar-02
1908 Zostoc Melissa AT melissaz 152 Building 1 15-May-97
1931 Miller Ursula AC ursulam 110 Building 2 20-Jun-02
1960 Fontaine Jean MF jeanf 150 Building 1 13-Nov-99
1964 Corwick Rob AC robertc 108 Building 2 16-Nov-04
1975 Smith Larry AC larryf 125 Building 2 01-Mar-09
1983 Corwick Judy AT judyc 154 Building 1 28-Jun-10
1990 Chang Jessica MF jessc 198 Building 1 14-Feb-11
1995 Miller Maria AT mariam 198 Building 1 01-Mar-11
2006 Atherly Katherine HR kathya 428 Building 3 05-Jul-11
 Vlookup Rules
lookup_value: Must be the leftmost column
Table_array: Simple 2 or more columns of data
Col-index-num: it’s the placement of the column in the table_array ( 2- Last Na
[range_lookup]: True == Approx Match, FALSE == 0 = EXACT MATCH

Pay Rate
$ 11.25
$ 12.25
$ 14.55
$ 11.25
$ 10.20
$ 12.25
$ 9.95
$ 12.30
$ 13.25
$ 10.20
$ 12.20
$ 14.25
$ 11.50
$ 10.35
$ 10.15
$ 12.25
$ 13.25
$ 9.50
$ 11.30
$ 12.35
$ 11.90
$ 11.85
$ 10.75
$ 9.75
$ 13.95 s
$ 11.20
$ 10.30
$ 12.25
$ 10.25
$ 9.85
$ 11.65
$ 9.25
$ 9.25
$ 11.00
$ 10.95
$ 11.75
$ 10.15
mn in the table_array ( 2- Last Name)
E == 0 = EXACT MATCH
 Database Functions

Pear Company - Q1 Expenses

Division Category January February March Total Expenses
East Technical Support $ 800.00 $ 650.00 $ 700.00 $ 2,150.00
East Telephone $ 900.00 $ 850.00 $ 850.00 $ 2,600.00
East Copying $ 4,850.00 $ 3,200.00 $ 1,155.00 $ 9,205.00
East Overhead $ 1,250.00 $ 1,250.00 $ 1,250.00 $ 3,750.00
East Software $ 2,025.00 $ 2,200.00 $ 1,650.00 $ 5,875.00
East Maintenance $ 1,350.00 $ 1,500.00 $ 1,700.00 $ 4,550.00
East Supplies $ 3,300.00 $ 3,500.00 $ 3,700.00 $ 10,500.00
East Telemarketing $ 3,825.00 $ 3,725.00 $ 3,750.00 $ 11,300.00
East Contractors $ 8,900.00 $ 10,315.00 $ 5,250.00 $ 24,465.00
East Consultants $ 6,250.00 $ 6,000.00 $ 6,500.00 $ 18,750.00
East Rent $ 8,000.00 $ 8,000.00 $ 8,000.00 $ 24,000.00
East Miscellaneous $ 11,500.00 $ 12,500.00 $ 12,500.00 $ 36,500.00
East Advertising $ 12,250.00 $ 12,250.00 $ 12,750.00 $ 37,250.00
East Clerical Support $ 25,000.00 $ 24,000.00 $ 26,390.00 $ 75,390.00
North Technical Support $ 800.00 $ 950.00 $ 750.00 $ 2,500.00
North Overhead $ 850.00 $ 750.00 $ 800.00 $ 2,400.00
North Maintenance $ 940.00 $ 950.00 $ 820.00 $ 2,710.00
North Telephone $ 980.00 $ 850.00 $ 950.00 $ 2,780.00
North Contractors $ 1,250.00 $ 1,250.00 $ 1,250.00 $ 3,750.00
North Software $ 1,150.00 $ 1,255.00 $ 1,400.00 $ 3,805.00
North Supplies $ 2,410.00 $ 1,850.00 $ 2,390.00 $ 6,650.00
North Telemarketing $ 3,200.00 $ 3,760.00 $ 3,750.00 $ 10,710.00
North Copying $ 5,000.00 $ 4,800.00 $ 4,500.00 $ 14,300.00
North Consultants $ 5,250.00 $ 8,990.00 $ 5,515.00 $ 19,755.00
North Rent $ 6,020.00 $ 6,020.00 $ 6,020.00 $ 18,060.00
North Miscellaneous $ 12,940.00 $ 11,300.00 $ 11,500.00 $ 35,740.00
North Advertising $ 14,250.00 $ 15,250.00 $ 12,050.00 $ 41,550.00
North Clerical Support $ 25,700.00 $ 24,200.00 $ 26,930.00 $ 76,830.00
South Overhead $ 2,140.00 $ 2,310.00 $ 2,000.00 $ 6,450.00
South Technical Support $ 730.00 $ 525.00 $ 430.00 $ 1,685.00
South Telephone $ 700.00 $ 750.00 $ 750.00 $ 2,200.00
South Maintenance $ 2,000.00 $ 950.00 $ 800.00 $ 3,750.00
South Supplies $ 745.00 $ 780.00 $ 900.00 $ 2,425.00
South Software $ 1,150.00 $ 1,200.00 $ 1,400.00 $ 3,750.00
South Copying $ 2,780.00 $ 3,590.00 $ 2,300.00 $ 8,670.00
South Contractors $ 3,490.00 $ 32,840.00 $ 3,070.00 $ 39,400.00
South Rent $ 4,700.00 $ 4,700.00 $ 4,700.00 $ 14,100.00
South Consultants $ 5,250.00 $ 5,000.00 $ 5,500.00 $ 15,750.00
South Telemarketing $ 6,980.00 $ 6,310.00 $ 6,375.00 $ 19,665.00
South Advertising $ 11,250.00 $ 11,250.00 $ 11,750.00 $ 34,250.00

Page 16
 Database Functions

South Miscellaneous $ 24,500.00 $ 23,500.00 $ 24,500.00 $ 72,500.00

South Salaries $ 56,900.00 $ 62,800.00 $ 60,870.00 $ 180,570.00
South Clerical Support $ 24,290.00 $ 24,050.00 $ 26,600.00 $ 74,940.00
West Overhead $ 775.00 $ 750.00 $ 700.00 $ 2,225.00
West Telephone $ 700.00 $ 750.00 $ 750.00 $ 2,200.00
West Technical Support $ 300.00 $ 100.00 $ 150.00 $ 550.00
West Supplies $ 2,000.00 $ 1,800.00 $ 1,900.00 $ 5,700.00
West Maintenance $ 2,000.00 $ 950.00 $ 800.00 $ 3,750.00
West Contractors $ 1,250.00 $ 1,250.00 $ 1,250.00 $ 3,750.00
West Software $ 1,150.00 $ 1,200.00 $ 1,435.00 $ 3,785.00
West Telemarketing $ 3,800.00 $ 3,700.00 $ 3,750.00 $ 11,250.00
West Copying $ 5,000.00 $ 4,800.00 $ 4,545.00 $ 14,345.00
West Rent $ 5,000.00 $ 5,000.00 $ 5,000.00 $ 15,000.00
West Consultants $ 5,250.00 $ 5,335.00 $ 5,500.00 $ 16,085.00
West Advertising $ 10,250.00 $ 10,250.00 $ 10,750.00 $ 31,250.00
West Miscellaneous $ 14,500.00 $ 13,500.00 $ 15,500.00 $ 43,500.00
West Salaries $ 72,000.00 $ 70,000.00 $ 70,000.00 $ 212,000.00
West Clerical Support $ 25,000.00 $ 24,000.00 $ 26,000.00 $ 75,000.00

Page 17
 Database Functions

Look up
Expenses

Division Category Total Expenses

East Software $ 5,875.00

Look up
TOTAL Expenses

Category Total Expense

Software 17215

Look up
AVG. Expenses

Category AVG. Expense

Rent 17790

Page 18
Last Name First Name Full Name: Left Mid Right
Smith Howard Howard Smith Neverthe less
Gonzales Joe Joe Gonzales
Scote Gail Gail Scote
Kane Sheryl Sheryl Kane Nevertheless
Hapsbuch Kendrick Kendrick Hapsbuch
Henders Mark Mark Henders
Atherton Katie Katie Atherton
Bellwood Frank Frank Bellwood
Cooper Linda Linda Cooper
Cronwith Brent Brent Cronwith
Simpson Sandrae Sandrae Simpson
Sindole Randy Randy Sindole
Smith Ellen Ellen Smith
Vuanuo Tuome Tuome Vuanuo
Szcznyck Tadeuz Tadeuz Szcznyck
Wu Tammy Tammy Wu
Number Other Useful Text Functions
123 45 67 Function Before After
1122 334 12 `=Proper() faz karim Faz Karim
444 123 321 `=Upper() faz karim FAZ KARIM
22 1 111 `=Lower() FAZ KARIM faz karim
1010 11 33
22 33 55
ext Functions
Try it!
Faz Karim
FAZ KARIM
faz karim
 Connecticut

Connecticut Division

Item QTR 1 QTR 2 QTR 3 QTR 4

Software 600 800 900 500
Training 400 100 800 100
Maintenance 200 500 500 200
Misc. 200 300 500 300

Totals: $1,400 $1,700 $2,700 $500

Page 22
 Maine

Maine Division

Item QTR 1 QTR 2 QTR 3 QTR 4

Software 400 800 900 300
Training 200 500 1200 100
Maintenance 300 400 1400 300
Misc. 100 300 500 300

Totals: $1,000 $2,000 $4,000 $1,000

Page 23
 New Hampshire

New Hampshire Division

Item QTR 1 QTR 2 QTR 3 QTR 4

Software 500 600 700 400
Training 300 200 500 600
Maintenance 200 400 500 100
Misc. 300 300 100 200

Totals: $1,300 $1,500 $1,800 $1,300

Page 24
 Summary Division

Item QTR 1 QTR 2 QTR 3 QTR 4

Software 1500 2200 2500 1200
Training 900 800 2500 800
Maintenance 700 1300 2400 600
Misc. 600 900 1100 800 Karim Faz:
Just to let you know! this is
Totals: $5,900 $8,200 $14,500 $5,600
Consolidation by position: The da
the same order, and uses the same labels. Use t
data from a series of worksheets, such as depart
that have been created from the sa
 Karim Faz:
ust to let you know! this is called:

n by position: The data in the source areas has

uses the same labels. Use this method to consolidate
worksheets, such as departmental budget worksheets
e been created from the same template.
 Henry Albertson
January 2015

Product Sales Commission

CD Encyclopedia $34.00 $5.10
CD Dictionary $45.00 $6.75
CD Thesaurus $22.00 $3.30
 Alan Patel
January 2015

Product Sales Commission

CD Thesaurus $22.00 $3.30
CD Road Atlas $14.00 $2.10
CD Encyclopedia $54.00 $8.10
 Caroline Brenshaw
January 2015

Product Sales Commission

CD Road Atlas $44.00 $6.60
CD Dictionary $32.00 $4.80
 Consolidated Sales Data
January 2015

Insert Consolidated Data below:::

Sales Commission Karim Faz:
Excel 2019 Advanc $22.00 $3.30 Just to let you know! this is calle
Excel 2019 Advanc $22.00 $3.30
CD Thesaurus $44.00 $6.60 Consolidation by category: Whe
Excel 2019 Advanc $14.00 $2.10 source areas is not arranged in the same order b
Excel 2019 Advanc $44.00 $6.60 labels. Use this method to consolidate data fr
CD Road Atlas $58.00 $8.70 worksheets that have different layouts but hav
Excel 2019 Advanc $54.00 $8.10 labels.
Excel 2019 Advanc $34.00 $5.10
CD Encyclopedia $88.00 $13.20
Excel 2019 Advanc $32.00 $4.80
Excel 2019 Advanc $45.00 $6.75
CD Dictionary $77.00 $11.55
 Karim Faz:
let you know! this is called:

by category: When the data in the

anged in the same order but uses the same
hod to consolidate data from a series of
different layouts but have the same data
labels.
The Question: Is Excel awesome?
Karim Faz:

2/3 = 66.67%
er

s
te
sw

%
Vo
An

Yes 5060 66.67% Who ever has 2/3 of the Votes

No 2530 33.33% wins
Total 7590 100.00%
how many more votes did we
need to win?
Faz:

6.67%

3 of the Votes
s

e votes did we
win?
 PMT Data Table

Credit Card Payment Plan

Current Balance: $8,000
Interest Rate: 14.2%
Project Payment Period 48
(Months):

Alternatives:
($219.42)
12.00% $ (210.67)
Interest Rate

12.50% $ (212.64)

Months
13.00% $ (214.62)
13.50% $ (216.61)
14.00% $ (218.61)
14.50% $ (220.62)
15.00% $ (222.65)

Alternatives: Months
($219.42) 190 240 360 420
12.00% ($94.23) ($88.09) ($82.29) ($81.24)
Interest Rate

12.50% ($96.85) ($90.89) ($85.38) ($84.42)

Months
13.00% ($99.51) ($93.73) ($88.50) ($87.62)
13.50% ($102.20) ($96.59) ($91.63) ($90.83)
14.00% ($104.91) ($99.48) ($94.79) ($94.05)
14.50% ($107.66) ($102.40) ($97.96) ($97.29)
15.00% ($110.42) ($105.34) ($101.16) ($100.55)

Page 34
 PMT Data Table

Practice:
($219.42)
12 $ (719.05)
24 $ (384.86)
36 $ (274.20)
48 $ (219.42)
60 $ (186.98)
72 $ (165.70)
84 $ (150.81)

Practice: Interest Rate

($219.42) 16.00% 16.50% 17.00% 17.50%
12
24
36
48
60
72
84

Page 35
 Scenarios

Pear Company Sales Projections

Region Q1 Q2 Q3 Q4
North $79,640.00 $82,268.12 $84,982.97 $87,787.41
East $79,840.00 $81,676.32 $83,554.88 $85,476.64
West $90,440.00 $94,328.92 $98,385.06 $102,615.62
South $97,880.00 $98,956.68 $100,045.20 $101,145.70
Totals: $347,800.00 $357,230.04 $366,968.11 $377,025.37

Predicted Sales Growth

North 3.30%
East 2.30%
West 4.30%
South 1.10%

Page 36
 Scenarios

ions
Yearly Total
$334,678.49
$330,547.83
$385,769.60
$398,027.58
$1,449,023.52

Page 37
Faz Karim Faz Karim Faz Karim
Learn iT! Learn iT! Learn iT!
2/12/2022 2/12/2022 2/12/2022

Faz Karim
Learn iT!
2/12/2022

Faz Karim
Learn iT!
2/12/2022
Faz Karim
Learn iT!
2/12/2022
Pear Company
Monthly Phone Sales

Run Date: 2/12/2022

Ran By: FK

R.Smith $3,241 $3,109 $3,703 $4,246

H. James $4,712 $4,895 $4,107 $4,754
S.O'Brian $3,860 $4,816 $3,632 $3,307
L. Carrie $3,027 $3,119 $4,895 $3,532
K. Dunn $4,813 $3,237 $3,324 $4,762
J. Johnson $4,167 $3,382 $4,458 $4,199
Pear Company
Monthly Phone Sales

Run Date: 2/12/2022

Ran By: FK

R.Smith $3,241 $3,109 $3,703 $4,246

H. James $4,712 $4,895 $4,107 $4,754
S.O'Brian $3,860 $4,816 $3,632 $3,307
L. Carrie $3,027 $3,119 $4,895 $3,532
K. Dunn $4,813 $3,237 $3,324 $4,762
J. Johnson $4,167 $3,382 $4,458 $4,199
Pear Company
Monthly Phone Sales

Run Date: 2/12/2022

Ran By: FK

R.Smith $3,241 $3,109 $3,703 $4,246

H. James $4,712 $4,895 $4,107 $4,754
S.O'Brian $3,860 $4,816 $3,632 $3,307
L. Carrie $3,027 $3,119 $4,895 $3,532
K. Dunn $4,813 $3,237 $3,324 $4,762
J. Johnson $4,167 $3,382 $4,458 $4,199
K. Rein $4,453 $3,303 $3,344 $3,938
A. Paoli $4,622 $4,280 $3,574 $3,477
L. Harrington $3,990 $4,547 $4,883 $4,439
O. Quincet $4,115 $4,557 $3,870 $3,938
E. Windham $3,239 $4,545 $3,676 $3,899
I. Bainbridge $4,778 $3,055 $4,750 $4,079
V. Nesbit $3,819 $3,534 $4,667 $4,808
F. Vanders $3,831 $3,414 $4,205 $4,782
S. Halsey $3,716 $3,963 $3,230 $4,517
J. Mancera $4,646 $3,044 $3,399 $3,665
G. Hicks $4,880 $4,448 $4,958 $4,712
L. McNair $4,963 $4,648 $3,762 $4,693
C. Gipson $3,407 $3,273 $3,365 $4,796
K. Wright $3,157 $3,106 $3,989 $3,121
Pear Company
Monthly Phone Sales

Run Date: 2/12/2022

Ran By: FK

R.Smith $3,241 $3,109 $3,703 $4,246

H. James $4,712 $4,895 $4,107 $4,754
S.O'Brian $3,860 $4,816 $3,632 $3,307
L. Carrie $3,027 $3,119 $4,895 $3,532
K. Dunn $4,813 $3,237 $3,324 $4,762
J. Johnson $4,167 $3,382 $4,458 $4,199
K. Rein $4,453 $3,303 $3,344 $3,938
A. Paoli $4,622 $4,280 $3,574 $3,477
L. Harrington $3,990 $4,547 $4,883 $4,439
O. Quincet $4,115 $4,557 $3,870 $3,938
E. Windham $3,239 $4,545 $3,676 $3,899
I. Bainbridge $4,778 $3,055 $4,750 $4,079
V. Nesbit $3,819 $3,534 $4,667 $4,808
F. Vanders $3,831 $3,414 $4,205 $4,782
S. Halsey $3,716 $3,963 $3,230 $4,517
J. Mancera $4,646 $3,044 $3,399 $3,665
G. Hicks $4,880 $4,448 $4,958 $4,712
L. McNair $4,963 $4,648 $3,762 $4,693
C. Gipson $3,407 $3,273 $3,365 $4,796
K. Wright $3,157 $3,106 $3,989 $3,121