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.

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 TABLE3 BLUE RIDGE MANUFACTURING Selling and Administrative Costs and Activities Costs Incurred in Each Funetion (Shipping, Sales, Marketing) Directly Assigned To: Total Total Shipping MaktngOtherAssigned Unassigned dministration $170,000 17,000S lling 37,400 $20,400$56,100$130,900$39,100 155,000155001178009300 12.400155,000 Each funetion is used for the Following Aetivities Percentage of: Sales Shipping Marketing Other 10 Entering Purchase Orders Commissions Shipping Activities Invoicing Cost to Make Sales Calls Checking Credit Samples, Catalog Info Special Handling Charges Distribution Management Marketing, by Customer Type Advertising/Promotiorn Marketing Administrative Office Support Licenses, Fees 10 15 20 10 10 65 30 10 10 30 10 15 50 20 Note that $39,100 of the SG&A cost was not directly assigned. This amount represents the facility-sustaining activity cost TABLE 4 BLUE RIDGE MANUFACTURING Activities and Cost drivers Cost Drivers for Allocating Costs of Activities to Customer Groups (Large, Medium, Small) Activity' Cost Driver Entering Purchase Orders Commissions Shipping Activities nvoicing Cost to Make Sales Calls Checking Credit Samples, Catalog Info. Special Handling Charges Distribution Management Marketing, by Customer Type Advertising/Promotion Marketing Administrative Office Support Licenses, Fees 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 Dollars Sales Dollars Management Estimate Number of Units Sold Number of Units Sold Sales Dollars with Medium Customers "20% to medium-sized customers; 80% to small-sized customers. 225% to medium-sized customers; 75% to small-sized customers. Excluding Specials 'Excluding Specials