Submission instructions:
Start your written report with this cover page, which lists the names of group members who contributed to the assignment.
The written report should address the questions listed below. Type your answers in a word processor, convert it to PDF format. Each group is to develop a single report write-up.
Submit the PDF file with the accompanying Excel files electronically using nexus (under any group member’s names). The Excel files will be used to check the sources of your computations and to detect a mistake, if any.
To make it easy to give you the marks you deserve, you need to communicate clearly and concisely. SHOW YOUR WORK TO GET FULL CREDIT!! Include page numbers. Please use grammatically correct English.
You may use Microsoft Office’s screen print functionality (Snip tool) to copy a spreadsheet into Microsoft Word. Be sure to copy only the relevant part of the sheet.
Use 2 decimal places as appropriate (unless stated otherwise).
Good luck!!!
Question 3. Perky Chocolates [6 marks]
Excel File: BUS3502 Q3 Chocolate TEMPLATE.XLS
Perky’s Chocolates carries a wide selection of chocolate but only orders the Easter Sweet Georgia Browns Milk Chocolate boxes one time in anticipation of Easter demand. This year, they want to stock this item that retails for $19.00. They cost the company $9.00 to order. If they do not sell all of them by Easter, they will be discounted by 60 percent and can always be sold easily. Perky’s Chocolates is unsure of the exact demand for the Easter Sweet Georgia Browns Milk Chocolate boxes but they estimate sales of about 500 boxes with a Poisson distribution. Their dilemma is deciding how many boxes to order for this season. If demand exceeds the order quantity, then Perky’s Chocolates loses a profit opportunity. On the other hand, if too many are ordered, they will lose money by discounting them below cost. Perky’s Chocolates first wants to examine the statistics associated with ordering exactly 500 boxes, their expected demand. They then want to examine the impact of ordering between 400 and 650 Easter Sweet Georgia Browns Milk Chocolate boxes. The manufacturer requires that the chocolate boxes be ordered in batch sizes of 50 because of shipping constraints.
Program Excel: In B3, set the Distribution to Poisson with a rate of 500. In Column C, rows 5 to 8, enter the appropriate revenue or cost per box of chocolate, calculated & formatted in dollar $ (watch signs).
Before you proceed with the simulation, check your model.
Check the Model Logic: Try at least 5 values to check the model logic by clicking F9.
Part a)
Use your model to determine the average net profit and the average unsatisfied demand when 500 boxes of chocolates are ordered for this season. Clearly state in words the average expected net profit and the average expected unsatisfied demand for boxes of chocolates at Perky’s.
Use seed value=123456
What is the 99% confidence interval for average expected net profit?
Show work using full values in original equation but rounded to two decimal places for solution.
What is the average expected probability that the unsatisfied demand is between 30 and 40?
For screen shots use the screen shot or screen clipping function under the Insert tab – Illustrations group:
Part b) Next, you will determine the best number of boxes of chocolates to order.
You need to run simulations for many different values of boxes of chocolates to order (cell B5), so we can determine the best number to order for the upcoming season. We’ll run simulations for the range of choices from 400, 450, 500, etc. We could do this by entering 400 in B5, running the simulation, recording the results, entering 450 in B5, running the simulation, recording the results, etc. This is rather tedious. Instead, we will use the Risk Solver function to automate this process.
Define Simulation Parameter: Highlight cell B5 (Number Ordered) and click the Risk Solver “Parameters – Simulation” button. Define the “Bounds”: “Lower” is 400, “Upper” is 650. This will allow Risk Solver to run multiple simulations with the number of boxes of chocolates to order using the required values.
Clear Cell as Risk Solver Result: Clear Unsatisfied Demand (cell B8) as a Result Cell. (Keep Net Profit (cell C10) as a Result Cell) To Clear the Result Cell refer to the right task pane, select cell $B$8 under Simulation, Uncertain Functions, Camping. Then click the red X to delete the function.
Run the Simulation and Generate a Report: You must run the simulation before you can generate a report. To run the model, ensure the simulation light bulb is illuminated “on”.
To generate the report, select “Reports – Simulation – Parameter Analysis”.
In the windows set the following:
– Ensure cell $C$10 is selected in the top drop down list.
– Select “Mean” under the statistics to include in the report.
– Under “Parameters”, select Number_Ordered and click the “>” button to move it to the right side.
– Type “6” in the Major Axis Point Box because there are eleven numbered ordered values in the range we set in the parameter cell.
– Click the “OK” button to generate the report.
The Sim Analysis Report will appear in the tab list at the bottom of the Excel Window; click it to view the report.
Ensure the output is properly formatted including appropriate heading for the data, proper titles (with product and company information) for the table and appropriate number formatting for profits (dollars and cents).
To keep the columns from being too wide, use the wrap text feature in Excel.
– First select the appropriate cell and click the wrap text button on the home tab.
– Then resize the cell horizontally and vertically to fit the data.
– You also need to center the values in the column to make it easier to read.
(i) Submit the data table generated by Risk Solver. Remember to use an appropriate title (company name) and headings (product name [Easter Chocolate Boxes] and net profit). Ensure that the table is properly formatted (wrap text, centered data).
(ii) Create a line chart in Excel that is uncluttered and properly labeled. (Axis labels, proper title, properly formatted, no excess information; see course notes for a good example).
(iii) Clearly state the best number of boxes of Easter Chocolates that Perky’s should order and the associated average expected net profit.
2