Design Assignment

This design exercise involves the development of a decision support system (DSS).  This type of tool provides a user with information that aids in decision making.

As an inventory manager, the idea with inventory policies for "B" items is that you develop the policy (e.g. R,Q) and then implement it in a computer system.  In this case, for example, once the inventory position drops to R units, the system will advise the user that it's time to place an order for that item, and that Q items should be ordered.  "A" Items require a bit more work, and "C" items may require a less precise approach (at the time of this assignment, we have not covered these two categories, but we will very soon).

Your assignment is to design and build a computerized tool to provide guidance for the development of an inventory policy for A, B or C items. The tool will provide two main functions for a SKU:

1. Recommend an inventory policy, based on the SKU information, the SKU category (A, B or C), and the criteria (minimize cost or satisfy service performance).

2. Allow the user to evaluate system performance and cost for that particular SKU by allowing the user to change the policy parameters (R and Q, or T and S) and calculating the resulting performance measures and policy costs (Scenario Analysis).

The user should be guided to enter all relevant information about a SKU.  To simplify matters, the user should be asked which category (A, B or C) the SKU falls into.

Function 1:  Recommend policy

The tool should recommend the following:

• An (R, Q) system, for an A item;
• Both an (R, Q) system AND a (T, S) system, for a B item, and
• A (T, S) system, for a C Item.

Obviously, the user will have to either specify a minimum desired service level or require the minimization of a cost function for a specified shortage cost.

The DSS should also then show several measures of the expected performance of the system, for the policy it is recommending.  It should also allow the user to enter a b2or b3cost, and calculate the expected policy cost for that measure.

Function 2:  Scenario Analysis

The tool should also allow the user to evaluate scenarios.  For a B item, for example, the user should be able to change R and/or Q, and see the resulting impact on the performance measures, and policy cost (if the b2or b3 cost are provided).  It might even be nice to generate a graph, that shows the impact of deviations from the recommended policy.  I'll leave that for you to decide how that might be useful.

Other Information

This tool could be in Excel, or whatever you wish to use.  Consider your user is someone like me, with my level of knowledge of Excel and other software.  In other words, it doesn't have to be foolproof nor have a slick interface.  It should, however, be easy for someone like me to use, and without having to do too much other than entering the data.  For example, in Excel, don't ask ME to open Goal Seek and enter values.  It should be more automated than that. 

You can assume each SKU is ordered individually from an external supplier (not made in house). 

Please provide the tool, and a short writeup describing how it was designed, and a user guide.  I will test it myself, so you have to also submit the tool itself, ready to use.  I will evaluate your project on its ease of use, functionality (it does everything it should), quality of the report/manual, and the accuracy of the calculations.