Case #3: Sharing Risk & Reward in the Supply Chain Tasks 1 & 2
For this case, you will analyze the impact on the supply chain of sharing risk and rewards through buybacks and revenue sharing. The complete problem description can be found at the end of this assignment. You will analyze different options in Excel and recommend an overall strategy. This assignment has a series of tasks, some of which are completed individually and others as a group. As a result, scores may vary among group members.
Important Submission Notes
•You will be submitting both an Excel spreadsheet and a written report (in pdf format) as part of your assignment.
•Name your files starting with your group number. For example, Group 2 should upload two files: 2-analysis.xlsx and 2-report.pdf.
•One member of the group needs to submit both files (in the same submission) in order for your peers to be able to perform their reviews. You will need to use the button to Add Another File during the submission. You can find details here: https://community.canvaslms.com/docs/DOC-9539-421241972 If you need to resubmit a revision, make sure that the most recent submission includes both files.
•Task 1 (Group Task: 35 points): Read the problem description at the end of this assignment. Your group will create a spreadsheet to analyze three scenarios.
scenario 1: The publisher does not share risk and reward, so Barnes & Noble orders independently (only considers what is best for itself).
scenario 2: The publisher offers Barnes & Noble a buyback (additional funds) for each book that is placed on clearance.
scenario 3: The publisher offers the books to Barnes & Noble at a wholesale price, but then receives a fraction of Barnes & Noble’s revenue.
Specifically, your Excel file should include the following five worksheets (tabs).
1.Independent Analysis: No buybacks or revenue sharing. You can find the template for this in the Excel file for Topic #17.
2.Buyback Analysis: Include results for a range of buyback values. Start with the Buyback worksheet from the Topic #17 examples. Instead of having a single column to calculate profit, copy and paste to perform the same calculations for a variety of buyback amounts to give you an idea what might be best.
3.Revenue Sharing Analysis: Include results for a variety of wholesale prices and fractions of revenue shared. Same idea as last sheet. Start with the example file in Topic #17, and play around with different combinations of wholesale prices and revenue sharing fractions.
4.Buyback Optimization: Use Solver to find the buyback value that maximizes profit. Hopefully, Sheet 2 gave you some insights into what buyback values work well. Starting from a point that looked good on Sheet 2, run Solver to find the “best” option. (You’ll need to think about what the objective function and constraints should be.)
5.Revenue Sharing Optimization: Use Solver for the revenue sharing policy that maximizes profit. Same idea as Sheet 4.
IMPORTANT NOTES ON USING SOLVER
Nonlinear optimization is difficult! You cannot use Simplex LP Solver, so you will either need to select GRG Nonlinear or Evolutionary. Generally, GRG is faster, but sometimes it refuses to move away from the starting solution. If that happens, you might need to try the (slower) Evolutionary Solver.
Where you start the search matters. Solver might only converge to a local optimum. Use Worksheets 2 & 3 to determine good starting solutions for Worksheets 4 & 5.
Give thought to your objective function and constraints. Should you maximize profit for the publisher, Barnes & Noble, or the supply chain? Are there any limits you should place on Solver with respect to the performance of the supply chain partners, or should maximizing profit be your only consideration?
Your overall recommendation is not required to come from Solver. You can consider trade-offs that Solver cannot. If you prefer a different solution, explain why.
Task 2 (Group Task: 50 points): Write a report to Bonsai Books that summarizes your findings and recommendations.
You are a consultant to the Bonsai Books, so make sure your report is focused on their needs and your findings.
Discuss the options you considered, along with the solutions that were obtained through Solver. Be sure to explain the assumptions you made in your optimization models (what was maximized, what constraints were included, etc.).
Use effective tables and graphs to support your recommendation.
Limit the body of your report (not including title page or appendices) to 5 pages.
You may include additional materials in an appendix not counted in the page limit,
but important summaries should be in the report body (and discussed there too).
Your paper should include a title page with the names of all group members.
Task 3 (Individual Task: 5 points): Submit a questionnaire on how you and your teammates worked together. Just like you did for previous cases, you will receive an email from the Teammates website. Click on the link and fill out the very brief survey about how well you and your teammates worked. (Students working individually can skip this.)
Task 4 (Individual Task: 10 points): After the group papers and spreadsheets are turned in, review the submissions (Tasks 1 & 2) submitted by two other groups. As always, Canvas will assign you TWO people for reviews. If it accidentally assigns you two people in the same group, please let me know right away so that I can correct it. You will not be entering scores in a rubric. You will write a paragraph to each group in the Comments area. Be sure to mention 3 things: What they did well, what they could improve upon, and your overall impression of their work.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
