Instructions: You will be asked to submit a printed Word document that includes answers to all questions as well as certain screenshots relating to each question. Presentation is important and your assignment, (including the screenshots) should be easy to read. As a guide, the total word count should be no more than 750. This is in addition to the screenshots of course. A Word count substantially over this value may lead to the excess length not being marked. You must also submit the excel spreadsheet.
You have recently joined the Getco company as a student intern to the purchasing manager at Getco headquarters. As an initial task, you have been asked to review purchases of paper. The headquarters office buys a thousand boxes of white A4 paper six times a year at a cost of $10 per box. The time between placing and receiving an order is typically about 5 working days. Getco headquarters is open 300 days per year. The cost of placing an order with a supplier is estimated to be $80. Your new colleagues claim that the present inventory arrangements are optimal, but the purchasing manager wants you to investigate. Calculations in part (a), (d), (e) and (f) should make use of tables created in Excel spreadsheets. These tables should be provided as screen shots in your Word document, accompanied by brief explanations of the calculations.
A few hints are given at the end of this document.
(a) Your colleagues claim that under the EOQ assumptions, the purchase of 1,000 boxes every fifty working days results in the lowest TARC. If the colleagues’ claim is correct,
i. What is the annual holding cost per dollar value of holding items in inventory? That is, what is the rate I ? Set up a table in Excel (Table (a)), and use Solver to find this rate. Summarize your conclusion.
ii. Hence what is the total cost of holding one box for one year?
iii. What is the Total Annual Relevant Cost (TARC)? In part (a)(i), provide two screenshots of the completed table (Table (a)) that was used in the calculation, one showing formulas and one showing values. Also show a screenshot of the Solver dialog box.
(b) What re-order point do you recommend in this case and why?
(c) You had your doubts about the claim that the original paper purchasing arrangement was optimal. In order to be better informed about the two significant components of holding cost, you now have discussions
i. With the CFO, and learn that the cost of tying up capital is estimated to be 13% per annum ii. With the warehouse manager, and learn that it costs $100 to store 500 boxes for one year.
Based on this information, and assuming these are the only significant holding costs, calculate a simple estimate of the rate I and hence the total cost of holding one box for one year. Show your reasoning.
(d) Using the parameter value obtained in (c), set up another table (Table (d)) with which to find the purchasing arrangement that leads to the minimum TARC, and use Solver to obtain this minimum cost. Summarise the details of the purchasing arrangements (how much, how often etc). Provide a screenshot of the Solver dialogue box, and also two screenshots of the Table (d) after doing this calculation: one showing formulas, and one showing values.
(e) We now consider the original arrangement described in (a), and also the one based on the estimates in (c).
i. Assuming the estimate of the rate I in (c) is correct, what was the true TARC from the original (non-optimal) arrangement discussed in (a)? And what were the total holding cost per year and the total ordering cost per year in the original arrangement? Comment on these values by comparing them with the values calculated in (a). Perform these calculations using a table constructed in Excel, and provide two screenshots of the table, one showing formulas and the other showing values ii. Are there reasons to be concerned about the suitability of your colleagues’ estimates being used in inventory calculations? Discuss briefly.
(f) The suppliers have introduced quantity discounts. Purchases of 1500 boxes or more attract a discount of 5%, while purchases of 3000 boxes or more attract a discount of 10%.
i. Set up a table in an Excel spreadsheet that can be used to determine what ordering regime should be followed in order to take maximum advantage of this price structure. In this table, you should start with any feasible placeholders for Q in each category. ii. By using constraints that specify each discount category, use Solver to find the optimum choice of paper ordering arrangements within each category, and hence … iii. Summarize the inventory management arrangements that should be followed according to this model. In your Word document, supply a screenshot of the initial table, and the table after the calculation is complete and screenshots of the Solver dialogue boxes used for each discount category. The screenshots in part (f) should show numbers
HINTS: • You will find it helpful to use Excel to create tables similar to those shown in lecture examples below, adjusted in each case to the particular question. • Place each table in an Excel spreadsheet in the top left-hand corner, so that your screenshot can include the row and column headers. • In part (a), things are turned around a bit. In your tables constructed in Excel, think about which quantities should be calculated by formula, and which should be initially occupied by a place holding value, and then evaluated using Solver. • Also in Part (a), you should make use of the possibility, in Solver, of setting a calculated quantity to a particular value, (that is, not a minimum or maximum, but a specified number) by adjusting another variable. • If you are taking a while to think about part (a), note that parts (b), (c), (d) and (f) do not depend on part (a) so you can have a go at those before completing part (a).
