IDS 355 Fall 2017 Assignment 5 Due Friday11/24 /2017 40 points Instructions Deadline This assignment may be submitted anytime until Friday, November 24th at 11:59pm. Due to the proximity to the end of the semester, late submissions will not be accepted. However, you can still receive a three-day extension as long as you request it by Wednesday November 23 th. As usual, we strongly recommend submitting your assignment early, in case problems arise with the submission. Problem Guidelines Because using spreadsheet software is a basic business skill, the assignments are intended to give you practice in structuring problems as well as simply finding the answers. All problems must be solved using Excel formulas to receive credit. If, for example, you solve a problem on paper and then copy the results to Excel, you will receive no credit for that problem. Submission Guidelines All assignments are to be submitted using the Blackboard site for your lab section. Assignments should be uploaded using the upload link in the Assignment folder, not the digital dropbox. It is important that all homework files use the following file naming system, so they can be easily identified and sorted. The file containing homework assignment 5 submitted by the student with NetID rbaker2 should be named rbaker2hw5. For this assignment, please submit one Excel file. This assignment uses only one worksheet; it should be labeled \"Q Set 1\". Be sure to label all results clearly. Questions Question Set 1. You are an inventory management consultant. You have been hired by Target to help optimize inventory management for their stationery department. Your immediate task is building a Monte Carlo simulation for sales of pens, pencils and notebooks. The probabilities of monthly demand levels are: Probabilit y 0.05 0.10 0.10 0.10 0.15 0.15 0.10 0.10 0.10 Pens 191 212 225 234 244 256 266 275 288 Pencil s 312 342 362 376 391 409 424 438 458 Notebook s 241 262 275 284 294 306 316 325 338 0.05 309 488 359 The unit revenues and costs are given in the table below. Product Pen Pencil Notebook Unit Revenue $1.45 $0.85 $2.95 Unit Cost $0.80 $0.40 $1.65 Each product has a separate display with an associated fixed monthly cost: Product Pen Pencil Notebook Fixed Monthly Display Cost $150 $100 $300 1. (5pts) Build a table for the cumulative probability distribution for demand. It should be set up for use with the VLOOKUP function, with the cumulative probabilities on the left. 2. (15pts) Build a table for tracking sales over one year (Jan 2016-Dec 2016) with separate columns for pens, pencils, and notebooks. This will just be the randomized demand value for each item for each month (like in 10.1.2 from the lab exercise on Monte Carlo simulations). For this assignment, assume all demand is actually met (that is, sales = demand) and there is no other inventory loss except through sales. 3. (10pts) Build a table for tracking total revenues, costs, and profit for the week. This table should use the information provided in your answer to 2., above. Your table should calculate values for: Total Revenue (based on the combined sales of all three products over the year) Total Cost (also based on combined sales, and including cost of goods sold and fixed costs) Profit/Loss (total revenue minus total costs) 4. (10pts) Create a data table to generate 50 runs of the simulation and calculate the average profit of the 50 simulation runs. As in previous assignments, this must be an actual data table structure to earn full credit. Calculate the average profit over the 50 simulation runs