Quantitative Analysis

Quantitative Analysis: Excel Solver



Quantitative Analysis: Excel Solver



Question 1

The EOQ of a product is calculated using the following equation:

Where,Co=Ordering costs per order

Cc=Carrying cost per unit p.a.

D=Annual Demand for stock

Q=Economic Order Quantity

For the company Toys For You, we are to determine a suitable EOQ and re-order frequency for the following 12 months. We will then analyse the effect of the different levels of demand on this recommendation. The following predictions about demand and the probability were made, after carrying out research on the production of board game (Cc and Co already given):

Due to the difference in probability of each scenario the most suitable EOQ for the company is best calculated using the expected (mean) demand levels, as this would take into account the relative probabilities and varying demand levels. To calculate EOQ based on expected demand, we multiply the probability by the demand level for each scenario (Column D above); accumulating this to achieve the expected annual demand (Cell D8 above). We then substitute the expected demand into FORMULA 1 along with Cc and Co to obtain the following year's recommended EOQ for board games (Cell H8 above). The formulae used for each of these calculations are shown in the respective columns and cells below.

To determine the number of orders per annum, we divide the expected annual demand (CELL D8 Table 1) by the recommended EOQ (CELL H8 Table 1), which equals 34. To then work out the re-order frequency, we divide the no of days in a year by the number of orders per annum, which equals 11. Therefore it is recommended that Toys For You order 2288 units of stock every 11 days; assuming demand in this year doesn't vary from the predicted value and the probabilities of each scenario stay the same. We must also assume that carrying costs and order costs do not vary. However this in the real world is unrealistic since if demand varies so does the number of orders of stock and it is known that economies of scale allow for reduced order costs per unit as orders increase. Furthermore, carrying costs also vary with directly proportion to sales volume. For these reasons we are assuming all these variables remain constant for the given recommendation to have a required effect.

The effect of Changing Demand

The effect of varying demand for board games is illustrated by the table below (Table 2):

If the recommended EOQ is used and the 'crisis continues' the total costs (£39,996) seen in the table above are far less than expected (£61,188); a similar result to that of a 'slow recovery'. On the other hand if the company saw 'Medium' or 'Fast recovery' the costs are seen to be mildly greater. However this is under another assumption that order costs per unit are unchanged, which we know from the theory above, may not hold true. If we simply examine the EOQ's (Column H in Table 1), in both a 'crisis' and 'slow recovery' situation the EOQ is less than the ...