Required information Excel Analytics 10-67: Customer Profitability - Data Analysis and Data Visualization [The following information applles to the questions displayed below] Stark Remodeling Center (SRC) is a home renovation showroom that caters primarily to contractors. Customers place orders at SRC, either in person or online, and the merchandise is packaged in standard-sized boxes and delivered to the home or job site. (The delivery service SRC uses charges based on the number of boxes shipped.) SRC sets prices so that they cover the order fulfiliment and delivery costs, which SRC estimates to be 15 percent of the total sales value of all orders (order value). Currently, the financial system at SRC calculates what is called "customer margin" by subtracting two items from the total customer's order value. First, an allowance for cost of goods sold is deducted. This allowance is 75 percent of the order value and approximates the overall experience at SRC. Second, the system deducts an additional 15 percent to reflect the order and fulfillment costs for the customer. A summer intern working with the cost analysis group at SRC has studied the delivery service using data from the last quarter. The intern identified six major activities, estimated costs, and recommended cost drivers for these activities to use in a customer profitability analysis. The intern's findings follow: To illustrate the proposed model, the intern chose two SRC customers who ordered a similar value of products in the last quarter: Eldridge Renovations and Horton Contracting. Data for the last quarter for the two customers follow: Excel Analytics PR 10-67: Customer Profitability - Data Analysis and Data Visualization Required a. What would be reported customer margin for each of the two customers under the current customer profitability system? b. Suppose the intern's ABC system is adopted to measure customer profitability. What would be reported customer margin for eac of the two customers under the intern's proposed ABC customer profitability system? Note: The ABC system would continue to subtract 75 percent of the order value to reflect an allowance for cost of goods sold. c. Prepare graphical evidence for the management at SRC Illustrating how to improve profitability as suggested by the results of the intern's ABC system. The relevant data has been included within the following file: Click here to access the Assignment 10-67 Student File. Assignment Steps: 1. Download the Assignment 10-67 Student File and open it within Microsoft Excel. 2. Within cell B4 enter the formula =B375%. Copy this formula and paste within cell C4. This Excel formula multiplies the allowance percentage for Cost of Goods Sold by the total order value for each customer. 3. Within cell B5 enter the formula =8315%. Copy this formula and paste within cell C5. This Excel formula multiplies the allowance percentage for order and fulfilment costs by the total order value for each customer 4. Within cell B6 enter the formula = B3-B4-B5. Copy this formula and paste within cell C6. 5. Within cell B7 enter the formula =B6/B3. Copy this formula and paste within cell C7. 6. Within cell B12 enter the formula =B1175%. Copy this formula and paste within cell C12. 7. Within cell B14 enter the formula =(37500/3000)15. Copy this formula and paste within cell C14. Within the formula in cell C14 replace " 15 with " 5 " to reflect the number of orders for Horton Contractors. This Excel formula multiplies the allocation rate for verifying orders (based on the number of orders) by the number of orders for each customer. 8. Within cell B15 enter the formula =(82500/37500)72. Copy this formula and paste within cell C15. Within the formula in cell C15 replace "72" with "70" to reflect the number of items for Horton Contractors. This Excel formula multiplies the allocation rate for picking orders (based on the number of items) by the number of items for each customer. 9. Within cell B 16 enter the formula =(53460/4860)40. Copy this formula and paste within cell C16. Within the formula in cell C16 replace " 40 " with "14" to reflect the number of boxes for Horton Contractors. This Excel formula multiplies the allocation rate for delivering orders (based on the number of boxes) by the number of boxes for each customer. 10. Within cell 817 enter the formula =(27360/3600)16. Copy this formula and paste within cell C17. Within the formula in cell C17 replace 16 with 6 " to reflect the number of invoices for Horton Contractors. This Excel formula multiplies the allocation rate for invoicing orders (based on the number of invoices) by the number of invoices for each customer. 11. Within cell B18 enter the formula (63180/4860)40. Copy this formula and paste within cell C18. Within the formula in cell C18 replace 40 with " 14 to reflect the number of boxes for Horton Contractors. for each customer 11. Within cell B18 enter the formula =(63180/4860)40. Copy this formula and paste within cell C18. Within the formula in cell C18 replace 40with 141 to reflect the number of boves for Horton Contractors. This Excel formula multiplies the allocation rate for leasing the warehouse (based on the number of boxes) by the number of boxes for each customet. 12. Within cell B19 enter the formula =(96000/2400000) B11. Copy this formula and paste within cell C19. This Excel formula multiplies the ollocation rate for administrative costs (besed on the order value) by the total order value for each customer. 13. Within cell B20 enter the formula = SUM(B14:819). Copy this formula and paste within cell C20. 14. Within cell B21 enter the formula =811B12B20. Copy this formula and paste within cell C21 15. Within cell 822 enter the formula =B21/B11, Copy this formula and paste within cell C22 16. Within cell B23 enter the formula =B14/B$11. Copy this formula and paste within cell C23. Copy the range B23:C23 and paste within the range 824:C28. 17. Highilght the range A2:C2, hold CTRL, highlight the range A6:C6, continue to hold CTRL, highlight the range A21:C21, choose Insert Column or Bar Chart from the Charts group of the insert menu, and select Clustered Column. 18. Click the chart title and type Stark Remodelling Center - Customer Margin (Current System vs. ABC) as the new title. 19. Choose Switch Row/Column from the Data group of the Chart Design menu. 20. Click the background of the chart and drag it so that the top-left corner is located within cell D1. 21. Highilght the ronge A2.C2, hold CTRL, highlight the range A14:C19, choose insert Column or Bar Chart from the Charts group of the Insert menu, and select Clustered Column. 22. Click the chart title and type Stark Remodeling Center - Fulfillment and Delivery Cost by Activity as the new tite. 23. Click the background of the chart and drag it so that the top-left corner is located within cell D15. 24. Save your progress by choosing Save As from the File menu. You may now answer all questions for Assignment 10-67. Excel Analytics PR 10-67: Submit Your Excel File Please upload your completed Excel file for this assignment. upload a response file (15MB max)