Reiser Sports Products wants to determine the number of All-Pro (A) and College (C) footballs to produce in order to maximize profit over the next four-week planning horizon. Constraints affecting the production quantities are the production capacities in three departments: cutting and dyeing; sewing; and inspection and packaging. For the four-week planning period, 340 hours of cutting and dyeing time, 420 hours of sewing time, and 200 hours of inspection and packaging time are available. The cutting and dyeing department takes 12 minutes to process an All-Pro football and 6 minutes to process a College football. The sewing department takes 9 minutes to process an All-Pro football and 15 minutes to process a College football. The inspection and packaging department takes 6 minutes to process an All-Pro football and 6 minutes to process a College football. Remember, you can't make negative footballs! All-Pro footballs provide a profit of $5 per unit and College footballs provide a profit of $4 per unit. 1. Mr. Reiser has hired you to help him determine whether making only All-Pro footballs, only College footballs, or some specific combination of both products would enable his company to maximize profits. You should create a Report for him with sections for the Background, Issue, Alternatives, Analysis of Alternatives, Recommendation and Attachments. Mr. Reiser is the type of individual that needs to see detail that backs up a logical recommendation. Therefore, you need to have Attachments for your Report that show and explain your LP analysis using graphing, Excel, and Excel Solver. The graphical LP solution will require some mathematics (Attachment 1). Then, you need to carefully draft an LP graph with labeled end points that match Alternatives, a colored feasibility region, and labeled constraint lines for each department (Attachment 2). The colored feasibility region end points that match Alternatives are used for profit calculations and are shown in (Attachment 3). 2. Mr. Reiser was impressed with your LP application of OM tools. However, his Marketing VP thinks that the Reiser pricing strategy is incorrect and that All-Pro footballs should be sold for $4 each and College footballs should be sold for $5 each. Mr. Reiser has asked you to show a new Report with Attachments for this new pricing structure, and make an appropriate recommendation.This means that you will have to redo your graphical, Excel, and Excel Solver analyses for the new pricing. You are not happy with this request, but it has to be done. 3. The head of the Accounting department doesn't trust your manual LP graphical calculations and that is why you must provide Reiser with both an Excel LP solution and an Excel Solver LP solution for the two pricing strategies. An Excel LP Methodology is shown below. Follow the "Flair Furniture" LP Excel Solver example in the Excel Solver PPTs for the Reiser Excel Solver LP solution.

Excel LP Methodology Spreadsheet Labels In the Data tab, set up a Table for the LP problem. In cell A1, enter the label "Reiser LP Analysis." In cells B3:C3 (centered), enter the label "Production Constraints." In cell A4, enter the label "Department." In cell B4, enter the label "All-Pro." In cell C4, enter the label "College." In cell D4, enter the label "Capacity." In cell A5, enter the label "Cutting and Dyeing." In cell A6, enter the label "Sewing." In cell A7, enter the label "Inspection and Packaging." In cell A8, enter the label "Profit Per Unit." In cells B13:C13, enter the label "Decision Variables." In cell B14, enter the label "All- Pro." In cell C14, enter the label "College." In cell A15, enter the label "Recommendation." In cell A17, enter the label "Max Profit." In cell A19, enter the label "Constraints." In cell B19, enter the label "Amount." In cell D19, enter the label "Available." In cells A20:A22, enter the labels for the three production departments. Spreadsheet Data/Formulas In cells B5:B7, enter the values (data) for All-Pro Constraints for the three production departments. In cells C5:C7, enter the values (data) for College Constraints for the three production departments. In cells B8:C8, enter the values (data) for All-Pro Profit Per Unit and College Profit Per Unit. In cells D5:D7, enter the values (data) for Capacity for the three production departments. In cells B15:C15, enter the values (data) for the LP coordinates for Max Profit. In cell B17, enter the formula for Max Profit (=B8*B15+C8*C15). In cell B20, enter the formula for Cutting and Dyeing Constraint (=B5*B15+C5*C15). In cell B21, enter the formula for Sewing Constraint (=B6*B15+C6*C15). In cell B22, enter the formula for Inspection and Packaging Constraint (=B7*B15+C7*C15). In cells C20:C22, enter the less than or equal to function (<=). In cells D20:D22, enter =D5, =D6, =D7 for the Capacity amounts. This spreadsheet should produce an LP solution.