USING MICROSOFT EXCEL 2016 - Independent Project 2-4 - Central Sierra Insurance (Update 2025)

USING MICROSOFT EXCEL 2016
Independent Project 2-4 (Mac 2016)
Independent Project 2-4 (Mac 2016 Version)
Central Sierra Insurance (CSI) sets bonus percentages based on commissions earned by each agent and calculates totals by
branch office. This workbook also tracks fundraising efforts of employees for a community event.
Skills Covered in This Project
• Create, copy, and edit formulas.
• Name cell ranges.
• Set mathematical order of operations.
• Set cell references to be absolute.
• Use the NOW function.
• Use HLOOKUP and VLOOKUP functions.
• Use the SUMIF function.
• Build an IF function.
Step 1:
Download
start file
1.
2.
3.
4.
Open the CentralSierra-02.xlsx start file. If the workbook opens in Protected View, click the
Enable Editing button so you can modify it. The file will be renamed automatically to include
your name. Change the project file name if directed to do so by your instructor, and save it.
On the Tables sheet, select cells A6:B10 and create range names using the Create from
Selection dialog [Formulas tab, Defined Names group].
Select cells B1:F2 and click the Name box. Name this as H_Rates.
Create an HLOOKUP function to display the bonus rate.
a. Click the Commissions sheet tab and select cell F5.
b. Start the HLOOKUP function and use cell E5 as the lookup_value.
c. For the table_array argument, use the H_Rates range.
d. Use the second row for the row_index_num argument (Figure 2-100).
~
F5
A
X
I
B
v'
I
f,,
C
=HLCX>KUP(E>1H_Rates12)
I
D
I
E
F
I
G
I
H
I
I
I
J
I
K
I
L
1_
2
3
4
<
6
Central Sierra Insurance
Commission Sales and Bonuses
ID
101 Linale
Last Name First Name
Bob
102 McCartnev Lanita
103 Santos
Branch
Cameron Park
Folsom
Commissions Bonus Rate
$1i:;:,ooo Rates,2)
Sl2 ooo
I
Function Arguments
HLOOKUP
Looll:up_value
[Es
T.ii ble_array
Row_index_num 121
Range_lookup [
I H_Rates
~ . 15(00
I
M
?
li'Sil . {3000,6000,9000, 12000, 15000;0.015,0.(
li'Sil . 2
7
8
9
10
11
12
13
14
12._
.c
104 Baxter
105 Alaro
115 Chan
117 Althouse
119 Taylor
1 20 Reia
125 Nevens
Eleesha
Roy
Jennifer
Tami
Charlene
Juan
Wayne
Cheryl
Cameron Park
Granite Bay
Folsom
Cameron Park
Granite Bay
Granite Bay
Cameron Park
Cameron Park
s5,500
s4,500
$3,000
$3,000
s4,700
S4fOOO
S4fOOO
$12,000
ffl . logtCal
. 0.025
look, for o voluc in the lop row of o toblc: or orr~y ofvoluu ond rc:lurni the: vo luc: in the Hime column from
a row you specify.
Rowjndex_num is the row number in table_arrayfrom which the matching value
l hould be: rc:lurnc:d, The: firlt row of value:) in the toblc i~ row 1,
formulo rnult: 0.025
Hel~ on this foodion
l
OK
I
t -LJ
I ~
N
X
2-100 HLOOKUP function to display bonus rate
5.
6.
7.
8.
Format the results as Percent Style with two decimal places.
Copy the formula in cell F5 to cells F6:F14.
Set order of operations to calculate total earnings.
a. Select cell G5.
b. Build a formula to add the commissions amount (E5) to the commissions amount times the
rate (F5*E5).
c. Copy the formula in cell G5 to cells G6:G14.
Create and copy a SUMIF function to calculate total earnings by branch office.
a. Select cell E18.
b. Start the SUMIF function with cells $D$5:$D$14 as the Range argument.
Excel 2016 Chapter 2 Working with Formulas and Functions
Last Updated: 12/18/17 Page 1
USING MICROSOFT EXCEL 2016
c. The Criteria argument is a relative reference to cell C18.
d. Select cells G5:G14 for the Sum_range
argument and make the references absolute.
e. Copy the formula in cell E18 to cells E19:E20
without formatting.
Format Cells
Number ~ Alignment
Cat@gory:
f.
9.
Format cells E18:E20 as Currency.
Total the earnings in cell E21.
10. Create and format the current date.
a. Select cell G23 and insert the NOW function.
b. Select cell G23. Click the Number drop
down list, and select More Number Formats.
On the Number tab, select the Date
category.
General
Number
Currency
l~fu;°untiny
Time
Percentage
Fraction
Scientific
Text
Special
Custom
Independent Project 2-4 (Mac 2016)
X
Font I Border I fill
Sample
January 1, 2018
Jype:
14-Mar
14-Mar-1 2
14-Mar-12
Mar-12
March-1 2
MMMll411
3/ 1-4/12 1:30 PM
~ocale (location):
I English (United Statest
I Protection I
r
H
c.
Scroll the Type list to find the date that displays
the month spelled out, the date, a comma,
and a four-digit year (Figure 2-101).
d. Click OK.
11. Create and copy a VLOOKUP function to
display goals for each funding source.
a. Click the Family Day sheet tab and select
cell F6.
b. Start the VLOOKUP function and use cell E6
as the lookup_value.
c. Click the Tables sheet tab for the
table_array argument and use cells
$A$6:$B$10.
d. Use the second column as the
col_index_num argument.
e. Copy the formula in cell F6 to cells F7:F20
without formatting to preserve the fill color.
f.
Format cells F6:F20 as Currency with no
decimal places.
12. Create and copy an IF function.
a. Select cell H6 and start an IF function.
b. Type a logical_argument to determine if cell
G6 is greater than or equal to (>=) cell F6.
c. Type Yes as the Value_if_true argument
and No as the Value_if_false argument.
d. Copy the formula in cell H6 to cells H7:H20
without formatting to maintain the fill color.
e. Center align cells H6:H20.
f.
Select cell A1.
13. Insert a new sheet at the end of the tab
names and paste the range names starting in
cell A1(Hint: Insert menu, Name, Paste). Name
the worksheet as Range Names.
Step 2
14. Save and close the workbook (Figure 2-102).
Upload &
Save
Step 3
15. Upload and save your project file.
Grade my
Project
16. Submit project for grading.
Date formats display d~te and time serial numbers as date values. Date formats that begin with
an asteris~ (*) re spond to changes in regional date and time settings that are specified for the
opuating syst@m. Formats without .in astuisk a1@ not afhct@d by opuating syst@m u ttings.
2-101 Date format selected
Central Slerr11 Insurance
Commmion Sales and
I
Central Sierra ln5"ranc.e
Family Day Community Fu ndraise
·-D 4':41 ___ Bab ____ ~ "HNnP;ft,; Rilfflilll~
·--:m Mceutn,L Lll'ftll ___ l"'daom __ W..~ pl~dg• -- ~ S.,rmi ___ B..t._ C.-:.•-an!P.tc i:.tidaraTlan -·
___ Ba-mr ___ ~----Grillw B_!l __ ~
.fii'r.s __ _ -- ~ ~ --· Jim hr_ fal10m ---·· l'.dmn.111' ... , .
·--· Ch.ti ·--· 1'~11!111 ___ Cr't.anPirt RilffllllO:U:.: __
___ lt»a.l,:, __ owtne GniiMC! B1 __ W-1~ PJ!~t . -
c;...1'111118!)' w..t,1Mp1~~·
C;n,c,,an~ Cia'IOJl'lill:ton _
c..,..,._.., ••
Cmh_d:lrili:lti::m --~-- Yti _ --· •5 ·--No ---..!l..11 __ !.,• __ ----~ ·-Y.;is ---· 'r.P --.,._ ·----1-115_ ____ No
__ n.1$. __ Yoes·-· --· •E-l ·-.,._ ·-· --~1-15 __ )!> __
:::T11bl1!~!:s&.6
=Tabl1!~!s6,.7
Pech:1ml!ti!!!rJB~! =T11bl1!,!2tli8
Rsffle_•i:::k:!!t, =T11bl1!,!:s&g
Wall;..run_pe(!;)e •Table1
2-102 Excel 2-4
Excel 2016 Chapter 2 Working with Formulas and Functions
Last Updated: 12/18/17 Page 2