Read “New Bakery for Oz Bread” case study. Develop an Excel model to use Solver (i.e., an add-in for optimisation) to help answer the case questions. Write a business report summarising the case, the issues identified, and your analysis of the different networks options under consideration.
The following should be noted:
- An Excel workbook with multiple worksheets, one for each scenario in each year, is required. The model should be clear in structure and layout with proper title and labels. Colour scheme can be used to facilitate understanding. Model logic should be easily followed and the results can be repeated. Please note that some case questions may require multiple worksheets to analyse the various scenarios involved.
- A formal business reportis also required.
- In addition to the Covering Page(a page that properly provides the course number and title, assignment number, title of the case, student number, student name, and submission date), the report should include the following:
(a) Executive Summary (Summarise the case, the findings of analysis, and the recommendations in one page.)
(b) Table of Contents (List the sections and subsections with corresponding page numbers on a separate page.)
(c) List of Figures (List the figure numbers and captions, if any, with corresponding page numbers on a separate page.)
(d) List of Tables (List the table numbers and captions, if any, with corresponding page numbers on a separate page.)
(e) Summary of the Case (Use your own words to give a brief summary of the case in half a page.)
(f) Objectives (State the purposes of the study and what needs to be investigated in half a page.)
(g) Issues Identified (Analyse the information provided in the case to identify the issues faced by the company in half a page.)
(h) Network Option Analysis (Analyse in one page the network options (i.e., scenarios) under consideration using the information provided in the case. Both the current (i.e., as-is) and the optimized (i.e., improved) configurations need to be worked out. While the as-is situation can be worked out using simple calculation, MS Solver will be needed to find the optimized solutions. Summary of findings of the scenario tests for the case questions should also be presented. NOTE: Only summaries of the analyses are needed in this section. Details of the analyses should be given in the Answers to the Case Questions section. Use summary tables to show the configurations and costs.)
(i) Recommendations (Recommend the best option to adopt based on the outcome of the analysis in half a page.)
(j) Conclusions (Summarise the study and give insight in half a page.)
(k) Answers to the Case Questions (Answer the case questions one by one in separate subsection with the question number clearly stated. DO NOT MERGE EVERYTHING into a big paragraph or essay. Marks will be deducted if answers to the case questions are not provided separately as required. Make full use of the information in the case and the model as a tool to help answer the questions. Assessment is based on how thorough the case analysis, whether the model has been appropriately used, and how relevant the answers to the case questions are.)
(l) References (List the references used in the report in strict Harvard style.)
(m) Appendices (Show the problem formulation and the Solver setups of the model.)
- Use summary tables, charts, and figureswhere appropriate to aid your analysis and discussion. The figures and tables should be inserted in the sections and subsections immediately after they are mentioned instead of in the List of Figures and List of Tables.
- Use screen dumpsof the spreadsheet to aid explanation where appropriate.
- All references must be provided in strict Harvard style. See http://mams.rmit.edu.au/zxwepdjik7ox.doc(Links to an external site.) for guidelines and examples in using Harvard style.
- Students can also refer to the document entitled “Written reports and essays: Guidelines for referencing and presentation in RMIT Business” (Version 3.0, available at http://mams.rmit.edu.au/s9sx559hurvc.rtf(Links to an external site.)) for advice and tips on how to write proper reports and essays.
- For this case study, you are required to analyse and compare among three network options. As stated in the case, one option is to keep the existing small warehouse in St. Louis and rent additional facilities all in the same city. Another option is to keep the small warehouse in St. Louis but rent additional facilities in other cities only. The final option is not to keep any warehouse in St. Louis and rent new facilities in other cities only. There are pros and cons under each option and it is your job to find the best one for SportStuff.com. In doing so, you should make use of ALL the information provided(particularly the cost figures) and develop a Solver model for the network analysis. Once the model is verified and validated, you can use the model as a tool for scenario testing to help you answer the case questions.
- The suggested word length of the report is 3,000 to 3,500.
- The assessment of the assignment will be based mainly on the accuracy of the logicand the comprehensiveness of the analysisas presented in the report. The more effort students invested in analysing the case and writing the report the higher the marks will be. Only getting the correct answer in a spreadsheet will certainly not be good enough.
Tips for Assignment 2
For the quantitative assignment, the optimisation model should be set up in such a way that once validated it can serve as a tool to help answer the case questions. To answer the case questions, you are required to create one model on a separate spreadsheet for each scenario for each year to facilitate assessment. This can be easily done by copying the validated base model to a new spreadsheet and amend the input parameters and constraints where necessary to represent the conditions of the scenario prior to running Solver to get the optimal solution. Basically, the workbook should comprise at least 11 worksheets. Ten of them should be models and one is the action plan based on the recommendation.
NOTE: DO NOT use only one single spreadsheet or a summary report in the Excel workbook for everything and expect the assessor to create the models for the different scenarios from scratch to check your answers. Marks will be significantly deducted if the required models are not provided.
The following file contains some tips for this assignment:
Assignment 2 Marking Rubric
For this assignment, the following marking rubric will be used: