Division of Research Graduate School of Business Administration The University of Michigan March 1981 RETAIL SALES AND INVENTORY BUDGETING: A NEW APPROACH Working Paper No. 259 Robert Haessler Paul Sweeney F. Brian Talbot The University of Michigan FOR DISCUSSION PURPOSES ONLY None of this material is to be quoted or reproduced without the express permission of the Division of Research.

ABSTRACT This paper presents a new approach to the periodic sales and inventory (stocks) budgeting process performed by most large department stores. This process involves determining the allocation of sales and inventory budgets among the various merchandising departments that will maximize total financial return. Whereas the most prevalent budgeting approach found in retailing can be viewed as a "top-down" distribution of sales and inventory budgets based on some rough measure of the historical performance of departments, the proposed method reverses and rationalizes this process. It is primarily a "bottom-up" approach that systematically allocates sales and inventory budgets on the basis of historically tempered forecasts of marginal return on investment (MROI) in each department. This budgeting procedure was developed for a large midwestern department store chain with three hundred departments. A central feature of the procedure is the use of a separable linear programming model that finds that mix of investments in inventory that optimizes MROI performance for the company.

1. INTRODUCTION This paper presents a new approach to the periodic sales and inventory (stocks) budgeting process performed by most large department stores. This new approach was developed by the authors for a large midwestern department store chain consisting of three hundred departments. Although the retailer is unnamed and the data used in this paper are hypothetical, the following discription is detailed enough to permit implementation of the procedure by other retailers. The original impetus for the project came from the parent corporation's decision to use the return on investment (ROI) as a standard for evaluating subsidiary and management performance. In the retail industry, where measures such as gross margin or inventory turns have typically been used to gauge performance, such a change has stirred a top-to-bottom reevaluation of the company's traditional ways of doing business. We became involved in studying this problem at the request of a company manager who saw the situation as an opportunity to apply a more rational, sophisticated technique to what had been a "seat-of-the-pants" budgeting approach. The budgeting process to which the approach presented in this paper applies is the semiannual determination of retail sales and average inventory budgets for the company's approximately three hundred retail departments. In the past this budgeting procedure was performed in the following manner. The president, in consultation with management of the parent corporation, would determine the total company sales and inventory goals and would allocate them, on the basis of previous period levels, to the five General Merchandising Managers (GMM's). The GMM's are responsible for broad retail classifications of merchandise. The GMM's would in turn use their own methods to further allocate goals to their Divisional Merchandising Managers (DMM's), who are

-2 - responsible for smaller, more uniform groupings of merchandise. Finally the DMM's would determine, usually in consultation with the appropriate buyers, how these figures would be divided among their retail departments. Although in some cases there might be some upward pressure on the DMM's and G4M's to revise their allocations, the general approach to budgeting has been top-down, with most of the allocations being based on past-period sales and inventory levels. When this budgeting process was complete, and each department had its sales and inventory goal, these goals would become the basis for open-to-buy authorizations for stock purchases during the budget period. This paper describes the development of an alternative approach to this budgeting process. Rather than top-down, it is essentially a bottom-up approach; rather than basing allocations on past performance, it uses forecasts for the period being budgeted; and rather than being a mere "seat of the pants" adjustment to previous-period levels of sales and inventory this approach allocates resources on the basis of the more rational criterion of marginal return on investment (MROI). It should be emphasized, however, that the model in no way changes the budgeting process from its limited use as a planning tool. Neither the current nor the proposed system determines or affects actual purchase decisions at the departmental level. Rather, they only establish performance goals for both the aggregate and departmental levels. Prior to embarking upon this study both manual and computerized searches were made of the relevant literature, but nothing of a similar nature was found in either the literature of retailing or in applications of separable programming. Discussions with retailing executives also indicated that the approach suggested here was novel. Hence, we believe that this is the first application of its kind, as well as the first report in the literature of such an application.

-3 - The presentation is given in five sections. Section 2 describes an introductory linear programming model which helps capture the essence of the problem. In Section 3 we present an improved separable linear programming approach. A numerical illustration of the separable programming model is given in Section 4. Summary comments are found in Section 5. Appendix 1 describes the formulas used to calculate return and investment values. Appendix 2 contains a sample input matrix for the separable programming computer package, and a description of a computer program that was developed to simplify the input preparation procedure. 2. AN INTRODUCTORY LP MODEL An introduction to the use of ROI as the basis for the allocation of the company sales and inventory goals can be illustrated with a simple linear programming model. The formulation is as follows: m n Maximize Z = I r..x. (1) j=l i=l 1J 1J where r.. is the rate of return on investment for department i of division j and x.. is the investment in department i of division j. m n S.T.: I I x < F (2) j=l i=l ij and for each j: n t..x > A (3a) i=l 1J 1 j n t.x.. < B (3b) i=1 'j ij j

-4 - where tij is the average number of stock turns for department i of division j and A. and B. are sales minimums and maximums J J for each division j. In the objective function (1), total company return is maximized, and the decision variables are the levels of investment in each department, x.i. As a surrogate for investment this model uses the average level of inventory, one of the two budget figures traditionally allocated.' In the above formulation, the total company average inventory goal, F, functions as a constraint on the total level of inventory investment (2). However, the investment in inventory is no longer allocated in the former top-down manner. Rather, the average inventory goal for each department is determined as the solution to the LP problem. In order to prevent the allocation of inventory investment to departments in which the rate of return is less than a company minimum standard, the rates used in the objective function, r i, are each department's positive or negative deviation from that standard. For example, if the company's hurdle rate of return is 20 percent and the expected return on investment for department 8 in division 3 is 24 percent, then r83 = 4%. The second traditionally allocated budget goal, total retail sales, is treated in constraints (3a) and (3b). Rather than a single sales goal, though, each division (i.e., each DMM) is assigned a sales range: A. to B.. These limits are based on strategic and merchandising considerations and, as in the traditional approach to budgeting, are determined by top management. In this formulation both the rates of return on investment (r..) and the average stock turns (t..) are calculated from past-period performance. 1J

-5 - It should be clear, however, that this simple approach contains a major flaw. By using past performance as the basis for the establishment of the rates of return in the objective function, the model assumes that these rates will continue to be operative in the future and that a department's rate of return is uniform over its entire sales range. Neither of these assumptions is realistic. It is considerably more reasonable to expect that, with changing business conditions, seasonal variations, and changes in taste, departmental performance will vary considerably from season to season and from year to year. Likewise, it is important to properly apply the concept of marginal return on investment (MROI). While a department's average ROI might be adequate for evaluating overall performance and profitability, it is not appropriate for deciding upon a future level of funding to support sales. What should be used as the basis for such decision making is the MROI from each additional dollar of investment in inventory above a strategically determined departmental minimum. It is this concept which we used in the development of the separable programming approach. 3. A SEPARABLE PROGRAMMING APPROACH The key assumption of this approach is that each retail department has a realizable range of sales levels for the period being budgeted. Attaining any sales level is based on such controllable factors as average stock level, average markup percentage, dollar quantity of markdowns, and level of expenses. Whereas in some departments increased sales might be generated by taking more markdowns or increasing advertising expenditures, in others the key factor might be the improved selection provided by an increase in the average level of inventory. Because only the DMM's and their buyers have the necessary working familiarity with each department to ascertain the potential sales levels and the actions required to attain those levels, it necessarily falls to them to

-6 - produce these forecasts. Thus, as a first step in the budgeting process, the DMM's would develop data for each department in the format shown by Table 1. In the company's traditional approach to budgeting, the DMM's would develop a single forecast for each department, consisting of figures for sales, average inventory, markup, and markdown. Under the new approach, a set of alternative forecasts must be developed. For the sample department shown in Table 1, it is assumed by the forecaster that sales are attainable in a range from SMin to SMa, and that each of the four levels shown requires specific values for the controllable factors. It is predicted, for example, that sales of S1 will occur when actions Al, ml, M1, and E1 are selected. Because each department will be budgeted at least its minimum forecast level of sales, it is important that sufficient administrative pressure be applied to the DMM's to prevent overly optimistic minimum sales forecasts. This might be done by pegging the minimums to the actual sales level of a previous period. The reason for setting the minimums at some attainable level rather than simply at zero is to guarantee that the model will allow departments whose performance is relatively poor to continue to exist if management so chooses. Such existence is presumed to be desirable for strategic reasons, such as to maintain a store's image as a general merchandiser, to allow the department to function as a loss leader, or to make it possible to continue offering certain prestige items which lend an aura of quality. It is expected that the DIM's would arrive at reasonable maximum forecasts, because they will be held accountable for meeting whatever sales goals result. Each department's goal for sales and average inventory, is based on the MROI of the increments above each department's minimum strategic level of sales. Figure 1, which should be viewed in combination with Table 1, illustrates this approach. For each of the four sales levels in Table 1, there are

-7 - Table 1: DMM Forecast Matrix for Sample Department 1 Sales at Strategy Retail ~3 S5Max 2 S2 1 S1 0 ^SMin Average Inventory at Retail A3 A2 A1 A0 Average Markup Percent m3 m2 m1 m0 Markdown Dollars M3 M2 M1 M0 Expense Dollars E3 E2 E1 E0 Note: Thus, for example, it is assumed by the forecaster that sales in department 1 can be attained in a range from SMin to SMax, and that each of the four levels shown is generated by its own strategic mix. Sales of S are predicted to be generated when a strategy consisting of A1, mi, M1, and E1 is adopted.

-8 - Figure 1: ROI and MROI for Sample Department 1 Return Dollars R 3 R 2 R 1 R 0 Note: These figures may be viewed in combination with Table 1. Thus, for example, strategy 3 in Table 1 results in R dollars of return and 3 I dollars of investment. The ROI for department 3 1 at level 3 would be R /I. The MROI for an 3 3 increase in sales from, say, level 1 to level 2 is the slope of the segment connecting those two points in the upper figure at left. The MROI for each interstrategy increment is given in the lower figure at left. I0 I1 12 13 Investment Dollars Incremental MROI z L 1~~~~~~~~~~~~~~~~~~ 0-1 1-2 2-3 Increment

-9 - corresponding investment and return amounts illustrated in Figure 1. The ROI for any particular strategy can be found by dividing its corresponding return amount by the appropriate investment figure. More important for our purposes, however, is the NIROI of each of the interstrategy increments. These are simply the slopes of the segments in the upper graph of Figure 1. It is this rate, indicated in the lower graph of Figure 1, which determines the advisability of adopting strategies above the departmental minimum. In order to calculate these MROI's, the investment and return dollars which correspond to each level of a department's forecast must first be determined. The formulas used to calculate these amounts are provided in Appendix 1, and are, in practice, calculated by computer. The data now available for each department are given by Table 2. Having made the calculations of investment and return for each sales alternative and of marginal investment and marginal return for each departmental increment, an optimal level of investment in each department can be determined using the IROI's of each increment. The selection of these departmental optimal levels of investment is done using a separable linear programming algorithm. The formulation is as follows: n+l Maximize Z = I x.f(xi) (4) i=l where x. is the investment in department i and f(xi) is the rate of return on investment at x. for department i. The summation is over n departments plus a dummy department for which the rate of return is the company minimum standard.

-10 - Table 2: Complete Matrix for Sample Department 1 Strategy 3 2 1 0 Sales at Retail SMax S2 S1 SMin Average Inventory at Retail A3 Average Markup Percent m3 A2 A1 A0 Markdown Dollars M3 M2 M1 M0 Expense Dollars E3 Investment Return Dollars Dollars 13 R3 2 R2 Io Note: Investment and return dollars are calculated from the input data, which appear in the other columns above, and from other company constants. The formulas used are provided in Appendix 1. Marginal return and marginal investment amounts are calculated as the differences between the investment and return amounts for the strategic levels involved. The MROI for any increment is its marginal return divided by its marginal investment.

-11 - S.T.: x. > c. (5a) 1- 1 x. < di (5b) n+l x g(x ) < F (6) 1=1 i i n+l. x h(x ) < S (7) i=l i i n+l I x =I (8) i=l i The objective function (4) maximizes total return from all departments. In equations (5a) and (5b), c. and d. are departmental investment minimums and 1 1 maximums, calculated at the minimum and maximum sales forecast levels of each department. For the sample department in Table 2, c. would equal I0 and di would equal 13. Thus (5a) and (5b) effectively constrain each department to the range of the D4MM's sales forecast. In equation (6), F is the overall company goal for average inventory, one of the two figures traditionally allocated in the budgeting process. In our formulation, it serves as a constraint on the total level of investment in the business. However, since average inventory is no longer being used as a surrogate for investment, a conversion factor, g(xi), is used to convert from true investment to average inventory. In equation (7), S is the total company goal for retail sales. Again, this is one of the figures traditionally allocated during the budgeting process. It also serves as a constraint on total investment. As in equation (6), h(xi) serves as a conversion factor from investment to retail sales. In equation (8), I is merely the sum of all of the departmental maximum levels of investment, the d 's of equation (5b). This constraint assures that 1

-12 - any potential investment which is not allocated to an actual department will be allocated to a dummy department, where it returns the company standard minimum rate. This prevents any investment in real departments in which the MROI is less than the company standard. Problems of this type can be solved, using IBM's NPS 360 [1]. Appendix 2 provides a sample input matrix for MPS 360 and a discussion of some of the steps used to simplify data preparation and input. The result of this procedure is an allocation of the company sales (S) and average inventory (F) goals among the retail departments within ranges established by the DMM's in such a way that: 1) no department is funded at a level above its minimum if the MROI from that marginal investment is less than the company standard minimum rate and 2) those incremental investments above the departmental minimums which are selected represent the optimal mix available from an MROI standpoint. 4. SAMPLE PROBLEM Tables 3 and 4 present a numerical example for a small imaginary company consisting of only four departments, 111 through 444. Each department is forecast to have three strategic alternatives, levels 0, 1, and 2, with each department demonstrating a different relationship between the decision alternatives (stocks, markup, markdown, and expense) and resultant sales. All numbers shown as dollar amounts can be regarded as being thousands of dollars. The return and investment amounts are calculated using the formulas described in Appendix 2. The ROI figures give the departments' average ROI for each strategic alternative, which is simply the appropriate return divided by the appropriate investment. For example, department 111 at level 0 with return of

-13 - Table 3: Sample Problem Data Summary DPT 111 111 111 LEVEL 0 1 2 NET SALES AVG STOCKS MARKDOWNS MARKUP % EXPENSES RETURN INVESTMENT ROI MROI.0970 $100 $120 $140 $30 $35 $40 $ 7 $ 9 $10 50% 51% 51.5% $ 40 $ 40 $ 40 $ 4.345 $ 9.023 $13.774 $ 44.788 $ 49.293 $ 53.972.1831.2552 1.03 1.01 222 222 222 333 333 333 444 444 444 0 $250 1 $300 2 $325 0 $185 1 $200 2 $210 0 $140 1 $175 2 $195 $50 $60 $70 $20 $28 $32 $33 $35 $40 $15 $15 $15 $ 2 $3 $4 $ $ $ 48% 48% 48% 51% 51.8% 52% 39% 40% 41% $ 75 $ 80 $ 82 $100 $106 $109 $ 30 $ 30 $ 38 $19.541 $28.348 $32.963 $ 1.116 $ 2.614 $ 3.727 $11.871 $18.758 $19.791 $ 91.845 $105.977 $114.930 $ 87.288 $ 95.161 $ 99.733 $ 47.439 $ 52.383 $ 60.046.2128.2675.2868.0128.0275.0374.2502.3581.3296 0.62 0.51 0.19 0.24 1.39 0.13

-14 - Table 4: Sample Problem Solution Total Sales Constraint: $775 Total Stocks Constraint: $158 DPT 111 222 333 444 NET SALES AVG STOCKS $140 $40 $275 $55 $185 $20 $175 $35 RETURN $13.774 $23.945 $ 1.116 $18.758 INVESTMENT $53.972 $98.911 $87.288 $52.383 Total $775 $150 $57.593 $292.554 $8 of the total stocks goal is unused Total company ROI would be $57.593/$292.554 =.1969 The strategies selected in each department are: Dpt. Ill —Level 2 Dpt. 222 —midpoint of the 0-1 increment Dpt. 333 —Level 0 Dpt. 444 —Level 1

-15 - $4.345 and investment of $44.788 has an ROI of 9.7 percent (4.345/44.788). The investment and return associated with the increase in sales from level 0 to level 1 in department 111 results in an MROI of 103 percent. As Table 4 indicates, the total sales and stock goals to be allocated in this example are $775 and $158, respectively. When run through the IBM MPS 360 separable programming package, the optimal levels selected for each department are as indicated in Table 4. In this particular example the full sales budget is allocated but $8 of the inventory goal remains unused. For department 111, where sales increments have the relatively high MROI's of 103% and 101%, the maximum sales level is selected. For department 333, where MROI's are relatively low, the minimum level is selected. In department 444, the middle level is selected because of the high MROI of the first increment and the significantly lower MROI of the second increment. Finally for department 222, the remainder of the sales budget is allocated by selection of 50 percent of the first sales increment. IWhile the solution for a small problem such as this one with such widely disparate MROI's is relatively trivial, the solution of the problem for a real company of about three hundred departments is not. Organizing the data and finding the optimal solution can only be done with the help of a computer. 5. SUMMARY This paper has presented a new and rigorous "bottom-up" budgeting procedure which was developed for a large midwestern department store chain. The approach taken involves the participation of Divisional Merchandising Managers in the development of realistic budget goals for each retail department, and the use of marginal rates of return as the budget allocation criterion. On the basis of these goals, a separable linear programming model is used to optimally

-16 -allocate sales and inventory budgets among all departments. The proposed method is a far more rational and systematic approach to the budgeting process than the essentially "seat-of-the-pants" methods currently in use.

-17 -APPENDIX 1 The formulas used to calculate return and investment are as follows: Return = k [GM-E + sc r 1+k t 1-T L Investment = AI + AR - AP + - [k(AI+AR-AP+T -. AI) + E] 1+k 360 The terms used in the above equations are defined as follows: T: the corporate tax rate; currently using 0.5 k: the cost of capital; used for discounting and calculation of opportunity costs; using 115% of prime GM: gross margin = (markup %) (net sales plus markdowns) - (markdowns) E: expenses s: net sales c: % of departmental sales made on credit r: annual rate of interest on customer receivables; using 0.18 0: % of receivables on which interest is received; remainder are paid off within 30 days t: receivables turns per year AI: average inventory at cost = (1-markup %) (average inventory at retail) AR: average receivables at cost = (1-markup %) (net sales) (c)/t AP: average accounts payable for inventory; currently using (.25) (AI) T: inventory turns = (net sales)/(average inventory at retail) L: average lag from payment for inventory to receipt of sales revenue; currently using 160 days

-18 - APPENDIX 2 Table 5 contains a sample input matrix for the IBM MIPS 360 separable programming package. The following is a line-by-line explanation of that matrix: line 1: this line serves as the heading for the matrix; xl, x2, and x3 represent investment in departments 1, 2, and 3 xl, xlb, etc., denote the increments between each department's strategic alternatives xlf(xl) and other columns similarly labeled are the return dollars for each department the dummy department is allocated investment not used by the other real departments the RHS1 column gives the right hand side values for the various equations in the matrix line 2: this is the objective function of the separable programming algorithm; total return is being maximized, including in this example any investment in the dummy department at a 12% standard rate line 3: this equation represents the total sales constraint; S' is equal to the actual total sales constraint (S) less the sum of the departmental minimum sales amounts sla is, for example, the sales increment between the department 1 sales minimum and the next higher sales level; in Table 2 it would correspond to S1 minus SMin line 4: this equation represents the total inventory constraint; F' is equal to the actual total inventory constraint (F) less the sum of the departmental minimum inventory amounts

-19 - fla is, for example,.the inventory increment between the department 1 minimum level inventory amount and the next higher level's inventory amount; in Table 2 it would correspond to Al minus A0 line 5: this equation is the investment equality which corresponds to equation (8) in the formulation lines 6-8: these equations define the return amounts which appear in the objective function; line 6, for example, defines department 1 return as RiMIN plus the values assigned to the two increments; the incremental return amounts are rl and rlb; R1MIN is the amount of return in department 1 at its minimum sales level; in Table 2 RiMIN would equal R0 and ra would equal R1 minus R0 lines 9-11: these equations define the investment amounts which appear in the investment equality; their function is analagous to lines 6-8; in line 9, cl is the level of investment for department 1 at its sales minimum, and il and il are la lb incremental investment amounts lines 12-13: these entries give the upper and lower bounds for the variables defined in line 1; cl and dl are the minimum and maximum level investment amounts for department 1; R1MIN and R1MAX are the minimum and maximum level return amounts for department 1; when the solution to the algorithm is found, it takes the form of values between 0 and 1 for the departmental increments; that percentage of the increment's sales, stock, return, and investment is included in solution: for example —if

-20 - the value for column x1a is 1, then the full values of la' fla, rla' and il are included in the respective formulas; if the solution value were 0, then 0 would be substituted for each of those terms in the respective equations The matrix in Table 5 is used to prepare the input stream for the TBM MP 3 60. system. To facilitate the preparation of that input, a program was written which takes the data supplied by the DMM's (see Table 1), calculates the corresponding return and investment amounts, prints a table similar to that shown in Table 2, calculates all the incremental amounts necessary for the calculation of the above matrix, and prepares the MPS 360 input stream. This program has proved to have two major benefits. The printed table has been useful in educating the merchandisers in the concept of ROI, and has served as a convenient method for analyzing past performance from an ROI standpoint. Secondly, the manual preparation of input for MPS 360 would be exceedingly tedious. With the program it is a simple matter not only to prepare it, but also to alter the variables and make iterative runs of the model.

f:4 -21 - Table 5: Input Matrix for IBM's MPS 360 1 2 3 4 5 6 7 8 9 10 11 x1 Xla Xlb x2 2a X2b I x3 X3a X3b xlf(x ) x2f(x2) x3f(x ) Dummy Dpt RHS1 OBJ I.I t I I I I I I I I 1 1 1 I.12 |N I SALES 1 I I I MAX | la slb S2a s2b s3a s | 3b|1< I S' STOCK I i I I I MA_ I fla flb f2a f2b f3a 3bl _____ F'| INVSTI 1 1 I. I I EQUAL 1! 1 I 1 |= I RETURN I 1 1 I I 1 I ral rl, I -1 I I| -RIMIN RETURNI 1 1 II 2 1 rr2a 2b -1 | = -R2MIN 2a 2b __.._,..... RETURN I I 3 j _ _ r3a r3b_ -1 | = j -R3MIN I I.__ 1I I__ I I I GRID I 1 I I 1 1 |-1 ila ilb | I l = I -Cl GRID I | I 2 jI _-1 i2a i2b ___I = I -C2 GRID 1 I I I 1 I J3 _ I ____!-1 | i3a i3b! __ I -C3. I I I I I I I 12 UPPER BOUND 13 LOWER BOUND dl 1 1 1~ d2 1 1 C2 0 0 d 1 c3 0 1 0 RlNLAX R1iMIN R2MAX R2MIN R3MAX R3MIN 00 0

-22 - REFERENCE [1] Mathematical Programming System/360, Version 2, Linear and Separable Programming User's Manual (360A-CO-14X), IBM Corporation, Third Edition, 1971. C