P Courses : BA 452.010V F20 : Assi X P Pepperdine Seafood Company.p X Homework Help - Q&A from On x + X On A https://courses.pepperdine.edu/access/content/attachment/ba452.010V_2206/Assignments/091ede16-e216-4bb3-a547-285a... Xx O . . . 1 of 1 + 2 | A' Read aloud | Draw Highlight v & Erase Pepperdine Seafood Company The Pepperdine Seafood Company (PSC) is a buyer and distributor of seafood products that are sold to restaurants throughout the west coast. PSC has a frozen storage facility in Los Angeles that serves as the primary distribution point for all products. One of PSC's product is the frozen large rock shrimp which are normally sized at 20 pieces per pound. Each Saturday PSC can purchase more rock shrimp or sell the rock shrimp that have in stock at the existing Los Angeles warehouse market price. PSC's goal is to buy shrimp at a low weekly price and sell it later at a higher price. PSC currently has 20,000 pounds of rock shrimp in storage. Space is available to store at a maximum of 100,000 pounds of rock shrimp each week. In addition, PSC developed the following estimates of rock shrimp prices for the next four weeks: Week Price/lb $6.0 A W I $6.20 $6.65 $5.55 PSC would like to determine the optimal buying storing selling strategy for the next four weeks to maximize profit. The cost to store a pound of shrimp for one week is $0.15, and to account for unforeseen changes in supply or demand, management also indicated that at least 25,000 pounds of rock shrimp must be in storage at the end of week 4. Questions (Use a separate worksheet for each question): 1. Provide the standard form (i.e., define the decision variables in detail, objective, and all constraints mathematically) for the problem stated above. (50 pts) 2. Use Solver to find the optimal buying storing selling strategy for PSC. What is the maximum projected profit for the four weeks? Follow the guidelines when building the model on Excel. (30 pts) 3. From the sensitivity report, calculate the impact of the following: If you only need to stock at least 20,000 pounds of rock shrimp instead of 25,000 how will this impact your profit? You must create the sensitivity report in a separate spreadsheet and answer from the sensitivity report while explaining how you find your answer. (10 pts) Type here to search O N W X P R 10:23 PM 9/17/2020P Courses : BA 452.010V F20 : Assi X P Pepperdine Seafood Company.p( X Homework Help - Q&A from On x + X On A https://courses.pepperdine.edu/access/content/attachment/ba452.010V_2206/Assignments/091ede16-e216-4bb3-a547-285a... . . . 1 of 1 + 2 | A) Read aloud | Draw Highlight v & Erase H Week Price/lb $6.00 A W I $6.20 $6.65 $5.55 PSC would like to determine the optimal buying/storing/selling strategy for the next four weeks to maximize profit. The cost to store a pound of shrimp for one week is $0.15, and to account for unforeseen changes in supply or demand, management also indicated that at least 25,000 pounds of rock shrimp must be in storage at the end of week 4. Questions (Use a separate worksheet for each question): 1. Provide the standard form (i.e., define the decision variables in detail, objective, and all constraints mathematically) for the problem stated above. (50 pts) 2. Use Solver to find the optimal buying storing selling strategy for PSC. What is the maximum projected profit for the four weeks? Follow the guidelines when building the model on Excel. (30 pts) 3. From the sensitivity report, calculate the impact of the following: If you only need to stock at least 20,000 pounds of rock shrimp instead of 25,000 how will this impact your profit? You must create the sensitivity report in a separate spreadsheet and answer from the sensitivity report while explaining how you find your answer. (10 pts) 4. Building upon the model created for question 2, enhance the model by making it more user- friendly by using the list box so that the user can change (1) the starting inventory, which is currently 20,000 pounds, with other options of 10,000 and 30,000 pounds, and (2) the maximum capacity of storing shrimps per week, which is currently 100,000 pounds, with other options of 90,000 and 120,000. The user should be able to find the optimal solution for different values chosen from the list boxes by just running Solver again and should not need to change any values in the model. (10 pts) Type here to search O N W X P R 10:23 PM 9/17/2020
