BA 302 Business Process Management - Simulation - Homework

In this homework, you'll work with a simple, discrete simulation model of a fictitious gift-wrapping business.

To successfully complete this homework, we strongly advise that you follow the following procedure:

• The homework is based on Microsoft Excel 2003 or 2007 (your choice) running on Microsoft Windows.
  

• For these spreadsheets to work you MUST(!!) add-in the Analysis Toolpak (if installed you see a Data Analysis option in the Analysis group of the Data option on the main menu). If you do not have the Analysis Toolpak added in and you do not know how to add it in, go to Excel's Help (F1) and type "Analysis Toolpak"

• Since the Analysis Toolpak is not part of Microsoft's Mac version of Excel, this homework will NOT run on Mac Excel.

• Carefully read the problem and make sure that you understand its logic.

• Study the simulation model associated with the problem. Make sure you understand what the spreadsheet shows and how you can use it before(!!) you use it to answer the questions.

• If reading the problem and studying the spreadsheet causes you problems or if you lose track of things, first review your notes from class and see if your classmates can help you. If you remain stuck, see your instructor.

• This homework may take a bit of time; please start it on time!

Assignment Background (adapted from Weida, Richardson, Vazsonyi (2000) Operations Analysis Using Microsoft Excel)

One of the costs of doing business is work-in-progress (WIP) inventory; i.e., value-added work that is in progress but not yet ready to be sold. The value of this 'inventory,' often known as holding cost increases as it moves forward through the process chain, with inventory at the end worth more than at earlier steps. Therefore, to compute holding costs one typically multiplies the WIP quantity at each step by its process-added value at that step. Holding cost is thus a function of how long the holding period is, the costs of storing and keeping track of the inventory, any finance costs incurred in borrowing the money to pay for the inventory until it can be eventually sold and the expenses recovered, etc.

Before you go on, make sure you understand this concept of holding cost and the way in which these get computed.

Your simulation model simulates a five step process employed by the Last-Minute Gifts company for preparing gift assortments for delivery to holiday customers:

1. Checking the order against available items in stock.
2. Selecting the right box and packing materials.
3. Retrieving the set of items from the warehouse stock.
4. Assembling the assortment in the box.
5. Closing the box and completing the documentation for delivery and sending the completed invoice to order processing.

Let us assume that process capacity; i.e., the availability and performance of the people working in the business, and the demand for gifts to be wrapped are both described by simple, uniform probability distributions. Under that assumption we can ask ourselves whether or not increasing the maximum capacity of our process affects WIP levels and hence, holding costs. Increased peak capacity at each step costs more, but if it lowers the holding cost more than the extra capacity costs, it might be worth it. To keep the numbers simple, we will assume the holding cost per gift to be $1, $2, $3, $5, and $7 for process steps 1, 2, 3, 4, and 5, respectively.

Now study the Simulation1.xls spreadsheet that models the process of this company. Notice how the Model sheet tracks for each of the five production steps the various variables for a 20-day sequence (20 demands, 20 capacity fluctuations). By pushing the F9 key you can resample the values and run another 20-day simulation. The Multiple Runs sheet collects 100 of these 20-day runs; i.e., every time you press F9, 100 20-day model runs are made for a total of 2000 days. Since the values for the runs are sampled from probability distributions, individual runs come out differently. Make sure you understand this before you go on.

Your assignment:
Using Simulation1.xls, evaluate the effects on holding cost when increasing the peak capacity of each step from 6 to 7 (about a 16% increase).  To do this, you will need to run two simulations of 100 runs each: one to estimate the holding cost with the current capacity, and the second to estimate the holding cost with the proposed capacity increase.

CAUTION: Since pressing the F9 key changes all of the data in the spreadsheet, you will need to copy the results for each of the 100 runs on the Multiple Runs sheet to a separate worksheet where they remain unchanged regardless of how many times you push F9. That way you can analyze your result data without having them change all the time. Use Copy --> Paste Special... --> Values to do this.

In your analysis answer the following questions:

1. Would you expect holding cost to go down with increases in peak capacity? Explain your answer. What could cause the actual pattern to be different from the expected one?
   
2. Run your model to test your hypothesis formulated under 1. What do the holding cost do when you increase capacity from 6 to 7? Do they follow the expected pattern? If not, why not?
   
3. Would you expect the capacity increase to have an effect on slack time (see Model sheet)? Would you include these slack effects in your decision to increase or not increase capacity?
4. Make a recommendation as to whether or not to increase the peak capacity. In your analysis, carefully consider the average holding cost, the minimum and maximum holding cost and the variation in holding cost.

Along with your answers, turn in copies of the Multiple Runs sheets of both the original and altered spreadsheet so that your instructor can see the data that you worked with.

 

Writing guidelines:

• Submit the homework in word-processed hardcopy (printed on paper).
• Add a title (cover) page to your work containing:
• Title.
• Your name.
• Course number (BA302), course session, and name of the instructor of record.
• Date.

• Add your name (format: Last name, First name) to the document header so that it shows on the left upper corner of each page !!!INCLUDING THE TITLE (COVER) PAGE!!!.

• Do not use the back side of the title (cover) page; leave it free for the instructor to make notes.
• Use one-and-a-half (1.5) line spacing, 11- or 12-point font and reasonable margins.
• Separate your text in logical blocks and sections.
• Number pages (Title page has no number. First page with content has page number 1).
• Integrate figures and tables into the text; do not add them as an appendix in the back of the report.
• If you use a figure or a table, properly number and caption it (use your textbook as a guide and your text processor's support for this -- DO NOT HAND-TYPE THE FIGURE AND TABLE NUMBERS).
• Use cross-references to refer to figures and tables (again, use your textbook as an example and your text processor's support for this -- DO NOT HAND-TYPE THE FIGURE AND TABLE REFERENCES).
• Spell check (both automatically and manually!).
• Grammar check!!
• Peer review!! Before handing in your work, consider asking one of your colleagues to review it in exchange for you reviewing his/hers. When reviewing, carefully (!!) read what your colleague wrote. Notice ambiguities, note each and every spelling/grammar error, break up long and awkward sentences into shorter, more elegant ones, etc. In other words: show that you care. Peer review is super powerful -- Use it!!!