4 Problem 1 - The Golden Eagle Supplies - (25 points In February, John, the owner of Golden Eagle Supplies, Inc., must place an order for the new USM football shirts for the coming season. The long lead-time is required for the contract manufacturers in Asia to order the necessary materials and to begin production in April. The shirts will be delivered in early August just in time for the new season. The manufacturer will charge $20 for each shirt ordered, and John will sell each shirt for $70 during the season. There will not be another opportunity for him to order any more shirts it he runs out. At the end of the season, John will sell all the shirts that are left at a price of $15. In case of a stock-out, John will incur a customer goodwill cost, which he estimates $5 per each lost sale. Obviously, John doesn't know exactly what the demand for these shirts will be, but during the past few years, he has sold an average of 500 shirts each season. Regardless of the order quantity, John also needs to pay a fixed ordering cost, which is $1.500 per order, However, note that if John decides not to order, the fixed cost will not be incurred a fill out the missing labels in the influence chart of the problem (s points) b. Develop a base-case Excel spreadsheet model to calculate Golden Eagle Supplies total profit. For the base case, use an order quantity of 600 shirts, and a demand of 500 shirts (8 points) Construct one way data table of order quantity vs profit. Vary order quantity from 200 to 800, with increments of 50. Use conditional formatting for better visualization (s points) d. Provide a scatterplot of order quantity and profit. Give proper chart title and axis names (5 points) What is the optimal profit using the one way data table and the scatterplot you have created in parti and drespectively? Do not attempt to use solver to answer this question (2 points) c SED 4 Problem 1 - The Golden Eagle Supplies- (25 points) In February, John, the owner of Golden Eagle Supplies, Inc., must place an order for the new USM football shirts for the coming season. The long lead-time is required for the contract manufacturers in Asia to order the necessary materials and to begin production in April. The shirts will be delivered in early August just in time for the new season. The manufacturer will charge $20 for each shirt ordered, and John will sell each shirt for $70 during the season. There will not be another opportunity for him to order any more shirts if he runs out. At the end of the season, John will sell all the shirts that are left at a price of $15. In case of a stock-out, John will incur a customer goodwill cost, which he estimates $5 per each lost sale. Obviously, John doesn't know exactly what the demand for these shirts will be, but during the past few years, he has sold an average of 500 shirts each season. Regardless of the order quantity, John also needs to pay a fixed ordering cost, which is $1,500 per order. However, note that if John decides not to order, the fixed cost will not be incurred. a. Fill out the missing labels in the influence chart of the problem (5 points). b. Develop a base-case Excel spreadsheet model to calculate Golden Eagle Supplies' total profit. For the base case, use an order quantity of 600 shirts, and a demand of 500 shirts (8 points). c. Construct a one-way data table of order quantity vs. profit. Vary order quantity from 200 to 800, with increments of SO. Use conditional formatting for better visualization (5 points). d. Provide a scatterplot of order quantity and profit. Give proper chart title and axis names (5 points). e. What is the optimal profit, using the one-way data table and the scatterplot you have created in part and d, respectively? Do not attempt to use solver to answer this question (2 points)