Can I get help with developing a spreadsheet as listed below .What formulas will be used and how do I do it .

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 10 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 127, 406 276, 204 Number of Units Embroidered 5. 959 5. 490 29, 394 41, 842 Number of Units Dyed 20. 536 3. 935 12. 328 42. 798 Sales Volume Revenue $308. 762 $183, 744 $318. 024 $810, 530 Number of Orders Received 133 845 5, 130 5. 108 Number of Shipments Made 147 923 5. 431 6. 501 Number of Invoices Sent* 112 754 4 . 737 3, 603 Accounts with Balance >60 Days 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 50. 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 Special 4.500 100 0 . 89 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 50. 0742 $0. 0817 Dyeing\\ 42, 798 50. 11 0. 0000 0. 1 100 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 Unassign Total Shipping Sales Marketing Other Assigned ed Administra tion $170,000 $ 17,000 $ 37,400 $20,400 $56,100 $130,900 $39,100* Selling 155,000 15,500 117,800 9,300 12,400 155,000 $325,000 $ 32,500 $155,200 $29,700 $68,500 $285,900 $39,100 Each Percentage of: function is used for the Following Activities Selling Shipping Sales Marketing Other and Administra tive Activities: Entering 5! 10 Purchase Orders Commissi 10 ons Shipping 65 15 Activities Invoicing 20 Cost to 30 Make Sales Calls Checking 10 Credit Samples, 5 10 Catalog Info. Special 5 5 Handling Charges Distributio 10 10 n Managem ent Marketing, 15 by CustomerType Advertising 30 g/ Promotion U Marketing 15 50 5 Administra 20 live Office* Support Licenses , 5 Fees 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 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 $ 20% to medium - sized customers ; 80% to small - sized customers . 2 25% to medium - sized customers ; 75%/ to small - sized customers . *Excluding Specials * Excluding Specials