Question: Please post formulas if there are any, there is more excel entries, I will just modify the formula to fit the rest. Thank you! 2.

Please post formulas if there are any, there is more excel entries, I will just modify the formula to fit the rest. Thank you! 2. Calculate the variable cost and contribution margin for each policy

sold. 3. Total the sales revenue, variable cost, and contribution margin for

each Insurance Type. a. Which Insurance Type had the highest total contribution

margin? b. Which Insurance Type had the lowest total contribution margin? c.

Please post formulas if there are any, there is more excel entries, I will just modify the formula to fit the rest. Thank you!

2. Calculate the variable cost and contribution margin for each policy sold. 3. Total the sales revenue, variable cost, and contribution margin for each Insurance Type. a. Which Insurance Type had the highest total contribution margin? b. Which Insurance Type had the lowest total contribution margin? c. How many insurance policies were sold in each Insurance Type? d. What is the average contribution margin per policy in each Insurance Type? 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. 5. Calculate the contribution margin ratio for each state. Rank the states from the highest contribution margin ratio to the lowest contribution margin ratio. Which states had a contribution margin ratio greater than 75%? 6. Within each region, what was the most profitable state in the most recent year, as measured by the contribution margin ratio? The least most profitable state in each region? 7. Analyze all the information you have gathered or created in the preceding requirements. What trends or takeaways do you see? Explain. Contribution Margin Tom Country USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA Region State Midwest TX Southeast TN New England CT New England MA Southeast GA West UT West NV West WA North Central MO Southeast MS North Central MI New England ME West MT Northeast DE New England ME Southeast TN Midwest KS Northeast OH New England MA Midwest NE Northeast VA North Central KY Northeast MD Southeast FL Midwest OK North Central IA Midwest TX Northeast DC North Central MN New England MA Southeast GA Northeast PA Southeast MS Northeast PA Northeast NJ Variable State Cost Variable Salesperson Insurance Type Sales Date of Sale Invoice No Type Percentage Cost Auto 934.79 1/1/2017 A-2017-000010 TX-Auto Jennifer Auto 570.51 1/1/2017 A-2017-000012 TN-Auto Crissy Auto 398.71 1/1/2017 A-2017-000016 CT-Auto Crissy Auto 887.79 1/1/2017 A-2017-000017 MA-Auto John Auto 500.21 1/1/2017 A-2017-000028 GA-Auto Kellie Auto 584.16 1/1/2017 A-2017-000029 UT-Auto Kellie Auto 433.22 1/1/2017 A-2017-000033 NV-Auto Peter Auto 958.92 1/1/2017 A-2017-000036 WA-Auto Mario Auto 411.77 1/1/2017 A-2017-000037 MO-Auto Frank Auto 517.65 1/1/2017 A-2017-000041 MS-Auto Nicole Auto 507.17 1/1/2017 A-2017-000046 MI-Auto Sara Auto 416.7 1/1/2017 A-2017-000050 ME-Auto Peter Auto 545.06 1/1/2017 A-2017-000059 MT-Auto Fred Auto 549.32 1/1/2017 A-2017-000061 DE-Auto Sara Auto 755.19 1/1/2017 A-2017-000065 ME-Auto Jennifer Auto 710.18 1/1/2017 A-2017-000078 TN-Auto Auto 556.41 1/1/2017 A-2017-000089 KS-Auto Mike Auto 967.44 1/1/2017 A-2017-000096 OH-Auto Crissy Auto 517.23 1/1/2017 A-2017-000099 MA-Auto Dave Auto 1143.34 1/1/2017 A-2017-000107 NE-Auto Matt Auto 1045.19 1/1/2017 A-2017-000113 VA-Auto Mario Auto 568.39 1/1/2017 A-2017-000114 KY-Auto Fred Auto 646.64 1/1/2017 A-2017-000115 MD-Auto John Auto 524.88 1/1/2017 A-2017-000117 FL-Auto Tom Auto 560.91 1/1/2017 A-2017-000122 OK-Auto Nicole Auto 860.73 1/1/2017 A-2017-000124 IA-Auto Tom Auto 580.86 1/1/2017 A-2017-000128 TX-Auto Matt Auto 835.25 1/1/2017 A-2017-000139 DC-Auto Nicole Auto 432.02 1/1/2017 A-2017-000142 MN-Auto Crissy Auto 379.12 1/1/2017 A-2017-000145 MA-Auto John Auto 1191.58 1/1/2017 A-2017-000146 GA-Auto Mike Auto 502.27 1/1/2017 A-2017-000153 PA-Auto Frank Auto 654.81 1/1/2017 A-2017-000154 MS-Auto Mike Auto 1029.76 1/1/2017 A-2017-000166 PA-Auto Fred Auto 651.63 1/1/2017 A-2017-000168 NJ-Auto Tom USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA State Type AL-Auto AL-Disability AL-Home AL-Life AL-Professiona AR-Auto AR-Disability AR-Home AR-Life AR-Profession AZ-Auto AZ-Disability AZ-Home AZ-Life AZ-Professiona CA-Auto CA-Disability CA-Home CA-Life CA-Profession; CO-Auto CO-Disability CO-Home CO-Life CO-Profession CT-Auto CT-Disability CT-Home CT-Life CT-Professiona DC-Auto DC-Disability DC-Home DC-Life DC-Profession: DE-Auto DE-Disability Variable Cos 0.62 0.3 0.23 0.09 0.29 0.58 0.27 0.28 0.11 0.32 0.62 0.29 0.19 0.12 0.32 0.57 0.31 0.19 0.12 0.17 0.6 0.29 0.24 0.12 0.29 0.55 0.32 0.22 0.13 0.28 0.5 0.26 0.25 0.11 0.18 0.39 0.27 2. Calculate the variable cost and contribution margin for each policy sold. 3. Total the sales revenue, variable cost, and contribution margin for each Insurance Type. a. Which Insurance Type had the highest total contribution margin? b. Which Insurance Type had the lowest total contribution margin? c. How many insurance policies were sold in each Insurance Type? d. What is the average contribution margin per policy in each Insurance Type? 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. 5. Calculate the contribution margin ratio for each state. Rank the states from the highest contribution margin ratio to the lowest contribution margin ratio. Which states had a contribution margin ratio greater than 75%? 6. Within each region, what was the most profitable state in the most recent year, as measured by the contribution margin ratio? The least most profitable state in each region? 7. Analyze all the information you have gathered or created in the preceding requirements. What trends or takeaways do you see? Explain. Contribution Margin Tom Country USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA Region State Midwest TX Southeast TN New England CT New England MA Southeast GA West UT West NV West WA North Central MO Southeast MS North Central MI New England ME West MT Northeast DE New England ME Southeast TN Midwest KS Northeast OH New England MA Midwest NE Northeast VA North Central KY Northeast MD Southeast FL Midwest OK North Central IA Midwest TX Northeast DC North Central MN New England MA Southeast GA Northeast PA Southeast MS Northeast PA Northeast NJ Variable State Cost Variable Salesperson Insurance Type Sales Date of Sale Invoice No Type Percentage Cost Auto 934.79 1/1/2017 A-2017-000010 TX-Auto Jennifer Auto 570.51 1/1/2017 A-2017-000012 TN-Auto Crissy Auto 398.71 1/1/2017 A-2017-000016 CT-Auto Crissy Auto 887.79 1/1/2017 A-2017-000017 MA-Auto John Auto 500.21 1/1/2017 A-2017-000028 GA-Auto Kellie Auto 584.16 1/1/2017 A-2017-000029 UT-Auto Kellie Auto 433.22 1/1/2017 A-2017-000033 NV-Auto Peter Auto 958.92 1/1/2017 A-2017-000036 WA-Auto Mario Auto 411.77 1/1/2017 A-2017-000037 MO-Auto Frank Auto 517.65 1/1/2017 A-2017-000041 MS-Auto Nicole Auto 507.17 1/1/2017 A-2017-000046 MI-Auto Sara Auto 416.7 1/1/2017 A-2017-000050 ME-Auto Peter Auto 545.06 1/1/2017 A-2017-000059 MT-Auto Fred Auto 549.32 1/1/2017 A-2017-000061 DE-Auto Sara Auto 755.19 1/1/2017 A-2017-000065 ME-Auto Jennifer Auto 710.18 1/1/2017 A-2017-000078 TN-Auto Auto 556.41 1/1/2017 A-2017-000089 KS-Auto Mike Auto 967.44 1/1/2017 A-2017-000096 OH-Auto Crissy Auto 517.23 1/1/2017 A-2017-000099 MA-Auto Dave Auto 1143.34 1/1/2017 A-2017-000107 NE-Auto Matt Auto 1045.19 1/1/2017 A-2017-000113 VA-Auto Mario Auto 568.39 1/1/2017 A-2017-000114 KY-Auto Fred Auto 646.64 1/1/2017 A-2017-000115 MD-Auto John Auto 524.88 1/1/2017 A-2017-000117 FL-Auto Tom Auto 560.91 1/1/2017 A-2017-000122 OK-Auto Nicole Auto 860.73 1/1/2017 A-2017-000124 IA-Auto Tom Auto 580.86 1/1/2017 A-2017-000128 TX-Auto Matt Auto 835.25 1/1/2017 A-2017-000139 DC-Auto Nicole Auto 432.02 1/1/2017 A-2017-000142 MN-Auto Crissy Auto 379.12 1/1/2017 A-2017-000145 MA-Auto John Auto 1191.58 1/1/2017 A-2017-000146 GA-Auto Mike Auto 502.27 1/1/2017 A-2017-000153 PA-Auto Frank Auto 654.81 1/1/2017 A-2017-000154 MS-Auto Mike Auto 1029.76 1/1/2017 A-2017-000166 PA-Auto Fred Auto 651.63 1/1/2017 A-2017-000168 NJ-Auto Tom USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA State Type AL-Auto AL-Disability AL-Home AL-Life AL-Professiona AR-Auto AR-Disability AR-Home AR-Life AR-Profession AZ-Auto AZ-Disability AZ-Home AZ-Life AZ-Professiona CA-Auto CA-Disability CA-Home CA-Life CA-Profession; CO-Auto CO-Disability CO-Home CO-Life CO-Profession CT-Auto CT-Disability CT-Home CT-Life CT-Professiona DC-Auto DC-Disability DC-Home DC-Life DC-Profession: DE-Auto DE-Disability Variable Cos 0.62 0.3 0.23 0.09 0.29 0.58 0.27 0.28 0.11 0.32 0.62 0.29 0.19 0.12 0.32 0.57 0.31 0.19 0.12 0.17 0.6 0.29 0.24 0.12 0.29 0.55 0.32 0.22 0.13 0.28 0.5 0.26 0.25 0.11 0.18 0.39 0.27

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Accounting Questions!

Can someone help me with these problems? File Home Insert Draw Page Layout Formulas Data Review View Developer Help Power Pivot Share X Arial 12 AI ab General $ %) Conditional Formatting Format as...

I have this Accounts assignment that needs to be done. Need help on that Statement of individual work Individual work required This project is designed to assure that all students, individually, have...

accounting 2 please show steps and formuals in excel !!! please help!!!! Draw Formulas Insert Data Review View Page Layout Home Tell me 12 ' ' Arial = Wrap Text X LD * V Paste ov Merge & cer a. Av E9...

Boyd Corporation is a manufacturer that uses job-order costing. On January 1, 2024, the 4 company's inventory balances were as follows: 5. Raw Materlals Inventory Work-In-Process Inventory Finished...

I got this big assignmnet for Quantitative methods for business i really need help Azimi, Hamida - azihy004 AH Share Comments Delete this page before submission Q-Constructions - building your future...

help asap need formulas woth excel layout 10:30 X Comprehensive Project 1 Template v1 XLSX - 40 KB Photo Once ve Breve Net Use the combuden waren eto poche con poetsen DATA malam per Format percent,...

can someone please give me the right answer instead of talking about church M N 0 5 6 1 Job Order Costing Using Excel to calculate a predetermined overhead allocation rate, journalize and post...

Through the information provided above could you help me answer these requirements, please! THANK YOU SOOO MUCH!!!! Boyd Corporation is a manufacturer that uses job-order costing. On January 1, 2024,...

Enter the beginning balances for the inventories in the T-accounts. Assume the rest of the accounts have beginning balances of zero. Post the journal entries to T- accounts. T-account balances will...

Using the attached pdf document complete each of the four tabs in the Excel Document. 101-131, Management Accounting Comprehensive Problem 2 Narrative Felicia Davenport would like to thank you for...

If the Federal Reserve decides to sterilize the foreign-exchange market intervention described in Problem, show the impact on the Feds balance sheet. What would the overall impact be on the monetary...

Aim is to transmit water from the source to the end point. Option is to use gravity pipeline system (may use tools i.e. pump, air valve, pressure reducer, etc if required!). Problem The following...

Question 1 4 ( 1 point ) Find the derivative o f the given function. w ( x ) = s i n ( 1 x ) a w ' ( x ) = - c o s ( 1 x ) b w ' ( x ) = - 1 x 2 c o s ( 1 x ) c w ' ( x ) = 1 x 2 c o s ( 1 x ) d w '...

The taxpayer is the sole owner employee of a small corporation that prepares tax returns. Before paying himself any salary or dividends or taking fringe benefits, the corporation has taxable income...

The US-Haul Truck Rental firm has accumulated extra trucks at three of its truck leasing outlets, as shown: Leasing Outlet Extra Trucks 1. ...........Atlanta 70 2. ............St. Louis 115 3....

Fechter Corporation had the following stockholders' equity accounts on January 1, 2019: Common Stock ($5 par) $500,000, Paid-in Capital in Excess of Par-Common Stock $200,000, and Retained Earnings...

Suppose a taxpayer invests $ 100,000 in a partnership. The taxpayer faces a personal tax rate of 70% and a tax rate on capital gains of 28%. In the first year, the partnership spends the entire $...

The following financial information is available for Flintlock Corporation. (in millions) 2019 2018 Average common stockholders' equity................$2,532...........$2,591 Dividends declared for...

Create a 200 word summary on the Juvenile Justice System than and today. Describe The Juvenile system and provide a generalizable definition. Highlight the issues in the Juvenile system and why this...

Prepare a trial balance from the ledger accounts.

Correct entries using the correcting entry method.

Arrange the following steps in the posting process in proper order: a. Write the ledger account number in the Post. Ref. column of the journal. b. Write the amount of the transaction. c. Write the...