Excel 2021 ?In Practice - ?Ch 2 ?Independent Project 2-4
Central Sierra Insurance sets bonus percentages based on commissions earned by each agent and calculates totals by branch office. This workbook identifies business policies and displays agent names with their locations.
[Student Learning Outcomes 2.1, 2.2, 2.3, 2.5, 2.6, 2.7, 2.8]
File Needed: CentralSierra-02.xlsx (Available from the Start File link.)
Completed Project File Name: [your name]-CentralSierra-02.xlsx
Skills Covered in This Project
Name cell ranges.
Build an XLOOKUP function.
Create, copy, and edit formulas.
Set mathematical order of operations.
Use absolute cell references in formulas.
Use the SUMIF function.
Calculate an average, the mode, and the median.
Build an IF function.
Display labels using CONCAT.
Steps to complete This Project
Mark the steps as checked when you complete them.
Open the workbook 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.
Select the Tables sheet, select cells A1:B6, ?and create range names using the Create from Selection button [Formulas tab, Defined Names group].
Create an XLOOKUP function to display the bonus rate. (If XLOOKUP is not available, build the following formula =VLOOKUP(E5,Tables!$A$2:$B$6,2). ?Leave the Range_lookup argument empty so that VLOOKUP finds the closest match).
Click the Commissions sheet tab and select cell F5.
Start the XLOOKUP function and use cell E5 ?as the Lookup_value.
For the Lookup_array argument, use the Commission_Level range.
For the Return_array argument, use the Bonus_Rate range.
For the Match_mode argument, type -1 ?which finds the next smaller match (Figure 2-106).
Figure 2-106 ?XLOOKUP function to display bonus rate
Format the result as Percent Style with two decimal places.
Copy the formula in cell F5 ?to cells F6:F14.
Set order of operations to calculate the commission and bonus totals. (An alternative formula is =E5*(1+F5)).
Select cell G5. ?The total paid is the dollar amount of the commissions plus the commissions times the bonus rate.
Start a formula and select cell E5.
Add cell E5 ?to the product of commissions amount times the bonus rate (Figure 2-107).
Format the result to show zero decimal places.
Copy the formula in cell G5 ?to cells G6:G14.
Figure 2-107 ?Use parentheses to set the arithmetic order
Create and copy a SUMIF function to calculate total earnings by branch office.
Select cell E18.
Start the SUMIF function with cells $D$5:$D$14 ?as the Range argument.
Set the Criteria argument as a relative reference to cell C18.
Determine the Sum_range argument and make the references absolute.
Copy the formula in cell E18 ?to cells E19:E20 ?without formatting to preserve borders.
Format cells E18:E21 ?as Currency with zero decimal places.
Total the earnings in cell E21.
Calculate the average, mode, and median values in cells E22, ?E23, ?and E24 ?for the commission and bonus totals in column G.
Correct border and number format inconsistencies as needed.
Create and copy an IF function.
Select the By Policy sheet and click cell F7.
Start an IF function with the logical_test E7=“business policy” ?to determine if the policy is for a business. If it is for an individual, it is a personal line policy. You must type the quotation marks because text follows the cell reference.
Type No as the Value_if_true argument. You need not type quotation marks for this argument because it is alone in the entry box.
Type Yes as the Value_if_false argument (Figure 2-108).
Copy the formula in cell F7 ?to complete the column. If it is a business policy, the result is No because the label in cell F6 ?asks if it is a personal line.
Increase the indent two times for cells F7:F27.
Press Ctrl+Home.
Figure 2-108 ?IF function that uses text in the logical_argument
Display names and locations using CONCAT.
Select the Tables sheet and click cell D2.
Start a CONCAT function.
The Text1 ?argument is cell C5 ?on the Commissions sheet.
The Text2 ?argument is a space.
The Text3 ?argument is cell B5 ?on the Commissions sheet.
The Text4 ?argument is a comma and a space.
The Text5 ?argument is cell D5 ?on the Commissions sheet.
Copy the formula in cell D2 ?to complete the list.
Select cell D13 ?and paste the range names.
Press Ctrl+Home.
Group all the sheets and center the pages horizontally.
While the sheets are grouped, insert a footer with the file name in the right section.
Ungroup the sheets.
Save and close the workbook (Figure 2-109).
Figure 2-109 ?Excel 2-4 ?completed
Upload and save your project file.
Submit file for grading.
