Develop a spreadsheet analysis which can be used to assess the profitability of the three customer groups of Blue Ridge - large, medium, and small customer account sizes. Use the information in Tables 1 through 4 to trace and allocate the costs necessary for the analysis. Please help me to develop a spreadsheet analysis and provide formulas used to calculate findings.

3. Develop a spreadsheet analysis which can be used to assess the profitability of the three customer groups of Blue Ridge -large, medium and small customer account sizes. Use the information in Tables 1-4 to trace and allocate the costs necessary for the analysis. TABLE I BLUE RIDGE MANUFACTURING Sales Information Product and Customer Size Statistics Sales in Units by Customer Account Size Medium Total Towel: Regular Mid-Size Hand 27,250 36,640 35,880 480 16,600 18,552 19,966 3,426 10,550 10,308 95,954 54,400 65,500 151,800 4,500 Number of Units Sold Number of Units Embroidered Number of Units Dyecd Sales Volume Revenue Number of Orders Received Number of Shipments Made Number of Invoices Sent Accounts with Balance-60 Days 17,406 29,394 12,328 $318,024 5,130 5,431 4,737 100,250 5,959 20,536 S308,762 58,544 9,935 $183,744 845 923 276,200 41,842 42,798 $810,530 6,108 6,501 5,603 TABLE 2 BLUE RIDGE MANUFACTURING Unit Cost Information Line 1 Direct Manufacturing Costs Per Unit Sales Price Material Unit uantit Overhead $0.37 54,400 65,500 151,800 $0.22 0.20 $3.60 $0.60 Regular Mid-Size Hand owe 0.39 0.67 0.31 0.48 2.55 0.89 0.29 Line 2 Direct Costs of Customizing Per Unit Overhead Total Material $0.0030 0.0026 uanti Cost Labor Inking (based on passes) 552,400 42,798 41,842 $0.0045 $0.0742 $0.0817 0.0000 0.1100 1.0994 1.2770 ei Embroidery 0.1750 Direct Labor Wage Rate: $9.00 (Including Fringes) Inking requires one pass for each color used; average two colors per towel (i.e., 2 er unit), and is used on all towels TABLE 3 BLUE RIDGE MANUFACTURING Selling and Administrative Costs and Activities TABLE 4 BLUE RIDGE MANUFACTURING Activities and Cost drivers Cost Drivers for Allocating Costs of Activities to Customer Groups (Large, Medium, Small) Costs Incurred in Each Function (Shipping, Sales, Marketing) Cost Driver Direetly Assigned To Number of Orders Sales Dollars with Medium Customers Number of Shipments Number of Invoices Sales Dollars with Large Customers Percent Accounts>60 Days Sales Dollars Management Estimate Sales Dotlars Sales Dollars Management Estimate Number of Units Sold Number of Units Sold Sales Dollars with Medium Customers Entering Purchase Ordens Total Shiping SalesMarketingOtherssignedUnassigned Shipping Activities $170,000 S 17,000 $ 37,400 $20,400$56,100130900$39,100 -155,000 -15,500 "uz800 9300 Cost to Make Sales Calls Cbecking Credit Samples, Catalog Info. Special Handling Charges Distribution Management Marketing, by Customer Type Advertising/Promotion Marketing Administrative Office Support Licenses, Fees 2 400 155,000 Percentage of: Each fanction is used for the Following Activities Entering Purchase Orders Shipping Activities SalesMarketing 15 20 10 30 Cost to Make Sales Calls Checking Credit Samples, Catalog Info. Special Handling Charges 10 10 30 120% to medium-sized customers; g096 to small-sized customers. 25% to medium-sized customers, 75% to 8mall-sized customers. 'Excluding Specials 'Excluding Specials 10 Markcting, by Customer Type 15 50 Administrative Offlce Support Licenses, Fees Note that $39,100 of the SG&A cost was not directly assigned. This smount repeesents the facility-sustaiing activity cost. 3. Develop a spreadsheet analysis which can be used to assess the profitability of the three customer groups of Blue Ridge -large, medium and small customer account sizes. Use the information in Tables 1-4 to trace and allocate the costs necessary for the analysis. TABLE I BLUE RIDGE MANUFACTURING Sales Information Product and Customer Size Statistics Sales in Units by Customer Account Size Medium Total Towel: Regular Mid-Size Hand 27,250 36,640 35,880 480 16,600 18,552 19,966 3,426 10,550 10,308 95,954 54,400 65,500 151,800 4,500 Number of Units Sold Number of Units Embroidered Number of Units Dyecd Sales Volume Revenue Number of Orders Received Number of Shipments Made Number of Invoices Sent Accounts with Balance-60 Days 17,406 29,394 12,328 $318,024 5,130 5,431 4,737 100,250 5,959 20,536 S308,762 58,544 9,935 $183,744 845 923 276,200 41,842 42,798 $810,530 6,108 6,501 5,603 TABLE 2 BLUE RIDGE MANUFACTURING Unit Cost Information Line 1 Direct Manufacturing Costs Per Unit Sales Price Material Unit uantit Overhead $0.37 54,400 65,500 151,800 $0.22 0.20 $3.60 $0.60 Regular Mid-Size Hand owe 0.39 0.67 0.31 0.48 2.55 0.89 0.29 Line 2 Direct Costs of Customizing Per Unit Overhead Total Material $0.0030 0.0026 uanti Cost Labor Inking (based on passes) 552,400 42,798 41,842 $0.0045 $0.0742 $0.0817 0.0000 0.1100 1.0994 1.2770 ei Embroidery 0.1750 Direct Labor Wage Rate: $9.00 (Including Fringes) Inking requires one pass for each color used; average two colors per towel (i.e., 2 er unit), and is used on all towels TABLE 3 BLUE RIDGE MANUFACTURING Selling and Administrative Costs and Activities TABLE 4 BLUE RIDGE MANUFACTURING Activities and Cost drivers Cost Drivers for Allocating Costs of Activities to Customer Groups (Large, Medium, Small) Costs Incurred in Each Function (Shipping, Sales, Marketing) Cost Driver Direetly Assigned To Number of Orders Sales Dollars with Medium Customers Number of Shipments Number of Invoices Sales Dollars with Large Customers Percent Accounts>60 Days Sales Dollars Management Estimate Sales Dotlars Sales Dollars Management Estimate Number of Units Sold Number of Units Sold Sales Dollars with Medium Customers Entering Purchase Ordens Total Shiping SalesMarketingOtherssignedUnassigned Shipping Activities $170,000 S 17,000 $ 37,400 $20,400$56,100130900$39,100 -155,000 -15,500 "uz800 9300 Cost to Make Sales Calls Cbecking Credit Samples, Catalog Info. Special Handling Charges Distribution Management Marketing, by Customer Type Advertising/Promotion Marketing Administrative Office Support Licenses, Fees 2 400 155,000 Percentage of: Each fanction is used for the Following Activities Entering Purchase Orders Shipping Activities SalesMarketing 15 20 10 30 Cost to Make Sales Calls Checking Credit Samples, Catalog Info. Special Handling Charges 10 10 30 120% to medium-sized customers; g096 to small-sized customers. 25% to medium-sized customers, 75% to 8mall-sized customers. 'Excluding Specials 'Excluding Specials 10 Markcting, by Customer Type 15 50 Administrative Offlce Support Licenses, Fees Note that $39,100 of the SG&A cost was not directly assigned. This smount repeesents the facility-sustaiing activity cost