Business & Finance assignment 2
chengxihe
BCOR3010-Assignment2-StepbyStepExcelInstructions.pdf
KAT Insurance Company (KAT)
• This data set is based on real‐life data from a national insurance company. All data has been anonymized to preserve privacy. •Data set contains sales data for 2017 • This set of instructions is based on a
small, 24‐record data set • For the actual activity, you will be using
the full data set so the answers will be different but the process will be similar
2
Pivot tables and pivot charts
•Office 365 Excel in Windows is used in this set of instructions •Other versions of Excel may be
slightly different
For this set of instructions on pivot tables and pivot charts, we will be demonstrating using Office 365 Excel for Windows. Other versions of Excel may be slightly different. Also note that there may be many ways of accomplishing the same thing. We are just presenting one way here. Make sure your version of Office 365 is updated; you may not see things the same way if you have not updated recently.
3
Update Office 365 (Excel 2016) before working on project
Again, please be sure to update your version of Excel before working on this project. Certain features will not work if you are not using the most current version of Excel.
4
Start by opening Excel workbook
Start by opening Excel file and click on the Main worksheet
5
General instructions
You will be using the Excel file provided to create pivot tables and other data. Step‐by‐step instructions using a smaller data set follow for each of the requirements for the assignment.
6
Data worksheet name
The Excel data file for this project contains multiple worksheets. One of them contains sales data including Region, State, Salesperson, Insurance Type, and so on. That is your main data sheet. Here in this set of instructions it is called “Main” but the main data sheet might be called “Data” or “MainData” or something similar. Just make note of the worksheet name in your data set and substitute that name wherever “Main” is used in this set of instructions.
7
Requirement 1 Find and correct errors in the Region and Insurance Type fields
8
Req 1: Find and correct errors
#1: Click on any cell in the data in the sales data worksheet
9
Req 1: Find and correct errors
#2: On the Insert ribbon, click on Pivot Table
10
Req 1: Find and correct errors
#3: Accept the defaults to insert the Pivot Table
11
Req 1: Find and correct errors
#4: Right‐click the worksheet tab to rename it as “Req 1”
12
Req 1: Find and correct errors
#5: Drag “Region” in the Pivot Table Fields panel down to the Rows box and “Sales” down to the Values box
Next, we will populate the pivot table. We would like to see what the total sales are by region.
Excel has summarized total sales by region in a matter of seconds. The same values could be obtained by manually summing the sales for each region.
13
Req 1: Find and correct errors
#6: Examine the list of Regions to find any error(s)
You now have a pivot table. Examine the list of Regions to find any error(s).
14
Req 1: Find and correct errors
One of the regions is misspelled
Here we see that one of the regions is misspelled.
Remember that this is a small data set. In the real data set, there may be multiple, different errors.
15
Req 1: Find and correct errors
#7: Click on the Main tab to go back to the data
Now we want to correct the original data set for the spelling error. Click on the Main tab to go back to the original data.
16
Req 1: Find and correct errors
#8: In the Home ribbon, click on Find & Select. Type the incorrect term in “Find what” box and the correct spelling in “Replace with” box. Click on Replace All.
In the Home ribbon, click on Find & Select. Type the incorrect term in the “Find what” box and the correct spelling in the “Replace with” box. Click on Replace All.
17
Req 1: Find and correct errors
#9: Go back to the Req 1 worksheet, right‐ click in the pivot table, and select Refresh
Now we need to refresh the pivot table data to reflect the corrections. Go back to the Requirement 1 worksheet, right‐click within the pivot table data, and select Refresh.
18
Req 1: Find and correct errors
You can see that the incorrect region is no longer in the data set
You can see that the incorrect region term is no longer present in the data set.
19
Req 1: Find and correct errors
By the way, if this panel ever disappears, you can bring it back by clicking anywhere in the pivot table you created.
20
Req 1: Find and correct errors
#10: Now repeat the process for the Insurance Type field. Drag “Region” back up to the main box and drag “Insurance Type” down to the Rows box.
21
Req 1: Find and correct errors
#11: Review the list of Insurance Types to find the error(s). Correct those errors by following the process we just used to correct the error(s) in Region.
22
Req 1: Find and correct errors
All errors have been corrected now
Once the errors are corrected and the pivot table is refreshed, we can see that the incorrect insurance types are no longer present in the data set.
23
Requirement 2 Calculate the variable cost and contribution margin for each policy sold.
For the second requirement, we will be calculating the variable cost and contribution margin for each policy sold.
To get the variable cost percentage for each sale from the Variable CostPct worksheet into the Main worksheet, we will use the VLOOKUP function.
24
Req 2: Calculate VC and CM
#1: Click on the Main worksheet and click on Cell I2 to lookup the variable cost percentage from the VariableCostPct worksheet. Type: =VLOOKUP(H2,VariableCostPct!$A$2:$B$246,2,0)
We need to add the variable cost percentage to the Main data worksheet first. The variable cost percentage is in the VariableCostPct worksheet. To get the variable cost percentage for each sale, we will use the VLOOKUP function. You will click on Cell I2 to locate the percentage.
The formula is =VLOOKUP(H2,VariableCostPct!$A$2:$B$246,2,0).
We are going to walk through the elements of that formula next.
25
Req 2: Calculate VC and CM
=VLOOKUP(H2,VariableCostPct!$A$2:$B$246,2,0)
Let’s look at that VLOOKUP formula that we just typed into cell I2, which needs to contain the variable cost percentage based on each row’s State Type code.
26
Req 2: Calculate VC and CM
=VLOOKUP(H2,VariableCostPct!$A$2:$B$246,2,0)
The cell that contains the State Type code in this worksheet
After typing in =VLOOKUP, the first term is the cell that contains the State Type code in this worksheet, which is cell H2. The column that is the same in both worksheets (i.e. State Type) must be in the leftmost column in order for the vlookup to work.
27
Req 2: Calculate VC and CM
=VLOOKUP(H2,VariableCostPct!$A$2:$B$246,2,0)
The cell that contains the State Type code in this worksheet
The range in the VariableCostPct worksheet that contains all the State Type codes and the related variable cost percentages
Next, we would point to the range that contains all of the State Type codes and the related variable cost percentages.
28
Req 2: Calculate VC and CM
=VLOOKUP(H2,VariableCostPct!$A$2:$B$246,2,0)
The cell that contains the State Type code in this worksheet
The range in the VariableCostPct worksheet that contains all the State Type codes and the related variable cost percentages
$ signs prevent Excel from changing the row or column when copied
$ means an “absolute” reference versus a “relative” reference
Notice that we have to insert dollar signs in front of the row and column references in the range. Those dollar signs prevent Excel from changing the row or column when the formula is copied. Those dollar signs make the Excel reference an absolute reference. References in formulas that change cell addresses as you copy them are relative references.
29
Req 2: Calculate VC and CM
=VLOOKUP(H2,VariableCostPct!$A$2:$B$246,2,0)
The cell that contains the State Type code in this worksheet
The range in the VariableCostPct worksheet that contains all the State Type codes and the related variable cost percentages
The column number in the VariableCostPct worksheet that contains the variable cost percentage
$ signs prevent Excel from changing the row or column when copied
The next part of the formula is the column number in the VariableCostPct worksheet that contains the variable cost percentage.
30
Req 2: Calculate VC and CM
=VLOOKUP(H2,VariableCostPct!$A$2:$B$246,2,0)
The cell that contains the State Type code in this worksheet
The range in the VariableCostPct worksheet that contains all the State Type codes and the related variable cost percentages
The 0 will produce an error code if there is no match
The column number in the VariableCostPct worksheet that contains the variable cost percentage
$ signs prevent Excel from changing the row or column when copied
And finally, the last piece of the formula is a zero at the end. This zero will produce an error code if there is no match for the State Type code.
31
Req 2: Calculate VC and CM
#2: Now calculate the variable cost in cell J2, click and type =E2*I2
32
Req 2: Calculate VC and CM
#3: Now calculate the contribution margin in cell K2 by typing =E2‐J2
33
Req 2: Calculate VC and CM
#4: Select cells I2..K2 and double‐click the cell handle in the lower right corner of cell K2
Now let’s copy those formulas to the rest of the rows.
34
Req 2: Calculate VC and CM
The formulas have been copied down to all the rows
35
Req 2: Calculate VC and CM
#5: Select Columns J and K. Click on the $ sign on the Home ribbon to format as Accounting number format.
One final thing before we leave this step. Let’s make sure that our new columns J and K are formatted as Accounting number format. Now we are done with this step.
36
Requirement 3
Analyze sales revenue, variable cost, and contribution margin by Insurance Type.
37
#1: Insert a pivot table using the same steps as for Req. 1:
a. Click any cell in the main worksheet
b. In the Insert ribbon, click on Pivot Table
c. Click on OK, accepting the defaults.
Req 3: Analyze CM by Insurance Type
38
#2: Rename the worksheet as Req 3 by right‐clicking the worksheet tab
Req 3: Analyze CM by Insurance Type
#2: Right‐click the worksheet tab to rename it as “Req 3”
39
#3: Drag “Insurance Type” down to the Rows box and drag “Sales,” “Variable Cost,” and “Contribution Margin” down to the Values box
Req 3: Analyze CM by Insurance Type
Next, we will populate the pivot table.
40
#4: Click the down arrow next to the “Count of Variable Cost” field in the Values box
Req 3: Analyze CM by Insurance Type
Click on the down arrow next to the Count of Variable Cost field in the Values box. If it says Sum of Variable Cost, you don’t have to change it to be Sum since it is already Sum. But chances are that yours says Count of Variable Cost.
41
#5: Select Value Field Settings
Req 3: Analyze CM by Insurance Type
42
#6: Select Sum and click OK
Req 3: Analyze CM by Insurance Type
43
#7: Click the down arrow next to the “Count of Contribution Margin” field in the Values box
Req 3: Analyze CM by Insurance Type
Now we are going to fix the other field. Click on the down arrow next to the Count of Contribution Margin field in the Values box.
44
#8: Select Value Field Settings
Req 3: Analyze CM by Insurance Type
If the values are not shown you will have to refresh the pivot table by right clicking within the table and then clicking refresh.
45
#9: Select Sum and click OK
Req 3: Analyze CM by Insurance Type
46
#10: Select the Sales data in the pivot table, right‐click, and select Value Field Settings
Req 3: Analyze CM by Insurance Type
47
#11: Click on Number Format in the bottom left corner and then select Accounting with 2 decimal places, and then click OK
Req 3: Analyze CM by Insurance Type
48
#12: Repeat the format steps (Steps #10 and #11) for the Variable Cost data and for the Contribution Margin data
Req 3: Analyze CM by Insurance Type
49
#13: Drag Contribution Margin down to the Value box
Req 3: Analyze CM by Insurance Type
50
#14: Right-click Sum of Contribution Margin field and select Value Field Settings
Req 3: Analyze CM by Insurance Type
51
#15: Select Count and click OK
Req 3: Analyze CM by Insurance Type
52
#16: Drag Contribution Margin down to the Values box again
Req 3: Analyze CM by Insurance Type
53
#17: Right click Sum of Contribution Margin field and select Value Field Settings
Req 3: Analyze CM by Insurance Type
54
#21: Select Average and click OK
Req 3: Analyze CM by Insurance Type
55
#22: Select the data in the Average column, right‐click, and select Value Field Settings
Req 3: Analyze CM by Insurance Type
56
#23: Click on Number Format, and then Accounting with 2 decimal places, and click OK
Req 3: Analyze CM by Insurance Type
57
#24: Select the first cell in the Sum of Contribution Margin column, right‐click, and Sort, Sort Largest to Smallest
Req 3: Analyze CM by Insurance Type
58
We can see the CM margin now, sorted by highest CM to lowest CM
Req 3: Analyze CM by Insurance Type
59
Answer the questions under requirement #3.
Requirement 4
Calculate the contribution margin ratio for each policy. Rank the Insurance Type field from the highest contribution margin ratio to lowest contribution margin ratio.
For the fourth requirement, we will be calculating the contribution margin ratio for each policy and then analyzing insurance types using the contribution margin ratio.
60
#1: Insert a pivot table using the same steps as for Req. 1:
a. Click any cell in the main worksheet
b. In the Insert ribbon, click on Pivot Table
c. Click on OK, accepting the defaults.
Req 4: Analyze CM ratio by Insurance Type
61
#2: Right‐click the worksheet tab to rename it as “Req 4”
Req 4: Analyze CM ratio by Insurance Type
62
#3: Drag “Insurance Type” down to the Rows box
Req 4: Analyze CM ratio by Insurance Type
Drag Insurance Type down to the Rows box. Our current data set contains the Contribution Margin Dollars but not the Contribution Margin Percentage. We can create a calculated field to do this for us.
63
#4: Click in the pivot table. Then, in the Analyze tab (ribbon) click on the “Fields, Items, & Sets” icon and click on Calculated Field.
Req 4: Analyze CM ratio by Insurance Type
64
#5: Fill in the name field with “CMratio.” Then select Contribution Margin from the list, type “/” and then select Sales from the list, click on Add, and then OK
Req 4: Analyze CM ratio by Insurance Type
65
#6: Select the CM ratio data and right‐click to select Value Field Settings
Req 4: Analyze CM ratio by Insurance Type
66
#7: Select the Number Format and then Percentage with 2 decimal places
Req 4: Analyze CM ratio by Insurance Type
67
#8: Click on a cell in the Sum of CMratio column, right‐click, and Sort, Sort Largest to Smallest
Req 4: Analyze CM ratio by Insurance Type
68
Now the data is sorted from largest to smallest.
Req 4: Analyze CM ratio by Insurance Type
69
Answer the questions under requirement #4.
Requirement 5 Calculate fixed cost for each policy sold. Determine the breakeven number of policies in total and for each insurance type.
For the fifth requirement, we will need to find fixed costs for each sale.
We need to add the fixed cost to the Main data worksheet.
First, we need to insert a new column between column K and L on the Main data worksheet. Start by highlighting column L -- click at the top of the column and the entire column will be highlighted. Next, right-click anywhere on the column (the column should remain highlighted) and choose "Insert". The new column should be "L". Label this new column "Fixed Cost".
The fixed cost for each policy type is located in the FixedCost worksheet. To get the fixed cost for each sale, we will use the VLOOKUP function again, similar to what you did for the variable cost percentage in Requirement 3.
Click on Cell L2 on the Main data worksheet to locate the fixed cost.
The formula is =VLOOKUP(H2,FixedCost!$A$2:$B$246,2,0).
Be sure and doubleclick the cell handle in the lower right corner of cell L2 to copy the formula down in the column.
We now have fixed costs in our data fields in the Main data worksheet.
70
#2: Insert a pivot table using the same steps as for Req. 1:
a. Click any cell in the main worksheet
b. In the Insert ribbon, click on Pivot Table
c. Click on OK, accepting the defaults.
Req 5: Determine Breakeven
71
#3: Rename the worksheet as Req 5 by right‐clicking the worksheet tab
Req 5: Determine Breakeven
72
#4: Drag “Insurance Type” down to the Rows box and "Contribution Margin" and “Fixed Cost” down to the Values box. If necessary, change the value field setting to "Average" for "Contribution Margin"
Req 5: Determine Breakeven
73
After creating the pivot table, we need to calculate breakeven:
In cell E3, enter "Breakeven". Next, you will insert a formula in cell E4 to calculate the breakeven point for each insurance type.
In cell E4, type =C4/B4. (This formula must be typed in the cell, otherwise you will not be able to copy it to other cells using the next step.)
Doubleclick the cell handle in the lower right corner of cell E4 to copy the formula down to all cells in the column.
Answer the questions under requirement #5.
Requirement 6 Calculate the operating income for each state within each region.
78
#1: Insert a pivot table using the same steps as for Req. 1:
a. Click any cell in the main worksheet
b. In the Insert ribbon, click on Pivot Table
c. Click on OK, accepting the defaults.
Req 6: Analyze OI by Region and State
79
#2: Rename the worksheet as Req 6 by right‐clicking the worksheet tab
Req 6: Analyze OI by Region and State
80
Req 6: Analyze OI by Region and State
After inserting the pivot table, drag Region and State down to the Rows box in the pivot table dialogue box.
81
Our current data set does not contain the Operating Income. We can create a calculated field to do this for us (similar to what you did for CM ratio in requirement 4).
Click in the pivot table and then click on the Fields, Items, & Sets icon in the Analyze ribbon and click Calculated Field.
Fill in the name field with “Operating Income.” Then select Contribution Margin from the list, type “-” and then select Fixed Cost from the list, click on Add, and then OK.
Now select the Operating Income data and right‐click to select Value Field Settings.
Select the Number Format and then Accounting with 2 decimal places.
Select any cell that contains operating income for a state with a region (for example Cell B5), right‐click, and Sort, Sort Largest to Smallest.
Answer the questions under requirement #6.
82
Requirement 7 Calculate operating income for each salesperson.
Req 7: Analyze OI for each salesperson by state
83
For Requirement 7, we will begin by inserting a pivot table using the same steps as before for Requirement 1-6. Click any cell in the main worksheet. In the Insert ribbon, click on Pivot Table and click on OK, accepting the defaults.
Rename the worksheet as Req 7 by right-clicking the worksheet tab.
After inserting the pivot table, drag "Salesperson" and "State" down to the Rows box and "Operating Income" and "Sales" in the Values box.
Now select the Operating Income data and right‐click to select Value Field Settings.
Select the Number Format and then Accounting with 2 decimal places.
Select any cell that contains operating income data for a salesperson in a state (for example Cell B5), right‐click, and Sort, Sort Largest to Smallest.
Select any cell that contains sales data. Right click and select "count" from the "Summarize Values by" option.
Answer the questions under requirement #7.
Don't forget to answer the question under requirement #8.
- Blank Page
