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 1 BLUE RIDGE MANUFACTURING Sales Information Product and Customer Size Statistics Sales in Units by Customer Account Size Large Medium Small: Total Towel: Regular 27,250 16,600 10,550 54,400 Mid-Size 36,640 18,552 10,308 65,500 Hand 35,880 19,966 95,954 151,800 Special 480 3,426 594 4,500 Number of Units Sold 100,250 58,544 117,406 276,200 Number of Units Embroidered 5,959 6,490 29,394 41,842 Number of Units Dyed 20,536 9,935 12,328 42,798 Sales Volume Revenue $308,762 $183,744 $318,024 $810,530 Number of Orders Received Number of Shipments Made Number of Invoices Sent 133 845 5,130 6,108 147 923 5,431 6,501 112 754 4,737 5,603 Accounts with Balance >60 Days 1 11 122 134 TABLE 2 BLUE RIDGE MANUFACTURING Unit Cost Information Line 1 Direct Manufacturing Costs Per Unit Sales Unit Quantity Price Material Labor Overhead Cost Towels: Regular 54,400 $3.60 $0.60 $0.37 $0.22 $1.19 Mid-Size 65,500 3.20 0.50 0.33 0.20 1.03 Hand 151,800 2.55 0.39 0.31 0.19 0.89 Special 4,500 4.00 0.67 0.48 0.29 1.44 Line 2 Direct Costs of Customizing Per Unit Quantity Cost Material Labor Overhead Total Inking (based on passes) 552,400 . $0.0030 $0.0045 $0.0742 $0.0817 Dyeing 42,798 $0.11 0.0000 0.1100 Embroidery 41,842 0.0026 0.1750 1.0994 1.2770 Direct Labor Wage Rate: $9.00 (Including Fringes) Inking requires one pass for each color used; average two colors per towel (i.e., 2 per unit), and is used on all towels. TABLE 3 BLUE RIDGE MANUFACTURING Selling and Administrative Costs and Activities Costs Incurred in Each Function (Shipping, Sales, Marketing) Directly Assigned To: Total Total Shipping Sales Marketing Other Assigned Unassigned Administration $170,000 Selling 155,000 $325,000 $ 17,000 $ 37,400 15.500 117.800 $ 32,500 $155,200 $20,400 $56,100 $130,900 $39,100* 9,300 $29.700 12,400 $68,500 155,000 $285,900 $39.100 Each function is used for the Following Activities Percentage of: Invoicing Selling and Administrative Activities: Entering Purchase Orders Commissions Shipping Activities Cost to Make Sales Calls Shipping Sales Marketing Other 55 10 10 65 15 20 30 10 Checking Credit Samples, Catalog Info. Special Handling Charges Distribution Management Marketing, by Customer Type: -10 550 10 10 Advertising/Promotion 30 Marketing 15 50 Administrative Office Support 20 Licenses, Fees 5 100 100 100 100 *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 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/Promotion Marketing Administrative Office Support Licenses, Fees 120% to medium-sized customers; 80% to small-sized customers. 225% to medium-sized customers; 75% to small-sized customers. Excluding Specials "Excluding Specials Cost Driver 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.