provide some quantitative insight you are asked to build a spreadsheet that will help them understand how robust

  • Home / General / provide some quantitative…

provide some quantitative insight you are asked to build a spreadsheet that will help them understand how robust

How much does this increase the total cost on a % basis? Based on this one data point would you say the EOQ is robust to order quantity or not? Why?Explain

 

  1. You have been asked to perform an analysis to assist in deciding how much of two products to produce to maximize the profit. The total profit associated with producing a units of product a and b units product b is given as a single function that is given below:

𝑃𝑃(π‘Žπ‘Ž 𝑏𝑏) = 0.2π‘Žπ‘Ž2βˆ’1.1π‘Žπ‘Ž+ 12βˆšπ‘π‘βˆ’2.5𝑏𝑏 where P is the total profit ($) a is the amount of product a to produce (tons) b is the amount of product b to produce (tons) The coefficients in front of each term (i.e. 0.2 1.1 12 and 2.5) convert the terms into US
dollars. Find the values of a and b that maximize C.

Note: You might not have done this in calculus but it is not hard because it extends what you learned for one variable.

  1. i) Recall to find the maximum or minimum of a function you take the derivative set it equal to
    zero and solve for the independent variable. So if this problem had been P(a) and not P(ab)
    you would have taken the derivative of P with respect to a (𝑑𝑑𝑑𝑑
    𝑑𝑑𝑑𝑑) set this to zero and solved for a.
  2. ii) To extend this to two variables here a and b take the derivate of P with respect to a assuming b is constant and set it equal to zero. This is just like i) because you are assuming b is
    a constant.

iii) Then take the derivate of C with respect to b assuming a is constant and set it equal to zero.

This is just like i) because you are assuming a is a constant.

  1. iv) Now you have two equations – one from ii) and one from iii). Use algebra to solve them and you have the optimal values. [For completeness with one variable it was necessary to take the second derivate to see if the point you found was a maximum minimum or inflection point.

This part of the two variable problem is complicated so you will need to believe me that the point you found yields maximum
profit.] 2. A ΒΌ inch titanium bolt is being consumed in a factory at a fairly steady rate of 60 per week.

The bolts cost $0.50 each and it costs the plant $12 to place an order with the supplier.

Plant management has no idea of the real cost to hold inventory so they are estimating the holding cost as the product of the annual interest rate multiplied by the cost of the item. Currently the CFO requires all calculations to use an interest rate of 25%/year.

  1. a) What is the order quantity that minimizes the sum of the cost of ordering plus the cost of holding inventory. Assume holding costs are based on average inventory over a cycle and the cost per unit per unit time is the interest rate * cost of the part. Also assume instantaneous replenishment.
  2. b) If you order the quantity you computed in part a) what is the time between placing orders.
  3. c) If you order the quantity computed in part a) what is the total yearly holding cost? What is the total annual ordering cost? 3. A company that orders parts from a supplier wants to use the EOQ as a basis for determining how many to order; however the some of the assumptions particularly constant demand has management concerned.

To provide some quantitative insight you are asked to build a spreadsheet that will help them understand how robust (or not) the model is to changes order quantity. The model parameters are K=$100/order h=$0.02/unit/month and D=2000units/month; hence Q*=4472.

Here are the requirements on your spreadsheet:

  • Column A is the quantity ordered
    β€’ Column B is the annual cost to place orders given the order quantity in column A
    β€’ Column C is the annual holding cost given the order quantity in column A
    β€’ Column D is the total cost (ordering plus holding)
  1. a) Let the order quantity vary from 1500 to 7500 in increments of 100 (Column A) and have the spreadsheet calculate the remaining columns (B through D).

Create a plot with the order quantity as the independent variable (x-axis) and the three costs (ordering holding total) in the
other three columns as the dependent variables (y-axis).

  1. b) Suppose you decide to order 50% more than Q* (i.e. 4472+0.5*4472=6708).

How much does this increase the total cost on a % basis? Based on this one data point would you say the EOQ is robust to order quantity or not? Why?

  1. c) Now add Columns E and F to the spreadsheet. In E compute how much the order quantity inΒ  Column A differs from Q* as a % …[(Order quantity – 4472)/4472] * 100.

In F compute how much the annual cost associated with ordering the quantity in Column A differs from the minimum annual cost. To keep this positive compute this by {[max(total cost with Q from column A total cost with Q=4472) – min(total cost with Q from column A total cost with Q=4472) /4472]} * 100.

Now plot these two with the difference in total cost (Column F) on the y-axis vs. difference in order quantity (Column E) on the x-axis.

  1. d) If you were showing these plots to management would you argue that the EOQ is robust or not? That is would Q* be a good place to start negotiating an order quantity with the supplier?