I need help in this question, Please do it on excel and do not make any mistake. Please do it correctly as required
A D E F G H 1 B The Quality Sweaters Company sells hand-knitted sweaters. The company is planning to print a catalog of its products and undertake a direct mail campaign. The cost of printing 2 the catalog is $30,000 plus $0.05 per catalog. The cost of mailing each catalog (including postage, order forms, and buying names from a mail-order database) is $0.10. In addition, the company plans to include direct reply envelopes in its mailings and incurs $0.25 in extra costs for each direct mail envelope used by a respondent. The average size of a customer order is $60, and the company's variable cost per order (primarily due to labor and material costs) averages about 80% of the order's value-that is, $48. The company 5 plans to mail 100,000 catalogs and wants to develop a spreadsheet model to answer the following questions: 5 a.) Please complete the Data Table and plot a figure in the Excel file, to show the relationship between response rate and the profits. 7_b.) Use Goal-Seek to find out the break-even response rate with which the profits equals zero 3 Model of responses Response rate (r) Number of responses (Use Goal-seek to find this number) LO 1 Quality Sweaters direct mail model 2 3 Catalog inputs 4 Fixed cost of printing (F) 5 Variable cost of printing mailing (c1) 6 7 Decision variable 8 Number mailed (Q) 9 Lo Order inputs 1 Average order (p) -2 Variable cost per order (c2) 3 100000 Model of revenue, costs, and profit Total Revenue Fixed cost of printing Total variable cost of printing mailing Total variable cost of orders Total cost Profits D25 f B C D E F G 21 Average order (p) 22 Variable cost per order (C2) 23 Total variable cost of orders Total cost Profits 24 25 26 Response rate Profits $0.00 27 28 29 1% 2% 3% 30 31 32 33 34 35 36 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 37 38 39 40 41 42 43 A D E F G H 1 B The Quality Sweaters Company sells hand-knitted sweaters. The company is planning to print a catalog of its products and undertake a direct mail campaign. The cost of printing 2 the catalog is $30,000 plus $0.05 per catalog. The cost of mailing each catalog (including postage, order forms, and buying names from a mail-order database) is $0.10. In addition, the company plans to include direct reply envelopes in its mailings and incurs $0.25 in extra costs for each direct mail envelope used by a respondent. The average size of a customer order is $60, and the company's variable cost per order (primarily due to labor and material costs) averages about 80% of the order's value-that is, $48. The company 5 plans to mail 100,000 catalogs and wants to develop a spreadsheet model to answer the following questions: 5 a.) Please complete the Data Table and plot a figure in the Excel file, to show the relationship between response rate and the profits. 7_b.) Use Goal-Seek to find out the break-even response rate with which the profits equals zero 3 Model of responses Response rate (r) Number of responses (Use Goal-seek to find this number) LO 1 Quality Sweaters direct mail model 2 3 Catalog inputs 4 Fixed cost of printing (F) 5 Variable cost of printing mailing (c1) 6 7 Decision variable 8 Number mailed (Q) 9 Lo Order inputs 1 Average order (p) -2 Variable cost per order (c2) 3 100000 Model of revenue, costs, and profit Total Revenue Fixed cost of printing Total variable cost of printing mailing Total variable cost of orders Total cost Profits D25 f B C D E F G 21 Average order (p) 22 Variable cost per order (C2) 23 Total variable cost of orders Total cost Profits 24 25 26 Response rate Profits $0.00 27 28 29 1% 2% 3% 30 31 32 33 34 35 36 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 37 38 39 40 41 42 43
