Following please find two problems for which I need answers. Use Excel in formulating your linear calculations. Please also show your work in detail and explain each step of each problem.
1) Problem #1 Atlantic Seafood Company (20 points)
The Atlantic Seafood Company (ASC) is a buyer and distributor of seafood products that are sold to restaurants and specialty seafood outlets throughout the Northeast. ASC has a frozen storage facility in New York City that serves as the primary distribution point for all products. One of the ASC products is frozen large black tiger shrimp, which are sized at 16-20 pieces per pound. Each Saturday ASC can purchase more tiger shrimp or sell the tiger shrimp at the existing New York City warehouse market price. The ASC goal is to buy tiger shrimp at a low weekly price and sell it later at a higher price. ASC currently has 20,000 pounds of tiger shrimp in storage. Space is available to store a maximum of 100,000 pounds of tiger shrimp each week. In addition, ASC developed the following estimates of tiger shrimp prices for the next four weeks:
ASC would like to determine the optimal buying-storing-selling strategy for the next four weeks. 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 25,000 pounds of tiger shrimp must be in storage at the end of week 4.
Determine the optimal buying-storing-selling strategy for ASC including the projected four-week profit.
2) EZ-Windows, Inc., manufactures replacement windows for the home remodeling business. In January, the company produced 15,000 windows and ended the month with 9000 windows in inventory. EZ-Windows management team would like to develop a production schedule for the next three months. A smooth production schedule is obviously desirable because it maintains the current workforce and provides a similar month-to-month operation. However, given the sales forecasts, the production capacities, and the storage capabilities as shown, the management team does not think a smooth production schedule with the same production quantity each month possible.
February March April
Sales forecast 15,000 16,500 20,000
Production capacity 14,000 14,000 18,000
Storage capacity 6,000 6,000 6,000
The company's cost accounting department estimates that increasing production by one window from one month to the next will increase total cost by $1.00 for each unit increase in the production level. In addition, decreasing production by one unit from one month to the next will increase total costs by $0.65 for each unit decrease in the production level.
Ignoring production and inventory carrying costs, formulate and solve a linear programming model that will minimize the cost of changing production levels while still satisfying the monthly sales forecasts.
Please see attachments.
Word file contains
1. Linear programming formulations with ...
Word file contains
1. Linear programming formulations with detailed explanation for decision variables, objective function, and constraints
2. Solution using LINDO software is also provided
Excel file contains
1. Problem set up using cell references
2. Formulas sheet for both problems.