Requirement 2:Using the Excel template provided, complete the following tabs using only formulas (unless otherwise noted). In this assignment you are using formulas that link different worksheets. For example, something on tab A times something on tab B to result in the answer on tab B. B: First stage allocations (the cost column should be typed, the rest is formulas)D: second stage allocation. Make sure your labels are correct (dollars versus percentages)F: Customer analysis. Requirement 3:In a memo, summarize the results of your customer analysis from requirement 2. Assume that the excel file is not be attached to the memo and that you should summarize information in the memo using data visualization (tables, graphs, charts etc)Make sure you answer the following question: what action would you recommend Carolina Creations Marketing department take with respect to each of the customers.

A B C D E Customer Analysis WJ Southeast Furniture Mart Indianapolis Interiors San Diego Interiors 5 Sales $ 437,500 $ 131,250 $ 87,500 6 Cost of goods sold 250,000 75,000 50,000 7 Gross margin $ 187,500 $ 56,250 $ 37,500 8 Activity Cost Pool: 10 Sales Calls 11 Contract Negotiations 12 Order Processing 13 Warehouse Activities 14 New Customer 15 Shipping 16 Post-Sale Support 17 Other 18 Total Operating Expenses Allocated 19 20 Operating Income 21 22 23 yellow cells formula only 24 link formula only as needed 25Sample Customers Students do NOT edit this tab Southeast Furniture Mart Indianapolis Interiors San Diego Interiors Sales S 437,500 131,250 $ 87,500 6 Cost of goods sold 250,000 75,000 50,000 Gross margin S 187,500 $ 56,250 $ 37,500 8 9 Driver Base Driver Quantity Driver Quantity Driver Quantity 10 # of sales calls 14 18 8 11 Negotiation hours 4 13 10 12 # of orders 19 62 3 13 New Customer? no no yes 14 Pounds shipped x Distance in miles 1,877,000 4,862,000 4,692,000 15 Suport Hours 7 29 31 16 17 18 19A B econd Stage Allocation Rates This column is formulas only ctivity Activity Cost Driver Base Driver Base Quan Rate per Unit of Driver Base Making Sales Calls # of sales calls 215 Negotiating Contracts # of hours of negotiation 200 Processing Orders # of orders 600 Warehousing Activities Cost of Goods Sold $ 1,779,520.00 New Customer Development # of orders 17 Shipping Pounds shipped x Distance in miles 87,000,000 Post-Sale Customer Support # of support hours 1,800 Other Amount of sales dollars 3,114,160 TOTAL Note this rates should be % 6 yellow cells = formula only 8 19 20 21A29 X V fx A B C D E F G H 1 Carolina Creations' Cost Drivers and Quantities Students do NOT edit this tal 3 Activity Cost Driver Driver Quantity 4 Making Sales Calls # of sales calls 215 5 Negotiating Contracts # of hours of negotiation 200 6 Processing Orders # of orders 600 7 Warehousing Activities Cost of Goods Sold $ 1,779,520.00 8 New Customer Development # of orders 17 9 Shipping Pounds shipped x Distance in miles 87,000,000 10 Post-Sale Customer Support # of support hours 1,800 11 Other Amount of sales dollars 3,114,160 12 13 Note: New customer development is a 0,1 variable. The quantity for a specific customer will be 1 if the customer 14 is a new customer (it will be charged the new customer development rate) and 0 if it is not a new customer 15 (it will not be charged the new customer development rate). 16 17 18 19 20 21 22 23 24 25 26A B C D First Stage Allocations E F G H K ALLOCATION Post-Sale Making Sales Negotiating Processing Warehousing New Customer Customer Cost Calls Contracts Orders Activities Development Shipping Service Other Tot Administration Wages 368,000 18400 18400 110400 18400 36800 36800 55200 73600 Advertising 71,852 14370 14370 43111 Building Depreciation 48,600 36450 12150 Maintenance 38,718 25167 13551 Office Equipment Depreciation 8,400 420 420 3360 1680 420 2100 Promotional Materials 58,000 29000 2900 20300 5800 Salesperson Commissions and Wages 361,416 234920 36142 54212 18071 18071 2 Shipping 42,893 42893 5349 1528 3 Taxes and Insurance 15,283 8406 4 Travel 92,788 46394 4639 37115 4639 325 5 Utilities and Fuel 6,491 1623 1298 649 2596 S 152,817 22923 122254 7641 15 6 Warehouse Wages 11548 17 Other S 11,548 85042 84455 185897 127 18 TOTAL S 1,276,806 345,128 62,501 136683 213654 163447 19 20 21 yellow cells Enter formulas only 22 blue cells Type data 23 24Creations' First-Stage Allocation Percentages Students do NOT edit this tab Making Negotiating Processing Warehousing New Customer Post-Sale Sales Calls Contracts Orders Activities Development Shipping Customer Service Other Total stration Wages 0.05 0.05 0.30 0.05 0.10 0.10 0.15 0.20 100% sing 0.20 0.20 0.60 100% g Depreciation 0.75 0.25 100% nance 0.65 0.35 100% Equipment Depreciation 0.05 0.05 0.40 0.20 0.05 0.25 100% tional Materials 0.50 0.05 0.35 0.10 100% erson Commissions and Wages 0.65 0.10 0.15 0.05 0.05 100% ng 1.00 100% and Insurance 0.55 0.35 0.10 100% 0.50 0.05 0.40 0.05 100% 0.25 0.20 0.10 0.05 0.40 100% es and Fuel house Wages 0.15 0.80 0.05 100% 1.00 100%