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