The importance of having a visual representation of observed data points can now be illus- trated. Suppose that the production manager of ABC Manufacturing has only a few recorded observations from which to derive a learning rate, Lr, namely production times T1 (= 10.9),
T3, T6, T8, T9, and T12. He has decided to use the second option provided in the learning curve
model of Figure 5.15, and obtains the following results:
Unit pairs (Ti, Tj) (T1, T3) (T1, T6) (T1, T8) (T1, T9) (T1, T12)
Learning rate, Lr 75.6% 81.9% 78.1% 78.2% 78.1%
From these calculations, it is clear that the production times for units 3 and 6 are unreli- able, giving learning rates that vary by –2.85% and +3.45% from the trendline value of 78.45%. This same conclusion can also be reached by simply observing the ‘closeness of fit’ of units 3 and 6 in the scattergraph of Figure 5.16. These two points are furthermost from the trendline.
EXCEL FUNCTIONS USED IN MODEL-BUILDING
The models developed in this chapter use Excel’s five depreciation functions – SLN, DB, SYD, DDB and VDB – which have been fully discussed in the section on ‘Depreciation’. The following eight Excel functions are used for the first time and are explained below. The user should remember that a comprehensive on-line help facility is also provided by Excel.
A 1 2 3 4 5 6 7 8 9 B C D E F G H Product name Gizmo Gadget Widget Sprocket 10 25 8 40 £10.00 £12.50 £20.00 £4.50 1 2 3 4 −10 4 3 6 No. in stock Product Year price Cash flow Sample Figure
Figure 5.17 Sample figure.
1. COUNTIF: COUNTIF(range, criteria)
searches a cell range and counts cells specified by given criteria
range = the range of cells to be examined
criteria= the specified criteria in the form of a number, text, or an expression. For example criteria can be specified in the form 32, “32”, “>32”, “apples”.
Examples: COUNTIF(C4:C7, “>20”) in Figure 5.17 returns 2, i.e., the number of cells greater than 20.
COUNTIF(B4:B7, “=Gadget”) in Figure 5.17 returns 1, i.e., the number of cells containing the word ‘Gadget’.
2. EXP: EXP(number)
returns e raised to the power of number where e is the base of the natural logarithm. Examples: EXP(1)= 2.7183 (the approximate value of e); EXP(2) = 7.3891; EXP(LN(3)) = 3
3. HLOOKUP: HLOOKUP((Table value, Lookup table, Column no, Nearest)
searches the top row of a range of cells (the Lookup table) for a specific value (Table value). It then returns a corresponding value from a different row in the table. Note: HLOOKUP is very similar to the VLOOKUP function, which has been fully docu- mented in Chapter 2.
Example: HLOOKUP(“Gizmo”, B4:D7, 4) in Figure 5.17 returns Sprocket. Since ‘Gizmo’ is located in the first column of the lookup range, the corresponding value in the fourth row is Sprocket.
4. LN: LN(number)
returns the natural logarithm of number.
Examples: LN(2.7183)= 1. LN(86) = 4.454347. LN(EXP(4)) = 4 5. MATCH: MATCH(value, array, type)
returns the position of value in the one-dimensional cell range array. Use MATCH instead of one of the LOOKUP functions when the position of a matched item is required instead of the item itself.
value= the value to be matched. ‘value’ can be a number, text, or logical value. array= the specified one-dimensional cell range.
type = 0 in which case, search for an exact match.
= 1 in which case, search for the largest value that is less than or equal to value. = −1 in which case, search for the smallest value that is greater than or equal to
value. If type is omitted, it is assumed to be 1.
Example: MATCH(“Widget”, B4:B7) in Figure 5.17 returns 3 because ‘Widget’ is in the third position of the cell range B4:B7.
6. MIN: MIN(array)
returns the smallest number in an array.
Example: MIN(D4:D7) in Figure 5.17 returns the value £4.50. 7. OFFSET: OFFSET(cellRef, rowOffset, colOffset, height, width)
examines a cell range and returns a cell value which is offset from a given starting point by a specified number of rows and columns.
cellRef = the starting position in a cell range from which the offset is required. rowOffset = the vertical offset. A positive number moves down; a negative number
moves up; zero performs no movement.
colOffset = the horizontal offset. A positive number moves right; a negative number moves left; zero performs no movement.
height,width= the number of rows, columns in the offset range. If these values are omitted, the offset range is assumed to be the same size as the cellRef range.
Examples: OFFSET(B7,−2, 2) in Figure 5.17 returns £12.50 which is the value in cell D5. OFFSET(D4, 0, –2) returns ‘Gizmo’ which is the contents of cell B4.
8. ROUNDUP: ROUNDUP(number, digits) rounds a number up, away from zero.
number= the real number which is to be rounded up.
digits = the number of digits to which the number is to be rounded. If digits > 0, the number is rounded up to the specified number of decimal places. If digits= 0, then the number is rounded up to the nearest integer. If digits< 0, the number is rounded up to the left of the decimal point.
Examples: ROUNDUP(27.2, 0) = 28; ROUNDUP(3.142, 2) = 3.15; ROUNDUP (465.83,−1) = 470.
EXERCISES
5.1 The MightyBig Company is considering three sites for the location of its new manufacturing plant. Annual estimated revenue along with fixed and variable costs for each site are shown in Table 5.13. Sales volume is expected to be 30,000 units per year. Set up a cost-volume-profit model and hence find the best location using break-even analysis.
Table 5.13
Site Revenue Fixed costs Variable costs
location per unit (£’000s) per unit
A £70 300 £65
B £70 500 £45
C £68 700 £30
(Answer: For locations A,B,C the break-even points are: BEPu = 60,000, 20,000, 18,421 and BEP£ = £4,200,000, £840,000, £536,842. Corresponding profit (loss) figures are –£150,000, £250,000, £440,000. The best site is C even though annual fixed costs are much higher than for the other two sites.) 5.2 A bakery is considering the purchase of a new delivery van costing £20,000. The estimated running costs of the van are £3000 in the first year, rising annually by 15% thereafter. If the van’s depreciation is 20% per annum and the cost of capital is 9%, how often should the van be replaced? How would your answer be affected if (i) the rate of depreciation decreased/increased (ii) the cost of capital altered? (Vary depreciation from 15% to 25% and capital cost from 6% to 12%).
(Answer: Modify the equipment-depreciation model of Figure 5.3 by introducing a ‘rate of depreciation’ factor, DR. Cells C11:D18 will now contain appropriate formulae.)
Rate of depreciation 15% 20% 25% Capital cost 6% 9% 12% Minimum annual cost (£s) 7580 7988 8205 7566 7988 8399
Replace item in year 4 6 7 5 6 6
5.3 A hospital has a permanent need for a piece of theatre equipment costing £48,000. The fixed annual running cost is £2000 and maintenance charges are estimated at £3000 in the first year, rising annually at a rate of 15% thereafter. The equipment’s useful life is eight years and equipment cost is written off on a straight-line depreciation basis. If the cost of capital is calculated to be 10%, when is the best time to replace the equipment, and what is the annual equivalent cost? Would
the answer change if the equipment’s life was extended to 12 years, given that depreciation is zero over this extended period?
(Answer: Modify the equipment-depreciation model of Figure 5.3 by introducing a ‘fixed running cost’, FC. Cells C11:D18 will now contain appropriate formulae. When an eight-year period is considered, the equipment should be replaced after five years with a corresponding equivalent cost of £15,653. When a twelve-year period is considered, the optimum replacement period now becomes eleven years with a corresponding cost of £15,216.)
5.4 Bloggs Engineering has recently carried out a detailed cost analysis of its machinery. Having studied past records of the maintenance costs and depreciation of its machines, the company has found that for a machine with a capital cost C , the maintenance cost and resale value at the end of year ‘n’ of its life can be expressed by the following formulae:
Maintenance cost= 0.08C (1.2n+ 2) Resale value = C/log
e(1.5n + 2)
Bloggs Engineering wants to find the optimum replacement time for the machines over a period of eight years, given that the annual cost of capital is 10%.
(Answer: Take a copy of Figure 5.3 and use the template of Table 5.14 to make the necessary modi- fications. Note that any value can be assigned to capital cost C , e.g., £10,000. Equipment should be replaced every six years, with the minimum cost being £4,867.)
Table 5.14 Replacement analysis – Bloggs Engineering.
Cell Formula Copied to
C11 0.08*F$3*(1.2ˆB11+ 2) C12:C18
D11 F$3/LN(1.5*B11+ 2) D12:D18
5.5 Consider Merlene Olonga’s equipment-leasing problem discussed earlier in the chapter. She is not satisfied with the answer obtained by the model of Figure 5.4, which found that a four-year leasing policy was the most economical solution. Merlene knows that her company’s microcom- puter system must be kept up to date and has decided that any equipment can be retained only for a maximum period of three years. By modifying the Cijcost options to reflect Merlene’s new request, re-run the model and find Merlene’s best option under these new conditions.
(Answer: Set the current option of C15equal to a very large cost, say £99,999, and re-run the model.
The new policy is C12+ C25, giving a total cost of £34,500.)
5.6 The GreenFingers Garden Centre hires out lawnmowers over a period of five months. Because of maintenance, many of the lawnmowers are out of service for varying lengths of time and are therefore losing money. GreenFingers’ table of hire rates, shown in Table 5.15, reflect these hidden expenses. The table also takes supply and demand into account, with hire rates being reduced towards the end of the summer. Unfortunately, the complexity of GreenFingers’ costs table presents a problem for Fred Flint. He would like to know the cheapest way of hiring out a lawnmower for a period of five months. Use the equipment leasing model of Figure 5.4 to help solve Fred’s problem.
(Answer: The cheapest option for Fred Flint is to hire out a lawnmower three times – at the beginning of months 1, 2, and 4 giving a total cost of £145, i.e., C12+ C24+ C46.)
Table 5.15 Hire rates – GreenFingers Garden Centre. Month j= 2 j= 3 j= 4 j= 5 j= 6 i= 1 £30 £65 £95 – £155 i= 2 £40 £60 £95 £120 i= 3 £45 – £100 i= 4 – £55 i= 5 £22
5.7 The Bedrock Company’s headquarters contains 1000 electric light bulbs. When any bulb fails, it is immediately replaced. From past records of 500 bulbs, the following failures table has been obtained:
Age at failure (months) 2 3 4 5
No. of failed bulbs 100 200 150 50
(i) Calculate the expected number of bulbs to be replaced in each of the first seven months. What rate of failure can be expected in the long run?
(ii) Replacement of individual bulbs costs £0.50 each. What is the average monthly replacement cost? An alternative policy is to replace all the bulbs after a fixed number of months at a cost of £200, as well as replacing any units that fail at the individual price of £0.50 each. Is this preventative maintenance policy justified, and if so, in what month should it come into operation?
(Answer: In order to be consistent, the failures table above must also include month 1 with zero failures. Failures in the first 7 months are 0, 200, 400, 340, 260, 288, and 328. In the long run, the average number of failures per month is 303. The average monthly individual replacement cost is therefore £151.50. The cheapest replacement policy is group replacement every two months at an average monthly cost of £150.)
5.8 Barry Lime is marketing manager of SuperMicros, which has recently begun to lease micro- computers. The company has set aside a total of 200 micros for this purpose. Barry has obtained recent market research data, showing the distribution of 100 similar micro leasing policies:
Length of lease (years) 1 2 3 4
No. of policies 20 40 30 10
Barry would like to find (i) the number of new leasing policies required each year for the next four years in order to maintain SuperMicros’ rentals at 200 (ii) the average length of the leasing period (iii) the average number of new leasing policies required each year.
(Answer: This exercise is equivalent to a statistical equipment replacement problem and is solved using the model of Figure 5.5. (i) 40, 88, 94, 86 (ii) 2.3 years (iii) 87.)
5.9 The Things A’Plenty Company has recently received several orders for its new solar-assisted car. The number of customer orders for the next five months are 2, 6, 10, 10, and 15 respectively. The first three cars have already been manufactured and their production times have been found to be 500, 400, and 350 hours respectively. Having studied these times, the Company’s engineers estimate that an 80% improvement in production time can be achieved. Things A’Plenty would
like to know how much labour is required to meet its commitments to customers. The company has asked its engineers to draw up a table of monthly labour requirements. Company data shows that an employee works twenty 8-hour days each month, i.e., 160 labour-hours per month. If production-time data for the next three units is found to be 320, 298, and 280 respectively, use the model of Figure 5.16 to check out the accuracy of the Company’s assumption of an 80% learning curve.
(Answer: Extend the model in Figure 5.15 to include details for 45 units.)
Month 1 2 3 4 5
No. of cars required 2 6 10 10 15
Actual unit nos. 1–2 3–8 9–18 19–28 29–43
Production hours 900.0 1773.0 2185.1 1815.5 2373.5
No. of employees 5.6 11.1 13.7 11.3 14.8
When the first six production times (500, 400, 350, 320, 298, 280) are entered into the model of Figure 5.16, a learning curve value of 79.96% is obtained, confirming the engineers’ estimation of 80%. The curve fit is almost perfect with a correlation coefficient of R= 0.9999. The equation is y= 500x−0.3227.
5.10 Sharon Smith is sales manager of the KleenUp Company. Sharon intends to launch a monthly sales campaign during the coming year. She is currently preparing a budget for the labour costs required to handle the sales campaigns and has estimated that the first one will require 100 hours at a cost of £15 per hour. From previous experience, Sharon knows that the salesforce’s learning experience follows a 78% learning curve. The KleenUp Company would like to know what the first four campaigns will cost.
(Answer: Using the learning curve model of Figure 5.15, the total time required for the first four campaigns, CUM4= 306.3. Total costs = 306.3 × £15 = £4594.50.)
REFERENCES AND FURTHER READING
Heizer, J. and Render, B. (2003) Production & Operations Management (7th edn), Prentice Hall, New Jersey.
Jackson, M. (1988) Advanced Spreadsheet Modelling with Lotus 1-2-3, John Wiley & Sons, Ltd, UK.
Ragsdale, C. (2004) Spreadsheet Modeling and Decision Analysis (4th edn), Thomson South- Western, USA.
Urry, S. (1991) Introduction to Operational Research, Longman Scientific & Technical, Essex. Wilkes, F. (1980) Elements of Operational Research, McGraw-Hill, UK.
Winston, W. (2003) Operations Research: Applications and Algorithms (4th edn), Duxbury Press, California.