Answered step by step
Verified Expert Solution
Question
1 Approved Answer
This is to be done in excel . Class is accounting information system . please help me with the steps please. (4 Points) There are
This is to be done in excel . Class is accounting information system . please help me with the steps please.
- (4 Points)
- There are some typographical errors in the data set in the Region and Insurance Type fields. Find and correct these errors. (Hint: use a Pivot Table or Sort to examine the two fields and look for typos)
- Complete the "variable cost percentage" column by using a VLOOKUP function. (Hint: use "State Type" as the lookup value and the information in the "VariableCostPct" tab as the table_array).
- Complete the "Variable Cost Column" (Hint: Variable Cost = Sales * Variable Cost Percentage).
- Compete the "Contribution Margin Column) (Hint: Contribution Margin = Sales - Variable Cost).
- (4 Points) - Create 4 separate tabs to answer the following questions. Label the tabs 2a, 2b, 2c,2d. Use separate Pivot Tables to answer the below:
- Which Insurance Type had the highest sales?
- Which Insurance Type had the highest total contribution margin?
- How many insurance policies were sold in each Insurance Type?
- What is the average contribution margin per policy in each Insurance Type?
- (5 Points) - Create 5 separate tabs label the tabs 3a-Auto, 3b-Disability, 3c-Home, 3d-Life, 3e-Professional. Each tab will illustrate a respective insurance type. Each tab will illustrate a pivot table and pivot chart. In summary -5 insurance types therefore 5 separate tabs. Tasks:
- Filter by the respective insurance type as described above
- Pivot by sum of sales and by state
- Use value filter to display the top 10 sales
- Create a Pivot Chart in the same tab (Clustered Column Chart)
- (2 Points) - Analyze all the information you have gathered or created in the preceding requirements. Create a word document and import at least three pivot charts. Provide a written analysis (few sentences) to describe any trends or takeaways that you have identified.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started