LIBRARY Michigan State ‘ University PLACE ill RETURN BOX to remove this checkout from your record. TO AVOID FINES return on or before die due. DATE DUE DATE DUE DATE DUE ‘, 007 ll I ll i—Tl J MSU ie An Affirmative Action/Equal Opportunity Institution emote”! ADVERTISING MEDIA SELECTION WITH PC BASED LINEAR PROGRAMMING SOFTWARE FOR TOURISM AND TRAVEL ORGANIZATIONS By Sean Arthur Sullivan A THESIS Submitted to Michigan State University in partial fulfillment of the requirements for the degree of MASTER OF SCIENCE Department of Park and Recreation Resources 1991 ABSTRACT ADVERTISING MEDIA SELECTION WITH PC BASED LINEAR PROGRAMMING SOFTWARE FOR TOURISM AND TRAVEL ORGANIZATIONS BY Sean Arthur Sullivan This research effort was undertaken to determine whether the WHAT’S BEST! linear programming package could be successfully applied to the media selection by tourism and travel organizations. The purpose is to evaluate the WHAT’S BEST! software specifically focusing on its appropriateness and ease of application to a realistic media allocation problem involving tourism and travel advertising. To provide a scenario for use in different tourism and travel organizations, secondary data were collected on reach, frequency, and unit costs of advertisements for 59 media variables (e.g., television, radio, direct mail, etc). Nine spreadsheet files were produced from a master spreadsheet file, representing $12, $5, and $1 million budgets using three different budget allocation strategies. WHAT’S BESTl was run on a Lotus 1-2-3 master Spreadsheet file producing optimal advertising impression levels (represented by reach multiplied by frequency) for each of the output files. Results demonstrate that WHAT'S BESTl is a useful tool that could aid tourism and travel managers to better allocate their advertising budgets. To my parents, and Aunt’s D. and N. ACKNOWLEDGMENTS I extend my appreciation to Dr. Donald F. Holecek for his guidance in this research effort and in editing this thesis. I also wish to thank the other members of my guidance committee, Professor Louis F. Twardzik and Dr. Thomas J. Page, for their assistance in the final preparation of this thesis. Appreciation is also due to Richard Stowell, who provided technical assistance and invaluable advice, and to my typist C.V.B. I also wish to extend my sincere thanks and appreciation to my family for all their love and encouragement during my university education. TABLE OF CONTENTS LIST OF TABLES .......................................................................................................... vi LIST OF ILLUSTRATIONS .......................................................................................... viii CHAPTER I. INTRODUCTION ............................................................................................. 1 II. LITERATURE REVIEW ................................................................................... 6 Linear programming models ................................................................ 7 Media selection problems ..................................................................... 10 Media selection in tourism and travel ................................................. 15 Media selection by linear programming ............................................. 16 Ill. METHODS ...................................................................................................... 21 IV. RESULTS ........................................................................................................ 49 V. SUMMARY ...................................................................................................... 84 BIBLIOGRAPHY ............................................................................................................ 94 APPENDICES A. Spreadsheet file, $12 million budget, budget allocation strategy A ............... 99 B. Spreadsheet file, $12 million budget. budget allocation strategy B ............... 112 C. Spreadsheet file, $12 million budget, budget allocation strategy C .............. 125 D. Spreadsheet file, $5 million budget, budget allocation strategy A ................. 138 E. Spreadsheet file, $5 million budget, budget allocation strategy B ................. 151 F. Spreadsheet file. $5 million budget. budget allocation strategy C ................. 164 G. Spreadsheet file, $1 million budget, budget allocation strategy A ................. 177 H. Spreadsheet file, $1 million budget, budget allocation strategy B ................. 190 J. Spreadsheet file, $1 million budget, budget allocation strategy C ................. 203 LIST OF TABLES 1. Comparison of media characteristics ................................................................... 13 2. Location of individual cells in forming the linear programming Spreadsheet constraints .............................................................................................. 34 3. A media category constraint as determined by a special media category formula ........................................................................................................... 41 4. Media category formulas ......................................................................................... 42 5. Maximum percentage of funds for the total budget available for each budget allocation strategy across the media groups in the master spreadsheet file ............................................................................................................. 42 6. Instructional table macro as it appears in the Lotus 1-2-3 master spreadsheet tile ............................................................................................................. 44 7. input menu for optimization in the master spreadsheet file .............................. 46 8. Sample output menu after optimization using fractional media units ............. 47 9. Sample output menu after optimization using integer units ............................. 47 10. Master file. columns 1 thru. 6. rows 1 thru. 3 of computer printout ................ 54 11. Master file, columns 7 thru. 12, rows 1 thru. 3 of computer printout .............. 55 12. Master tile. columns 13 thru. 18, rows 1 thru. 3 of computer printout ........... 56 13. Master file, columns 1 thru. 6, rows 4 thru. 6 of computer printout ................ 57 14. Master file, columns 7 thru. 12, rows 4 thru. 6 of computer printout .............. 58 15. Master file, columns 13 thru. 18, rows 4thru. 6 of computer printout .......... 59 16. Master file, columns 1 thru. 6, rows 7 thru. 12 of computer printout .............. 60 vi 17. Master file, columns 7 thru. 12, rows 7 thru. 12 of computer printout ........... 61 18. Master file, columns 13 thru. 18, rows 7 thru. 12 of computer printout ......... 62 19. Masterfile, columns 1 thru. 6, row 11 of computer printout ............................ 63 20. Master file, columns 7 thru. 12, row 11 of computer printout .......................... 64 21. Master file, columns 13 thru. 18, row 11 of computer printout ....................... 65 22. WHAT‘S BEST! '3 number of tries to find an optimal solution for each output file ........................................................................................................................ 67 23. Sample output macro to display results for fractional and integer values .............................................................................................................................. 68 24. Results macro for the spreadsheet file, $12 million budget, budget allocation strategy A ..................................................................................................... 70 25. Results macro for the spreadsheet file, $12 million budget, budget allocation strategy B ..................................................................................................... 71 26. Results macro for the spreadsheet file, $12 million budget, budget allocation strategy C ..................................................................................................... 72 27. Results macro for the spreadsheet file. $5 million budget, budget allocation strategy A ..................................................................................................... 74 28. Results macro for the spreadsheet file, $5 million budget, budget allocation strategy B ..................................................................................................... 76 29. Results macro for the spreadsheet file, $5 million budget. budget allocation strategy C ..................................................................................................... 77 30. Results macro for the spreadsheet file, $1 million budget, budget allocation strategy A ..................................................................................................... 79 31. Results macro for the spreadsheet file, $1 million budget, budget allocation strategy 8 ..................................................................................................... 80 32. Results macro for the spreadsheet file, $1 million budget, budget allomtion strategy C ..................................................................................................... 82 vii LIST OF ILLUSTRATIONS 1. Computing cost- per- thousand ( C.P.M. ) ............................................................ 14 2. Ten steps for the development of the master spreadsheet ............................... 22 3. Lotus 1- 2- 3 output of the master spreadsheet file in sectional form ............ 50 viii CHAPTER I INTRODUCTION The purpose of this research effort was to investigate the use of linear programming PC software to solve advertising media selection problems for tourism and travel organizations. In the United States, almost $200 billion was spent on'tourism and travel in 1988 (W, 1989, p. 15). In addition, tourism and travel directly generated 5.34 million jobs, $64.3 billion in payroll income, and $34.2 billion in federal, state, and local tax revenue (DEM mm, 1989, p. 15). Current trends in tourism and travel include a rapidly growing cruise market, soaring golf and resort vacations, eve r-increasing interest in cultural tourism and growth in frequency of weekend trips (W 2000, 1989, pp. 17-18). Royal Caribbean Cruise Lines will spend $35 million on advertising for fiscal 1991, up from $14 million in 1990 and $6 million in 1989 (“Last Minute News,” 1990a, p. 45; 1990b, p. 35.) The importance of tourism and travel and the value it represents to various organizations and businesses is evident. In any industry, making the best management decisions possible is crucial to marketers and advertising management teams. It is'difficult to make quality decisions without first reducing advertising data via an objective process to guide media selection in light of advertising budget considerations (Hodge & Clements, 1986, p. 5). The marketer needs to make use of all possible decision- making tools. The subject of advertising appropriations and media selection merits more attention than it often receives in the tourism and travel industry (Aaker & Myers, 1987, pp. 439-441 ; Frey, 1955, p. 1). To aid in making media selection decisions, one of the proven tools is linear programming. Linear programming applications to media selection have been completed by Engel and Warshaw (1964), Stasch (1965), Bass and Lonsdale (1966), Gensch (1968), Dallenbach and Bell (1970), and Schneiderjans (1934). Recently the media selection task has been facilitated by ready availability of advanced modeling techniques and computer technology. Com- puter modeling is a powerful technique with a broad range of applications (Kelly, 1985, pp. 82-83; Martin, 1968, p. 3). Computer models are valuable as they often reveal relationships not apparent before, and having built a model, it is usually possible to analyze it mathematically to suggest new courses of action (Williams, 1985, p. 3). Quantitative methods of analysis are especially crucial to modern marketing and advertising decision makers because ofthe nature of what is riding on their decisions—often millions of dollars and the success or failure of the organization's ventures (Lapin, 1976, p. 13). Currently, end-user computing, the use of computer-based information systems by anyone outside formal data-processing areas, is growing by leaps and bounds (Jarke, 1986, p. 73). This puts the computer’s power in the hands of all management decision makers. Thus, a powerful mathematical program- ming process like linear programming can be used by tourism and travel marketers on their PC systems to aid in making crucial media selection decisions. The process of linear programming was developed by George B. Dantzig and associates in 1947 (Gass, 1969, p. ix), but recent advancements have enhanced access to the process for addressing an array of everyday manage- ment problems. One of the new advancements has been to put the power of linear progra: sming in the hands of the average manager or marketer with a linear programming software package called WHAT’S BESTl. It is designed for use on an IBM PC, or other 100% IBM Compatible with a minimum of 256K RAM. WHAT‘S BEST! needs Lotus 123 Release 1A or 2.0, or Symphony Release 1.1 to run its linear programming applications. It is based on the LINDO optimization software of Professor Linus Schrage. WHAT'S BEST! could aid the average marketer with access to an inexpensive personal computer to make more sophisticated media selection decisions. It overcomes limitations of past linear programming packages due to its ease of operation. Thus, it is accessible to both a manager at a large organization or the small businessperson. Having established the potential importance and value of this decision- making aid (linear programming) for media selection in the tourism and travel industry, the problem statement forthis thesis can be described as follows: Can the WHAT’S BEST! linear programming package be successfully applied to a Spreadsheet model, created in Lotus 123, for a tourism and travel media- selection problem, using secondary data sources? Before proceeding, however, it is useful here for both interpreting what follows and for those interested in applying WHAT’S BEST! in their media . selection decisions to note some of the obstacles and limitations that were encountered at the outset of this project. The first limitation involved obtaining up-to-date data. Second, this author, like many potential users of this technique, lacked extensive in-depth knowledge of linear programming practices and thus had to rely on the claims made by the creators of WHAT’S BEST! as to its relative ease of use. Another limitation faced bythe authorwas that no trials ofthe master spreadsheet file in actual media selection and advertising budget allocation processes could be made. It is difficult to convince an advertiser to release current figures about an advertising plan due to the factor of competitive intelligence. Thus, the Situation depicted in this thesis is hypothetical but not atypical. Although challenging, these obstacles did not prove impossible to sur- mount. Interviews and discussions were conducted with advertising account executives on both the buying and selling levels. Conducting research in a university setting also provides access to a wealth of secondary data through library searches. Finally, technical help is available to the user in computer hardware and software and linear programming techniques. This paper includes five chapters including this introductory chapter. The second chapter presents a literature review which focuses on four areas: (a) linear programming; its history, and use in media selection problems; (b) advertising and its role, media plans, media variables, and reach, frequency, and cost perthousand definitions; (c) tourism and travel advertising processes; and (CI) adiscussion of linear programming applications including the WHAT’S BEST! software. In the third chapter, the methodology employed in this study is presented. It includes a more in-depth discussion of WHAT’S BESTl, sample files, file creation, derivation of figures for the models, budget selection, and budget allocation strategies. After the methods are discussed, results from applications are presented in the fourth chapter. Results are highlighted in tables with accompanying discussions for each of the budget level scenarios devel- oped. The final chapter is a summary chapter that looks at evaluation of the research effort, its implications, and recommendations for further study. CHAPTER II LITERATURE REVIEW Linear programming was first developed and applied in 1947 by George B. Dantzig and associates for the US Department of the Air Force (Gass, 1969, p. ix; Lee, 1976, p. 15). The original name of the technique was “programming of interdependent activities in a linear structure;" Iaterthis became known simply as linear programming (Lee, 1976, p. 15). The term “programming” in this context is not associated with computer programming. Rather, it refers to choosing a course or program of action (Wu& Coppins, 1981 , p. xvi). Dantzig's research was continued by otherscholars, including J. von Neumann, L. Hurwicz, and TC. Koopmanns (Lee, 1976, p. 15). Their early applications of linear programming fell into three main categories: military applications, inter-industry economics, and problems involving zero-sum two-person games (Gass, 1969, p. ix). Afterthese successful applications, linear programming was carried over into the government sector, business and industry, and not for profit organiza- tions (Lee, 1976, p. 16). The first successful solution of a linear programming problem on a high-speed electronic computer occurred in January 1952 on the National Bureau of Standards SEAC machine (Gass, 1969, p. x). By 1970, IBM estimated that 25% of all scientific computing was devoted to solving linear 7 programming and related problems (Wu & Coppins, 1981, p. xvi). The rapid development and widespread use of computers have increased the number of linear programming applications in recent years. Presently, organizations apply linear programming to many managerial problems, such as blending fuel, capital investments, environmental protection, food processing, marketing mixes, per- sonnel assignments, production scheduling, and transportation of goods and services (Anderson, Sweeney, & Williams, 1974, p. 220; Lee, 1976, p. 16). Linear programming has become an important tool not only of modern theoretical and applied mathematics but of business management as well. Linear Programming Models A linear programming problem can be described as follows. Optimize (find the best value of) a dependent variable by finding values for a set of independent variables, given that there are a variety of restrictions on the values of the independent variables. The dependent variable is a function of the independent variables. The objective is to choose a course of action that yields an optimal value forthe dependent variable, referred to as the objective function. The independent variables are referred to as decision variables because a set (or sets) of values must be determined for them. The restrictions on the values of the decision variables are referred to as constraints (Wu & Coppins, 1981 , pp. xvi- xvii). All these relationships and restrictions can be described mathematically. The aim of this research effort was not to provide detailed mathematical models for linear programming. For more in—depth information-regarding linear programming, it is recommended that the reader refer to W W by Gass, an introductory level text of linear programming problems. Should a more advanced level of linear programming be desired, the text by Wu and Coppins titled WW may prove helpful. However, to provide some basic understanding forthe remainder of this research effort, the next section of this paper reveals some of the basics of linear programming problems. A linear programming problem formulation must meet certain require- ments. In addition to the relationship of the dependent and independent variables, a linear programming problem must have an explicit objective criterion to optimize. The objective function may be one of either maximization or minimization ofthe criterion but never both. Secondly, resources must be limited so that a decision problem must involve activities that require consumption of limited resources (99. money). Also, linear programming requires that the total measure of outcome (objective criterion) and the total sum of resource usage must be additive. In addition, linear programming requires a complete divisibility of the resources utilized and the units of decision variables. For problems that require nonfractional values of resource utilization and decision variables, integer programming can be utilized. As well, linear programming implicitly assumes a decision problem in a static time period. To handle linear program- ming problems with uncertain coefficients, parametric linear programming and sensitivity analysis may be applied to the problem. Finally, the primary require- ment of linear programming is linearity in the objective function and in the constraints. The term “linear" implies that all relationships among the decision variables must be directly proportional (Lee, 1976, pp. 17-18). Therefore, all linear programming problems have certain characteristics in common. They are: (a) the system can be described in terms of a series of possible activities, (b) the decision maker has to chose the most appropriate levels for each of the activities, (c) the decision maker is restricted by the availability of limited resources, and (d) there is a well-defined quantity that can be used to compare the desirability of different strategies (Salkin & Kornbluth, 1973,p.4) An LP model is built around the requirements and characteristics of a linear programming problem. The real thinking to modeling goes into the structuring ofthe model and into collecting required data inputs. The construction of the model focuses on isolating the aspects of the problem situation that are most important for analysis, determining relationships between relevant vari- ables, deciding on the appropriate parameters, and evaluating its feasibility (Hughes & Grawiog, 1973, p. 131). A great deal of the value of any linear programming model is dependent on establishing restrictions or limitations that are relevant in number and magnitude (Hughes & Grawiog, 1973, p. 134). Also of vast importance in creating a valuable linear programming model are the data requirements. If the model is to supply meaningful and useful information for decision making, it is the effort put into data collection that determines whether or not sensible results will be obtained (Hughes & Grawiog, 1973, p. 141 ). Linear programming models are usually applied to complex decision problems, which involve many interacting variables that contribute to the objec- tive criterion function. Many management problems fall into this category, such as the media selection problem. Media selection applications of linear program- , ming are aimed at helping marketers allocate a fixed advertising budget across various media variables (Anderson et al., 1974, p. 229; Lee, 1976, p. 19; Wu & Choppins, 1981 , p. 164). Linear programming is a very effective tool forthis type 10 of resource allocation problem (Lee, 1976, p. 19). In most of these applications, the objective function is taken to be the maximization of audience exposure, or impressions (Anderson et al., 1974, p. 229; Loomba, 1976, p. 23). The linear programming model will aid management in the decision to reach optimal impression levels using selected media variables such as television, radio, newspapers, magazines, direct mailings, and others (Anderson et al., 1974, p. 229; Loomba, 1976, p. 23). Restrictions on the allowable allocation of the advertising budget across the media variables are made on such considerations as company policy, contract requirements, availability of media, and the cost of advertisements (Anderson et al., 1974, p.229; Lee, 1976, p. 20). Management may also have certain preferences regarding each of the media variables. The linear programming problem is to determine the advertising dollars to be allocated to each variable in order to obtain total effective exposure of the organization and its services (Lee, 1976, p. 20). A linear programming model for media selection problems can often be used to arrive at an approximation of the best decision, depending on howthe model is constructed (Anderson et al., 1974, p.233) Media Selection Problems To construct a useful and successful linear programming model for media selection problems, background information on advertising is necessary. Adver- tising is a component of the promotional mix, along with personal selling, publicity, and sales promotion. The promotional mix is part of the marketing mix which forms the overall marketing plan (Kaufmann, 1980, p. 48; Schewe & Smith, 11 1983, pp. 52-53). Advertising’s goals often become goals of marketing. Adver- tising has a continuing responsibility to help management reach the higher-order goals of the company (Kaufmann, 1980, p. 49). Advertising is nonpersonal communication (i.e., the marketer does not personally interact with buyers) that is purchased by the marketerto promote a product or service through the mass media (Kaufmann, 1980, p. 49; Shewe & Smith, 1983, p. 53). The advertising plan is broken down into objectives, budget, media, and copy components (Aaker & Myers, 1987, p. 30). The media plan sets out the details of the media schedule, which may include the specification of up to four types of media factors: (a) media class or the type of medium (9.9., television, radio, direct mail) to be used; (b) media vehicles, which provide the immediate environment forthe advertisement, such as ABC News, Don Cherry Radio Show (a Canadian hockey analyst), or Time magazine; (c) media options, a description of the advertisement’s characteristics, excluding copy and artwork, but including characteristics of size (9.9., full page), length (30 seconds), color (black and white), and location (inside front cover); and (d) media timing, how the media options are scheduled over a time period (Aaker & Myers, 1987, pp. 439-440). Each major media class has characteristics that make it valuable to the media plan. Television can provide an active demonstration of the product or service. More than 94% of all homes in the United States have at least one television set (Kaufmann, 1987, p. 151). Television, then, is often the most efficient way to reach a large, national audience. Local or regional coverage can be gained by buying television time on a station-by-station basis (Dean, 1980, pp. 111-112; Kaufmann, 1987, p. 151 ). Newspapers can deliver advertisements to a target market on a daily basis. Newspapers offer highly efficient geographic 12 selectivity but are not very selective as to a particulartarget audience (Kaufmann, 1987, p. 151). Also, as television news coverage has become more complete, newspaper readership has begun to decline (Dean, 1980, p. 21). Magazines can offer audience selectivity, durability. editorial climate, high-quality color repro- duction, and opportunities for regional coverage. Many magazines will have special sections or features closely connected in some way to a specific product or service. These features can be invaluable to a marketer (Dean, 1980, p. 22; Kaufmann, 1987, p. 151). Radio can go a long way toward establishing product identity in the minds of its audience. Radio can deliver an advertising message at a low cost per thousand, but it lacks a visual presentation, making it a supplementary medium (Dean, 1980, p. 21; Kaufmann, 1987, p. 151). Other media classes that are important to consider included outdoor, transit, direct mail and specialty media advertising. Outdoor advertising involves the use of billboards and other signs. Outdoor advertising can be used on a national or local basis and offers flexibility and intensive market coverage. Outdoor advertising is mainly used in establishing an image or as a directional aid. However, outdoor advertising is mostly nonselective, with a lot of waste circulation (Dean, 1980, p. 22; Kaufmann, 1987, p. 151; Shewe & Smith, 1983, p. 520). Transit advertising can expose the advertising message to a captive audience and can be targeted to specific markets (e.g., commuters). Transit advertising uses signs inside and outside buses, taxis, streetcars, and commuter trains. However, the advertising message is delivered only to those within the vicinity of the ad (Rielly, 1980, pp. 153-154; Schewe & Smith, 1983, p. 520). Direct mail can offerthe most personal and individualized advertising. It is highly selective, has minimal waste circulation, and the copy can be very flexible, as 13 each letter can appear personalized. However, mailing is becoming increasingly more expensive and many consumers consider direct mail advertising to be “junk mail” and never receive the intended advertising message (Dean, 1980, p. 22; Rielly, 1980, p. 155; Schewe & Smith, 1983, pp. 519-520). Specialty advertising can offerawide range of opportunities to the marketer. Local directories, guides, and programs can be targeted to specific markets (e.g., new citizens, with a welcome guide), and an imprinted gift can be especially useful for thanking customers and establishing an image (Dean, 1980, pp. 22-23; Rielly, 1980, pp. 154-155). The characteristics of each ofthe media classes discussed previously are summarized in Table 1. Table 1. — Comparison of media characteristics. Characteristic E: § .05) _ '66 g. ‘9 0' it! o 8 2 S! > m -- 'o 13 % E 8 8 s 2 § I— Z 5 II 0 '5 m Audience size + + + + + — .. Selectivity + + + + — + 4- Exposure time - + + - + - + Quality of reproduction + — + - _ + _ Complexity potential — + + — - + _ Wasted circulation + + + + — - — Flexibility in placing an ad — + - + — + + Availability of the medium — + + + - + + Prestige + — + _ _ _ _ Cost + — + — _ _ _ Key: (+) = Relatively High H = Relatively Low Source: Adapted from CD. Schewe and RM. Smith, Ming: W, 2nd ed. (New York: McGraw-Hill, 1983), p. 522. 14 The medium chosen must be able to convey the advertising message intended. Some marketers use the cost-per-thousand (CPM) figure as a guide to selecting media classes that will convey the advertising message to the intended market. The cost-per-thousand concept relates the size of medium's audience to the price of the medium. Illustration 1 indicates how cost-per- thousand is computed for an advertisement (Davis, 1985, p. 551 ). Illustration 1 - Computing cost-per-thousand (C.P.M.). Price of medium to advertiser Cost per thousand = Delivered audience (thousands) When computing the cost-per-thousand, both the numerator and the denominator in the cost-per-thousand equation may be difficult to determine. The price of a medium is not always well known. Print media (magazines and newspapers) have well-established rates. Radio and television have highly variable program costs. But, the major difficulties are encountered in establishing the size of the delivered audience. The term “reach” is commonly used to indicate how many potential buyers are reached by a specific advertising medium. Knowing the medium’s cost and reach, a relevant cost-per-thousand figure can be obtained (Davis, 1985, p. 552; Kaufmann, 1987, p. 151). Reach figures measure the total number of potential buyers who will see, read, or hear an advertising message, given a particular media plan. The term “frequency” is used to measure the number of times a person, household, or family is exposed to an advertising message during a given time period (Davis, 1985, pp. 552-553; Kaufmann, 1987, p. 152). 15 The term “impressions” refers to the total number of exposures to an advertising message a person, household, or family may experience. An impression is a result of reach multiplied by frequency (Kaufmann, 1987, p. 153). The data to link sales to advertising impressions by type of media variable used is not readily available, so the quantitative analysis used in this research effort can not go beyond a maximization of impressions. Perhaps these data will be available in the future at which time a more accurate picture of the relationships between sales and advertising impressions may be developed. Media Selection in Tourism and Travel One of the problems a tourism and travel organization has in creating advertising impressions is found in the allocation of its advertising dollars. The problem is: How to get the most effective combination of media variables to produce an effective advertising allocation strategy (Starr, 1984, p. 148). For example, in 1982 the state of Illinois spent approximately $1 million on travel promotion, but by 1988 its spending had grown to approximately $10 million. Thus, it becomes essential that an appropriate media selection strategy be developed to account forthe great volume of money spent on advertising (Ritchie & Goeldner, 1987, p. 481 , and, W, 1988, p. 43). How managers allocate advertising dollars to media variables becomes very important no matter the budget size. Most tourism and travel organizations , allocate budgets of up to 5% of their sales revenue to advertising. However, there is considerable variation. Tour operators and cruise lines may allocate 15% of sales revenue to paid advertising. Meanwhile, minor hotel chains allocate less 16 than 1% of sales revenue to their advertising budget (Schmoll, 1977, p. 72; Wahab, Crampon, & Rothfield, 1976, p. 219). To meet total advertising impression objectives, the advertising campaign must achieve coverage of the target market. Coverage will not only depend on the proportion ofthe marketthat sees the advertisements (reach), but also on the frequency with which the advertisements are seen (Burkhart & Medlik, 1981, p. 209). Therefore, the allocation of the budget across the media variables becomes crucial to the success of the advertising campaign. Intangible products like tourism and travel can seldom be tried out ortested in advance by the consumer. Thus, tourism and travel organizations are very dependent on the presentations and descriptions provided by print and/or audiovisual media variables (Foxall, 1985, pp. 176-177). Media Selection by Linear Programming The mass media selection process is facilitated by the availability of quantitative data on audience profiles, demographics, circulation, reach, pen- etration, and cost-per-thousand measures. Computer-based media selection models are capable of producing media plans tailored to specific requirements. Models reduce the marketers media data requirements to mathematical terms in the form of a series of equations. Computational techniques are used to carry out the matching of requirements with alternatives (Schmoll, 1977, p. 122). The computer model is designed to process data on the media variable alternatives and to select those alternatives which best meet the requirements set by the marketer. Because the computer has great mathematical capacity and because it treats data consistently and accurately, the solution proposed may be better 17 than one derived through less formalized procedures (Nylen, 1975, p. 268). Media selection models can be classified in terms of the computational method on which the model is based. The three basic approaches identified by Kotler, in Nylen (1975), are; mathematical programming, stepwise analysis, and simu- lation. One of the earlier linear programming models, a mathematical program- ming approach, was Batten, Barton, Durstine, and Osborn advertising agency’s 1963 BBDO model (Engel, Warshaw, & Kinnear, 1983, p. 301 ; Nylen, 1975, pp. 268—269). This BBDO model's success created considerable interest in linear programming. It was followed by Young and Rubicam’s HIGH ASSAY MEDIA MODEL. These models were developed for large advertising agencies, mostly for in-house use. Some of the models to follow, like MISER, MEDIAC, and ADMOD, have built on the strengths of these first models while overcoming some of their limitations (Aaker & Myers, 1987, pp. 450-459; Engel, Warshaw and Kinnear, 1983, p. 301 ; Nylen, 1975, pp. 268-271). Other examples of models or the beginnings of model formulations for media selection problems can be found in Anderson, Sweeney and Williams (1974); Lee (1976); Loomba(1976) and Wu and Coppins (1981). There are a number of advantages and disadvantages to using linear programming models to solve media selection problems. The linear program- ming model is valuable because it: 1. Forces the marketer to define in specific terms the definitions of markets to be reached. Instead of guesses or hunches, specific data must be developed about the markets. 2. Requires quantification of qualitative factors. For example, editorial climate and related considerations are qualitative or subjective fac- 18 tors. The linear programming model requires management to quantify them in specific mathematical terms. 3. Can be applied to problems involving a variety of media. Assuming availability of data, the linear programming model can be applied to all media types. 4. Allows the blending of many factors. There is an opportunity to change relationships, and work with them while keeping them all at the forefront of the operation. This creates an effort that ends up examining the whole set of factors as they interact, not individual media elements operating in isolation of others (Engel and Kinnear, 1983, pp. 302-303). The limitations of the linear programming model are: 1. The assumption of equal effects for repeat exposures. However, it is commonly believed that the response by the prospect will diminish after many exposures. This introduces nonlinearity into the response function (ie the second exposure has less impact than the first and impact declines further with each subsequent exposure). However, their impacts can be reduced by disaggregating the relevant variable in formulating the linear programming model. 2. The assumption of media costs. Linear programming models assume media costs are constant, not taking into account discounts for multiple time and space purchases. Introduction of discounts would make the cost function nonlinear. Again, disaggregation can reduce but not eliminate this limitation in the linear programming model. 3. Solutions determined without consideration of audience duplication. 19 Audience overlap cannot be handled with present computer algo- rithms. 4. The illusion of definiteness. The solution is only as good as the data and the assumptions on which it is built (Engel and Kinnear, 1983, pp. 303-304). As noted, it is often possible to reduce the impact of these limitations by building more complex models. In othercases, it is often possible to evaluate the impacts of such limitations through sensitivity analyses which help the user to formulate a most relevant single model or a set of models which yield a range of outcomes within which a best solution is likely to be found. Linear programming has become more readily available for practical use because, of continuing developments in computer technology (both in terms of hardware and software). Most of the major computer manufacturers have developed their own linear programming packages for various clients. A large number of computer service firms provide packages on a time-sharing basis. However, it is usually only the large firms that can take advantage of these linear programming packages (Lee, 1976, p. 19). When readily accessible, application of linear programming models can be cost effective in allocation problems involving just a few thousand dollars (Engel & Warshaw, 1964, p. 47). The building of linear programming models can be a very involved process, with the model builder needing extensive knowledge of linear pro- gram ming practices. The cost of sophisticated hardware and software can also be prohibitive for the smaller tourism and travel organization. However, with the ‘ development of the linear programming package WHAT’S BEST! by General Optimization, Inc. in 1985, some of these problems are overcome. WHAT‘S 20 BEST! is based on a well-known LINDO optimization software developed by Professor Linus Scharge (Savage, 1985, cover). WHAT‘S BEST! allows forthe creation of linear programming models on spreadsheet software using linear formulas. WHAT’S BEST! invokes the full power of linear programming proce- dures to find the mathematically optimal solution, which is typically unattainable in any other way (Savage, 1985, p. i). This puts the power of linear programming at the disposal of tourism and travel organizations without the associated cost and knowledge that may have deterred them in the past. This review of the literature leads to the conclusion that linear program- ming can be used to assist in making media selection decisions. However, this tool was, until recently, not generally accessible because of limited access to computer systems needed to solve linear programming models and lack of knowledge required to effectively use it even if required hardware and software were accessible. The recent development ofthe WHAT’S BEST! linear program- ming software for solving linear programming models on most personal comput- ers (PC's) and its modest cost and “user friendly” nature would appear to have removed the major obstacles to wider use of linear programming. Thus, this thesis evaluates the WHAT’S BEST! software specifically focusing on its appro- priateness and ease of application to a realistic media allocation problem involving travel and tourism. If successful, this test should serve as a demonstra- tion to stimulate use of WHAT’S BEST! as a tool to use in travel and tourism organizations. CHAPTER III METHODS In this chapter, the methodology used in preparing this research effort will be discussed. This process begins with a brief overview of the computer system and software requirements necessary to run WHAT'S BESTl. A brief introduc- tion to the sample files follows, that assists the user in learning and understand- ing WHAT'S BESTl. The remainder of the chapter discusses in detail the process used to arrive at the finished spreadsheet master file. These discus- sions are broken down into ten steps. They begin with the selection ofthe media variables to be used in the master spreadsheet file and finish with a discussion of how to execute the completed master spreadsheet file by recalculating the spreadsheet using macros. To better illustrate how each of these ten steps fit together, a diagram is provided in Illustration 2, on the following page. To execute WHAT’S BEST! Release 1.2 program a minimum system requirement of Lotus 123 Release 1A or 2.0, or Symphony Release 1.1, and . PS-DOS/MS-DOS 2.0 or higheris needed. An IBM PC or 1 00% IBM Compatible with 256K RAM minimum memory for the Personal Version of WHAT‘S BEST! is necessary. Also, dual, double-sided, double-density floppy disk drives or one floppy and one hard disk drive are required. The program needs about 21 1 K in 21 22 Illustration 2. -- Ten steps for development of the master spreadsheet 1. Select media variables 99. Newspaper, Full Page Ad 6 2. Obtain basic Reach data {I 3. Obtain basic Frequency data {I 4. Obtain basic Unit Cost data {Iv 5. Set format of master spreadsheet file eg. Label and position columns and rows {Ir 6. Determine spreadsheet cells to be calculated by WHAT‘S BEST! 99. Objective cell - Total Impressions 7. Select budget levels for spreadsheet files a 8. Select budget allocation strategies (3) for the spreadsheet files {I 9. Develop macros (3) for use in the master spreadsheet file A) Instructional table 8) Input menu C) Output menu a. 10. Determine process for calculation and recalculation of the master Spreadsheet file 23 free memory to run the 256K Personal Version (Savage, 1985, pp. A-42, A-53). In this effort, Lotus 1-2-3 Release 2.2, MS-DOS 3.0, and a Zenith 100% IBM compatible computer with 512K RAM memory with one floppy and one hard disk drive were used. The WHAT’S BEST! software program provides seven sample or demon- stration files on disc that are described step-by-step in the program manual. For familiarization purposes, two files (PRODMIX and HOGFEED, for more detail refer to the WHAT'S BEST! manual) were selected from the section on Basic Modeling Concepts in the WHATS BEST! manual. Anothertwofiles (PRODMIX1 and TRUCK) were selected from the section on Advanced Modeling Concepts. These sample files can be followed in the text and on the computer screen as the demos are run. Approximately 30 to 45 minutes were spent on each sample file, gaining familiarization with the basic procedures of WHAT’S BEST! These sample files were referred to during the building of the master spreadsheet file to check formats and procedures. In this respect, the sample files are very important and serve a dual role, one of learning and guidance for the user. After seeking basic knowledge of WHAT'S BESTl's requirements and procedures, the first step is to create afile in Lotus 1-2-3that would allow WHAT’S BEST! to invoke its linear programming procedures. Then obtain specific data to fill the Lotus 1-2-3 file. Fifty-nine media variables were selected from a compilation of seventy-eight media variables listed in Graham’s (1969mm W. Media variables are those elements which make up the advertising strategy, such as a full page newspaper advertisement, a one page color magazine advertisement or a fifteen second cable television advertise- ment. The fifty-nine selections were made on the sole criterion of perceived relevance to advertising plans for tourism and travel organizations. Media 24 variables not selected included games, parade floats, yearbooks, and so on, as their advertising value did not appear consistent with the goals of an advertising plan for a typical tourism and travel organization. Next, the process of collecting data forthe master spreadsheet file begins. In the first three steps information on reach, frequency and unit cost data must be collected. It is important to note that obtaining these figures is made difficult by a secrecy that prevails throughout the advertising industry. Published figures are often acknowledged to be only estimates or approximations. This research effort used both figures in print publications and those obtained from discussions with account executives in advertising firms or in businesses who sell advertising time and space. For reasons of competitive secrecy, some of the sources of information asked not to be identified specifically. This term was agreed to when the data were obtained out of necessity and because citing all specific sources was deemed to be of minimal importance overall. It was the intention of this research effort to develop only reasonable estimates so that an evaluation of WHAT'S BEST! could be accomplished. The marketing or advertising manager for a tourism and travel organization should find the task of developing estimates for individual media variables to be less of a problem. A media sales represen- tative should be more willing to provide media variable data as the assistance they provide may produce media sales. The manager may also have some ofthe media variable data available from past media purchases. This may provide some of the most valuable data for the manager. As stated previously, the objective of the research effort is to demonstrate the use of WHAT'S BEST! and evaluate it, and not to solve a specific media selection problem. The second step is to develop reach data for the individual media variables. To begin with, values fortelevision network media variables (15- and 25 30-second spots) were obtained from the Datafiles of We, Decem- ber 22, 1986. The reach for cable television (15- and 30-second spots) were obtained from a discussion with an account executive at a Michigan cable advertising distributor. The reach for local television (15- and 30-second spots) was obtained from a similar discussion with a local Michigan television station. The reach figures for the six newspaper media variables were obtained from discussions with an account executive at a Southern Ontario newspaper publish- ing company. These figures were said to match the circulation figures of a mid- size us. daily newspaper like the AW Gazette, with its circulation of 221,594 (“Last Minute News,” 1990c, pp. 5-14). The reach figures for aweekly magazine, a monthly magazine, a business magazine, and a consumer-interest magazine were obtained from talking to two account executives at a local Lansing advertising agency. The weekly magazine selected was the April 24, 1990, issue of W, the monthly magazine was Playboy (December 1989), the business magazine was Echune (October 16, 1989), and the May 1 990 issue of Wforthe consumer magazine. The reach data are admittedly best estimates by one of the account executives questioned in this matter. The reach figures were double-checked against circulation figures to ensure that the reach quote did not surpass the total paid circulation of the magazines. In only one case did this happen. The monthly magazine attributes much of its circulation to newsstand sales and not paid subscription figures. The magazines selected for use were chosen solely on the basis of availability of their reach and cost figures. For radio, the reach figures were obtained from the rate card of a mid-Michigan FM radio station and discussions with an account executive of that station. 26 The outdoor advertising reach figures were obtained from a discussion with an account executive at Adams Outdoor, Inc., a local Lansing firm. The transit reach figures were gleaned from a discussion with a knowledgeable employee of a Michigan transit company. These figures were admittedly rough estimations. The figures for the reach of direct-mail advertising came from a discussion with an employee of a Philadelphia, Pennsylvania, advertising firm. These figures for reach are based on the total distribution of all direct-mail advertisements produced at a given cost. The reach information for specialty media variables came from catalogues of merchandise provided by Sales Guide, Inc., of Wisconsin; NEBS (New England Business Service, Inc.) of Massachu- setts; the Lesco Corporation of Michigan; and Artistic Greetings, Inc., of New York. The third step is to develop frequency levels for each of the media variables used in the master spreadsheet file. The television media variables were all set at a frequency of 1.0, as reach and cost figures are for a single television advertisements. The frequency levels for the newspaper media variables was set at 2.0, as discussions with an account executive revealed that most newspapers bought are perused once and then read through more thoroughly, giving the advertisement a frequency of being noticed by the reader of 2.0. In other instances the newspaper is passed to another within a potential purchasing unit (eg. a couple, family, business associates, etc). Thus the frequency of an advertisement may increase. The various frequency numbers for the four magazines were determined in discussions with account executives of two Lansing area advertising firms. The frequency numbers forthe radio media variables were arrived at after discussions with a radio station account executive. The frequency of 4.0 for 15- 27 and 30-Second spots is based on a prime time (Monday-Friday 5:30 am. to 8:00 pm, Saturday 10:00 am. to 3:00 pm.) purchase of Spots to run in an advertising campaign over a three-month period. The sponsorship of news and weather reports has a frequency rating of 6.0 as the bulk purchase of each would be greater and would run in non-prime-time hours as well as prime time. The frequency numbers of the outdoor advertising media variables are based on estimates arrived at by the account executive at Adams Outdoor, Inc. These estimates are based on two factors: expected traffic patterns and billboard size. A frequency estimate of 5.0 for a 14 x 48 billboard as compared to 7.0 for a 12 x 25 billboard. With the “Rotary Plan,” an advertiser may take a 4- to 12- month lease of the billboard space, with the option to repaint every 60 days. For this research effort, a six-month lease was selected and the numbers adjusted accordingly. The rotary plan was developed this way so as to approximate the effect of a six month lease with no repainting procedures. The frequency numbers for transit advertising are the result of a discus- sion with a transit company employee. The frequency for the bus cards (i.e. advertising placards placed above passenger seats) is 4.0, higher than the 2.0 for station and bus-stop posters, as the transit user is more likely to be on the bus longer than at the bus stop or station, therefore greater opportunity for observa- tion of the bus card. For the direct-mail frequency, discussions indicated frequency estimates of 1.0 for leaflets, 3.0 for brochures, and 2.0 for newsletters. These numbers . derived from the quality and the attractiveness of direct-mail options. A leaflet (1.0) is usually read quickly and does not hold the attention of the reader that a newsletter or brochure might. A newsletter (2.0) is approached much the same as a newspaper, often perused and then read through again for content. The 28 brochure (3.0) is quite often used over and over, as it may be read for its own merit, compared to others, and even used in making travel arrangements. Frequency estimates for specialty media variables used in this study also vary widely across speciality media types. Directories (e.g., state park camping directory) were treated as an item that is first perused and then read for content, like newspapers, thus a frequency of 2.0. Catalogues (eg. state guide to special events) were given a frequency of 1.0. This is based on discussions that reveal that unless the catalogue is of specific interest or value to the reader, it is often not reused. Note that these media variables were not included in the direct-mail category because fifteen percent to forty percent, an estimate by an advertising firm employee, are distributed at travel bureaus and travel information centers. Banners have a frequency rating of 1.0, as they are usually observed just once. The location of banners (i.e., at park or event entrances/exits) has a major effect on their frequency rating. Thus, it is difficult to evaluate exact frequency figures. The media variable in maps were assigned a frequency rating of 2.0, as is consistent with their use. T-shirts were assigned a frequency rating of 4.0, also based on their use, determined by considering the number of times a person would wear the T-shirt over a six-month period and having others view the advertisement on the T-shirt. The frequency for bumper stickers is 2.0 and is related to observation of the sticker and use of automobiles. This number was admittedly a rough estimate. The poster type selected in the specialty media category was one employed in giveaway promotions. It has a frequency value of 2.0. The frequency number is based on expected usage patterns. Note that a poster used (i.e., put up in'home/office) may have a higherfrequency rating, but accurate figures on this type of use were not readily available. Menus was assigned a frequency rating of 1.0, based on a single tourist’s observation of the 29 menu. It would be higher ifthe regular patrons at a restaurant or diner has been selected as the target audience. The last specialty media variable selected forthe model was key ring tags. This represents an almost unlimited host of imprinted products which could be used in giveaways, or other promotional stunts for potential tourists and visitors. Key tags were selected from among such products as buttons, highlighters, lapel pins, luggage tags, golf tees, pens and pencils, shopping bags, signs, and yo- yo’s. The key ring tags were assigned a frequency rating of 1.0 as their actual use patterns, like promotional posters, and their ability to make a lasting impression are not accurately noted by the companies that produce them, and less seems to be known by the people who use them (Dean, 1980, p. 23). The unit cost estimates for each of the media variables were developed from a vast number of sources. The costs for 15- and 30-second network television commercials were obtained from the Datafiles of the December 22, 1986, issue of W. The costs for the cable 15- and 30-second spots were obtained from a discussion with an account executive for a Michigan cable distributor. The costs are estimates fortop-rate cable television shows on a major cable network like ESPN or Fox. The costs forthe local 15- and 30-seconds spots were derived from a discussion with a local Michigan television station. The cost figures for the newspaper media variables came from discussions with an account executive at a Southern Ontario newspaper publishing company. These numbers were converted to U. S. dollar funds based on an exchange rate of thirteen percent. In terms ofthe cost figures forthe four magazines selected, initial numbers for one-page ads were determined from the “Media Works” section of mum 30 Age. The figures for the weekly magazine were taken from the March 6, 1989 issue of meme. The monthly magazines cost figures came from the August 6, 1990 issue. The business magazine cost figures came from the August 7, 1989 issue, and the July 30, 1990 issue of AeyenjeineAge was the source used forthe consumer magazine cost figures. To obtain cost figures for the media variable other than one-page ads, discussions were held with to account executives from a local Lansing advertising agency. The costs for radio advertising came from a Mid-Michigan FM station’s rate card and discussions with an account executive at a Mid-Michigan FM radio station. The figures for the radio news and weather reports are best estimates because sponsorships are arranged on a special-request basis. Since actual costs could not be determined withoutdetailed negotiations,only approximations could be developed from the discussions with the account executive. The costs forthe outdoor advertising media variables were obtained from a telephone interview with an account executive at Adams Outdoor, lnc., Lansing, Michigan. Figures quoted include an estimate of set-up and production costs. The issue of volume discounts was discussed, but due to linearity requirements of the model, discounts were not included. The transit variable costs were from a telephone interview with a knowl- edgeable Michigan transit company employee. The figures are based on her best estimates and, again, do not include discounts based on volume and length of campaign. The cost figures forthe direct-mail media variables camefrom discussions with an employee of a Philadelphia advertising firm and an employee involved in brochure production for a small Ontario firm. All Canadian dollar figures quoted were converted to US. dollar amounts. 31 The cost data for specialty media variables came from catalogues and quotes by sales representatives of Sales Guide, Inc., of Wisconsin; Lesco Corporation of Michigan; and Artistic Greetings, Inc., of New York. A catalogue from NEBS of Massachusetts was used, but a sales representative was not contacted. No effort was made to find a best product or lowest price among the companies. Products were chosen based only on ready availability of cost data. To develop the model, reach, frequency, and cost data needed to be collected from outside sources, the other components of the model are estab- lished by the user to reflect internal conditions (eg. the budget available). The software package converts inputs into the required format for linear programming (LP) analysis. With these data collected, the fifth step in developing the model is to place them into a format which can be accessed and manipulated by both software packages used, (i.e. WHAT‘S BEST! and Lotus 1-2-3). The model was based on the row/column format familiarto Lotus 1-2-3 and other spreadsheets where data is entered into specific cell locations in the Lotus 1-2-3 spreadsheet. The data discussed above, and other components established for the model (to be discussed later), were placed in 18 majorcolumns. The first column forthe model contains the media variables (eg. Radio and Direct Mail). The second column contains the Reach in 1,000’s figures for each media variable, while the third column holds the Frequency numbers. The fourth column in the model is the Impressions column. It is created with a formula multiplying the Reach column . spreadsheet cells, by those in the Frequency column. After this column, the unit cost data collected are put into a fifth column headed Unit Cost in $1 ,000’s. While the sixth column called CPM or cost-per- 32 thousand column was created with the formula Reach divided by Unit Cost. This column is valuable as a check to see that reach and cost figures are in line with industry Standards. The following column, Number of Units Selected, has all the numbers in the column set to zero. The WHAT’S BEST! program wasthen used to make this range an adjustable-cell range. By invoking the optimization command of WHAT‘S BEST!, the value of these cells will be adjusted according to the optimal solution selected by WHAT'S BEST! The adjustable cell is also referred to as the decision variable (Daellenbauch & Bell, 1970, pp. 3-4; Savage, 1985, p. A-56). The eighth column refers to the Total Cost of Units Selected (in $1 .0005). This column of cells uses the formula Number of Units (media variables) Selected multiplied by Unit Cost in $1,000. The next three columns (nine, ten and eleven) are necessary to provide the constraints for the spreadsheet model. The first of these (the ninth overall column) contains the less than sign (<). This Sign represents the mathematical term “less than or equal to” in the mathematical form ofthe LP model formulation. In Lotus 1-2-3, the (<) sign serves no computational function and is just a visual reminderin this spreadsheet model (Savage, 1985, p. A-34). The role ofthis (<) sign is that, when used with the command sequence [Prt Sc] [<] in the WHAT’S BEST! mode, it automates the creation of constraints and associated slack cells (Savage, 1985, p. A-34). These constraints fill the tenth column of the spread- sheet model underthe title $ Limit. The eleventh column contains the associated slack cells which indicate the remainder of the allotted constraint not spent. A slack cell is a spreadsheet cell created to enforce a given constraint. It contains the formula for the remaining amount of a limited resource (such as money). 33 WHAT'S BEST! forces all slack cells to be non-negative during the optimization procedure in keeping with the linearity requirements of the linear programming model. This eleventh column is titled Non-Negative to represent the resource limitations set out in the spreadsheet model. A constraint requires WHAT'S BEST! to choose the best solution from those alternatives that are within the resource limitations (Savage, 1985, p. A-56). The constraints were developed through a subjective decision making process that looked at what would likely be appropriate levels for the number of media variables selected. To ensure that each category of media variables (i.e., television, radio, and so on) did not take complete control of all budget funds, each category of media variables was given an overall constraint. For example, television media variables generate very high numbers of impressions so overall constraints are needed for each media variable category so that all the budget won’t be allocated to a media category like television with its high impression output, thus defeating a specific media allocation strategy. This ensures that a limited resource will not be used beyond its capacities during the optimization procedure (eg. more money will not be spent than is allowedinthe budget). The role ofthese constraints are illustratedin Table 2 on the following page. The twelfth column represents the total impressions generated for each media variable unit selected during optimization (e.g., six cable television 30- second spots). The formula for the column is impressions multiplied by the number of media units selected. The number of media units selected are generated by the WHAT'S BEST! model as it solvesthe linear programming (LP) matrix. 34 Table 2. - Location of individual cells in forming the linear programming spreadsheet constraints. if of Units Total Cost of Constraints Selected Units Selected $1 .0003 5 Limits Non-Negative 1 .00 1 00.00 <200 1 00 0.00 0.00 <200 200 2.00 100.00 <150 50 3.00 150.00 c <150 0 a 350.00 d 500 150 b 87.50 a) The total cost of the units selected in the media category. b) The average cost of the units in the media category. c) The individual media variable constraint (eg. Transit Poster). d) The media catergory constraint (eg. Transit Advertising). The following column again contains the Number (if) of Units Selected; however, this time the numbers in this column will appear as integers. The column will be representative of the Number (ft) of Units Selected at an Integer Value, and will be titled (ft) of Units at (@) Integer Value. WHAT’S BEST!, in its optimization process, allows fractional values (real numbers) to be inserted into adjustable cells. However, fractional values are not truly representative of what can realistically be expected of media variables. This means that one can not realistically expect to select 5.32 Cable, 15-Second spots for advertising on television, or that 13.83 billboards can be purchased for outdoor advertising. Thus the number for units selected of each media variable must be computed as 35 integer values. To combatthis problem, WHAT’S BEST! does have a 0,1 Integer value option. However, the number of units to be selected can only be zero, or one, and release 1.2 only offers 42 ofthese Integer Variables. Since this process does not provide a workable solution to the problem of fractional values in this case, an alternate solution had to be developed. The creators of WHAT’S BEST! suggest the next most viable solution to produce workable results is that fractional values may be converted to integer values by rounding off answers returned by WHAT'S BEST! to the closest integers which do not violate any constraints (Savage, 1985, p. "-5). This suggestion is followed for column thirteen, to provide outputs that are more realisticfor use in an advertising media solution problem. Thus, the Number (if) of Units at (@) Integer Value column contains a formula that is taken directly from the Number (if) of Units Selected column, using the cell values generated during the WHAT’S BEST! optimization process. The values in the cells are rounded off to the nearest integer. The fourteenth column represents the total cost at integer value after recalculation (in $1 ,000’s). This column's cell values are derived from a recalculation using the integer values for the media units selected from column thirteen and the Unit Cost in $1 ,000’s data from column five. The column is titled Total Recalculated Cost of Integer Units Selected in $1 .0005 and is abbreviated in the spreadsheet as Total @ Recalc. Int. Unit Cost. The formula entered in the spreadsheet would read; If the “unit cost” multiplied by the rounded “number of units selected” is less than the “constraint limit” and the “number of units selected” is not zero, orthe “unit cost” multiplied by the rounded “number of units selected” subtracted by -0.25 and is less than the “constraint limit” and is not zero, enterthe value as the unit cost multiplied by the integer value ofthe number 37 The eighteenth and final column contains the total impressions generated for each media variable afterthe recalculation process. It uses integer values for the number of media units selected from column thirteen in its formula to find the total number of impressions after recalculation. In the sixth step, the cell ranges are selected forthe WHATS BEST! linear programming process. The first to be developed is the cell to be maximized. Maximize is a command that tells the linear programming code within WHAT’S BEST! to maximize the value of the formula in the cell. This cell then becomes the objective cell. During optimization, the linear programming code finds the combination of values of the adjustable cells (# Media Units to be Selected) that maximize the objective cell (Total Impressions) (Savage, 1985, p. A-58). The optimal solution is found when a combination of values of the adjustable cells that maximizes the objective cell is found, as compared to all other feasible alterna- tives (Savage, 1985, p. A-56). The value of the objective cell that is determined by the optimal solution is now referred to as the best or optimal value. The objective cell as indicated will be the sum of all impressions calculated in the twelfth column. The column is titled Total Impressions, and reading down this column one would calculate the total of all impressions. Thus, the objective cell will appear at the bottom of this column as a grand total of all impressions generated by the WHAT’S BEST! optimization process. However, before optimization may begin WHAT’S BEST! needs a dollar amount or budget to allocate to the various media variables so that selection may begin. A convenient and practical place to enter the budget is under the Constraint Limit column (tenth column). Thus, the total budget amount will act as a constraint for the upper limit which WHAT'S BEST! may allocate. It is crucial 38 to have an upper limit so that the adjustable cells can not be increased infinitely during the maximization process (Savage, 1985, p. A-60). In making this upper limit constraintthere is the need for a slack cell. This slack cell forthe total budget constraint appears underthe other slack cells in column eleven (Non-Negative). By adding a formula in the spreadsheet to sum the figures of column eight, the Total Cost of Units Selected in $1 ,000's, a figure can be arrived at to indicate how much of the total budget was spent. For convenience and practicality a total budget spent cell is put at the end of column eight and under that cell in the spreadsheet appears that net budget cell. The resultant figures from these two cells may be useful in evaluating the effectiveness ofthe spreadsheet developed. These special spreadsheet cells are also put in place undertheir respec- tive columns in the recalculation range (columnsthirteen to eighteen). With these final constraints in place, along with the cell to maximize, it is necessary to select a budget in step seven. There were three budget levels selected forthis research effort. The three levels were chosen to display the varying outcomes of the objective function at different levels of budget allocation. The first budget level selected was that of $12 million. This budget would be comparable with the Illinois Office of Tourism’s budget, which was $10 million in 1988 (51600231 W, 1988, p. 43). The $12 million budget scenario would also approximate the advertising allocation plan of the states of California, Florida, and Alaska, which spend even a larger amount on the promotion of tourism and travel than does Illinois, (Horton, 1985, p. 20; Meyers, 1985, p. 85). This scenario might also fit private tourism and travel organizations with very large advertising budgets, such as Embassy Suites Hotels. $10 million in 1990; Holiday Inns Hotels, $9 million in 1989; Choice Hotels International, $12 million 39 in 1990; and Pan American Airlines, $12.6 million in 1989 (Endicott, 1990, p. 45; “For the Record,” 1990a, p. 33; Meyers, 1990, p. 16). The scenario could also apply to the national tourism office of a country like the Bahamas Ministry of Tourism, which will spend $13 million in fiscal 1991 to advertise in the United States and Canada (“Last Minute News,” 19905, p. a). The second budget level selected was $5 million. This budget would be similar to that of cruise lines like the Royal Caribbean Cruise Line ($6.6 million in 1989) and Carnival Cruise Lines ($6 million in 1989) ( Endicott, 1990, p. 45). A mid-size state like North Carolina, Tennessee, or Wisconsin spends close to $5 million on tourism and travel promotion (Meyers, 1985, p. 85; W W, 1988, p. 1492). Also fitting this scenario is a foreign country’s national tourism office, such as Bermuda’s, which spent almost $6 million on newspaper, magazine, direct-mail, and spot radio advertisements, among others, for 1988 MW, 1988, p. 951 ). The $5 million budget gives the model an opportunity to produce results for an organization other than those with big eight-figure budgets. Thethird andfinal budget levelselected wasthat of$1 million. This budget would be consistent with that spent on advertising by such tourism and travel organizations as Brevard County Tourist Development in Cape Canaveral, Florida ($1.2 million), the state of Georgia ($1 .1 million), the Phoenix and Valley of the Sun Visitors and Convention Bureau ($800,000), or Copper Mountain Resort in Colorado, a similar $800,000 (Bearden-Mason, 1985, p. 20; Meyers, 1985, p. 85; “Newswatch,” 1990a, p. 12, 1990b, p. 12). Some foreign countries advertising in the United States, like Barbados and Greece, also have advertising budgets in the $1 million range (W. 1988. pp. 40 950, 992). The state of Nebraska would also fit in this scenario, spending a little more that $1 million for fiscal 1988, according to the WEBB—Q! Advertise: (1988). With the budget levels selected and the WHAT'S BEST! optimization process invoked and completed, some questions arise: “What would be the result if the constraints were altered?”, “Could there be a more optimal solution if more money was spent on television?”, or “Can smaller allocations be made to the Special Advertising media variables?” To address these questions, three budget allocation strategies were developed in step eight that could be deployed over each of the varying budget levels. To aid in the development and application of these scenarios, the media variable categories were grouped as follows; television alone, represented by TV; newspaper and radio, represented by News/ Radio; the weekly magazine, the monthly magazine, the business magazine, and the consumer magazine, all formed the group Magazine; outdoor advertis- ing, transit advertising, and direct mail are represented by the group Other; and special media was placed in its own group, Special. With these five groups designated, formulas were created to distribute a set percentage of the total budget over each group. The budget amount allocated to each group would be reflected in the media category constraint as determined by it’s individual formula. See Table 3, on the following page, for an example. The formulas used for each media group would not allow one media category in a media group to take all the money allocated for that group. For instance, the media category Consumer Magazine could not spend all 60 percent of the budget allocated to the media group Magazines. The formula was based on the premise that no one media variable group would spend more than 50 41 Table 3. - A media category constraint as determined by a special media category formula. if of Units Total Cost of Constraints Selected Units Selected Non- $1,000's $ Limits Negative 1.00 100.00 < 200 100 0.00 0.00 < 200 200 2.00 100.00 < 150 50 3.00 150.00 < 150 0 350.00 a 500 1 50 87.50 a) The media catergory constraint determined by a media group formula as applied to each budget scenario. For example, the value 500 can be derived from the formula: + $BUDGET ’ $SPECIAL percent of the budget amount allocated to its media group. This was developed as part of the allocation strategy to ensure that no one media category became too dominate, thus creating as realistic a scenario as possible. As in the example above, consumer magazines would have no more than 30 percent (50 percent of 60 percent) of the total budget to allocate among its media variables. The formulas used for each of the media groups appear in Table 4 on the following page. With the formulas set for each media group, the budget strategies could now be developed. The three budget allocation scenarios are based on three different distribution strategies, centered around the different media groups' potential for impression generation. First, a mixed strategy thatfavors no media group too heavily, but provides reasonable budget allocations to each. This mixed allocation strategy is represented by the letter A. The second scenario 42 Table 4. - Media category formulas Media Category Formula TV + $BUDGET * $TV News/Radio + 0.75 * $BUDGET " $RADIO Magazines + 0.50 + $BUDGET ‘ $MAGAZINE Other media + 0.75 + $BUDGET * $OTHER Special media + $BUDGET * $SPECIAL uses an allocation strategy that favors providing the majority of the budget to the higher impression generating media groups. This high impression generating media strategy is represented by the letter B. The third allocation strategy focuses the bulk of the budget toward the low impression producing media groups (ie. Other and Special media). This low impression generating media Strategy is represented by the letter C. An outline ofthese scenarios is presented in Table 5. Table 5. - Maximum percentage of funds for the total budget available for each budget allocation strategy across the media groups in the master spreadsheet file. Budget Allocation Budget Allocation Budget Allocation Media Group Strategy A Strategy 8 Strategy C TV News/Radio Magazines Other media Special media Total Budget 25.0% 1 2.5% 37.5% 1 2.5% 1 00% 50.0% 20.0% 20.0% 5.0% 1 00% 1 0.0% 1 0.0% 30.0% 25.0% 25.0% 1 00% 43 Each of these three budget allocation strategy scenarios is then run for each of the three budget levels producing nine output spreadsheets. To aid in producing the nine output spreadsheets, each representing a different budget and budget allocation strategy scenario, a master spreadsheet/file was devel- oped in Lotus 1-2-3. It contains all 18 major columns, the objective cell, and other cell groups used for analysis. The raw data is input into the master spreadsheet, along with the basic constraints for each media variable and the corresponding slack cells. Also the preliminary calculations of the number of impressions and the cost-per-thousand are included in the master file. All other cells are set to zero. To aid the deployment of the three different budget levels and three different scenarios for budget allocation, a Lotus 1-2-3 macro was developed for the master spreadsheet file. This instructional macro is intended to provide easier access to the adjustable cells contained within the master spreadsheet file, so it could then be deployed by those other than this author in a relatively straightfontvard and simplified way. This macro with its varying commands are displayed in Table 6 on the following page. With this instructional table macro, the budget may be changed and any other budget allocation strategy by percentages may be entered for use in the master spreadsheet file. Adjustments that are deemed necessary can be completed by using the () macro. This takes the user back to the master Spreadsheet where the constraints appear. Changes in constraints may arise when budget levels are altered substantially, for example the $1 million dollar - constraint limits used in the $1 million and $5 million dollar budgets in the television media category had to be changed to $2.5 million dollar constraint limits to allow allocation of the $12 million dollar budget to be more effectively utilized. 44 Table 6. - Instructional table macro as it appears in the Lotus 1-2-3 master spreadsheet file. Instructions: ( to return to this screen ) 1) Enter total budget to be optimized ($1,000's): 2,500 2) Allocate budget to media groups by percentage. Media Budget Group Allocation % Television = 45.0 News/Radio = 10.0 Magazines = 15.0 Other media = 15.0 Special media = 20.0 To total hit F9 100.0 3) To modify allocations to specific media variables, hit < ALT A >. 4) Invoke WHAT‘S BEST! to optimize. 5) To display results, hit < ALT B >. The figures input into the master file instructional table are generated as follows; the Total Budget to be Optimized in $1 ,000’s uses a Lotus 1-2-3 macro of its own to reproduce the value input into the instructional table in the master file cell that contains the overall total budget constraint that follows at the end of column eight (T otal Cost of Units Selected ($1,000’s), while the percentages allocated to each of the media groups (eg. TV, News/Rad, etc.) have a macro of their own that will reproduce the corresponding allocated budget amount at each of the media categories in the master spreadsheet file (see Table 3). The 45 conversion from a percentage figure to a real dollar amount for each media group is accomplished in the spreadsheet column to the right of where the percentage figures are entered. Here a simple formula is used to convert the percentage value entered to a real dollar amount which is hidden from view on the computer screen. This cleans up the appearance ofthe instructional macro and creates the appearance of a user-friendly macro. Another macro to aid analysis of the master Spreadsheet file was devel- oped that outputs results for fractional and integer values after optimization and recalculation. The two main tables of the macro show the results after optimiza- tion using fractional media units (99. 5.32 cable television 15-second spot advertisements) and the results after optimization and recalculation using integer media units (eg. 5 cable television 15-second spot advertisements). Within each of these tables are two smallertables, one of which contains the input menu , as it was adapted from the instructional menu. It has the total budget and percentage allocations as per the budget allocation strategy scenario currently being employed. For an example see Table 7, on the following page. The second table is deemed an output menu. In contains the budget allocation in $1 .0005 for each of the media groups. Along with the number of impressions (in 1,000's) produced in each media group after the optimization process has been completed. The Cost per Impression is also displayed here for each media group and as an overall total forthe complete budget of $1 2 or $5 or $1 million dollars depending on the budget allocation strategy scenario used. The results in both the small tables ofthe output menu are produced based on optimization results using the fractional values and on recalculation results using the integer values. A sample of a fractional value table and an integer value table appear in Tables 8 and 9 respectively on page 47 of this paper. 46 Table 7. — Input menu for optimization in the master spreadsheet file. Input Menu Total budget ( $1 ,000's ) = 2,500 Media Budget Group Allocation % Television = 45.0 News/Radio = 10.0 Magazines = 15.0 Other media = 15.0 Special media = 20.0 To total hit F9 100.0 In order to present results using integer values, the master spreadsheet had to be divided into two large ranges, one in which WHAT’S BEST! would optimize the objective cell and one range that WHAT’S BEST! would not try to optimize. The one in which it would not try to optimize contains the integer columns (13 through 18). Also placed in this range are the instructional macro and the results macros, which need protection from random adjustment by WHAT’S BEST! during its optimization process. To protect this range, a WBCALC range (as it is known in the WHAT‘S BEST! manual) was developed. This is a functional option provided by WHAT'S BESTl so that all formulas in the WBCALC range are frozen at the pre-optimized values. The WBCALC range is then removed from calculation while WHAT’S BEST! is running (Savage, 1985, p. A-65). However, upon return to the spreadsheet in use, recalculations must be performed to display newly optimized 47 Table 8. - Sample output menu after optimization using fractional media units. Output Menu Net Budget ($1,000's) = 0 Media Budget Group Allocation Impressions Cost/ $1 .0005 1,000's Impression Television 1 ,000 100,500 0.0100 News/Radio 200 1 15,005 0.0575 Magazines 150 160.555 0.1070 Other media 150 501,000 0.3340 Special media 1,000 230,055 0.0230 Totals = 2,500 1,107,115 0.0443 Table 9. - Sample output menu after optimization using integer units. Output Menu Net Budget ($1,000's) = 155 Media Budget Group Allocation impressions Cost/ $1 ,000's 1 ,000's Impression Television 900 99,500 0.01 10 News/Radio 190 100,005 0.0526 Magazines 120 140,525 0.1171 Other media 135 489,898 0.3629 Special media 1,000 230,055 0.0230 Totals = 2,345 1 ,059,983 . 0.0452 48 values (Savage, 1985, p. A-66). To aid the recalculation effort, a simple macro was added to the master spreadsheet that recalculates the WBCALC range and takes the userto the fractional and integer results macrosto display the optimized and recalculated results. This macro is invoked using (B) as indicated in the instructional table macro as displayed in step 5 of Table 6. Upon recalculation, the master spreadsheet is complete and can be altered or copied for future use. CHAPTER IV RESULTS The master spreadsheet file for this research effort is contained in the tables that follow. To reproduce such a large file taken from a Lotus 1-2-3 output print file, it is necessary to divide the master spreadsheet file into different sections. Each section will maintain the first column (media variables) of the master spreadsheet file as reference guide. For purposes of explanation, the master spreadsheet file is discussed as having three main ranges within the spreadsheet. The first range, made up ofthe first six columns, contains the basic data to be used in the WHAT'S BEST! calculations. This is where data on reach, frequency, impressions, and unit cost are stored in the master spreadsheet file. The second range is made up of columns seven through twelve, contain- ing the data to be manipulated by the WHAT'S BEST! program. The third range is columns fifteen to eighteen, where the numbers to be recalculated in to integers are stored in the master spreadsheet file. To better illustrate further discussions, Illustration 3 indicates how the ranges are divided into separate’sections which can be viewed in Tables 10 through 21 . Of note in this results discussion are the abbreviations, or spreadsheet codes, for columns and rows that are indicated in 49 50 capital letters throughout this chapter. This is to provide a better understanding of the Tables 10 through 21 and can be used for reference when viewing the appendices. To provide some continuity to the viewing of the master spread- sheet file in Tables 10 through 21, they are placed on pages 55 to 66 after the discussion of all the sections as presented in Illustration 3 are completed. The following illustration indicates how all twelve sections would fit togetherto form the master spreadsheet file as they were adopted from the Lotus 1-2-3 output. Illustration 3 — Lotus 1-2-3 output of the master spreadsheet file in sectional form. (Section 1 Section 2 Section 3\ Table 10 Table 11 Table 12 Section 4 Section 5 Section 6 Table 13 Table 14 Table 15 Section 7 Section 8 Section 9 Table 16 Table 17 Table 18 Section 10 Section 11 Section 12 \T able 19 Table 20 Table 21 j The first section of the master spreadsheet is presented in Table 10 and has six columns and three rows of media categories. The first column lists the media variables (eg. NET 15's) in each media category (99. TELEVISION) along with column 2, (REACH in i,000's), column 3 (Frequency, abbreviated FREQ), column 4 (Impressions, abbreviated IMPRSS in 1 ,000's), column 5 (UNIT COST in 1,000’s) and column 6 (the cost per thousand dollars, abbreviated C.P.M.$). 51 The rows contain the media categories: television (TELEVISION), newspaper (NEWSPAPER), and weekly magazine (WEEKMG). The second section, Table 11 has columns seven through twelve, and the three media categories, as in Table 10 across the rows. Column 7 contains the number of units selected (# OF UNITS SELECTED) by WHAT’S BEST! during an optimization process. How- ever, as with all adjustable cells (or those dependent on them) in the master spreadsheet file, these are set to zero. Column 8 is the TOTAL COST OF THE UNITS SELECTED IN $1,000’s. Column 9 contains the notation (<), which means lessthan or equal to the constraint cell in WHAT'S BEST! LP program. For illustrative purposes in these tables, this column is closely combined with Column 10, the constraint limit ($LIMIT), underthe overall heading Constraints. In column 11, the NON-NEGATIVE column are the slack cells generated by WHAT‘S BEST! when the command sequence [Prt Sc] [<] is invoked. As these are adjustable cells, they too are set to zero in the master spreadsheet file. In column 12 are the total impressions in 1,000's (TOTAL IMPRSS) generated, also set to zero as these cells are dependent on the selection of media units in Column 7. The rows contain the media categories television, newspaper and weekly magazine. The third section, Table 12, contains columns 13 through 18, and three rows of media categories. In Column 13 are the number of integer units selected after the recalculation process (abbreviated ff OF UNITS @ INTEGER VALUE). Column 14 contains the total cost after recalculation of an integer value (abbreviated TOTAL @ iNT. RECALC. UNIT COST ). Column 15 contains the notation (<), which means less than or equal and represents the slack cell generation to WHAT'S BESTl. Column 16 holds the constraint limits ($ LIMIT), which do not change from the original WHAT'S BEST! optimization range 52 (columns seven through twelve) to the WBCALC recalculation range that holds columns thirteen through eighteen. Column 17 contains the slack cells and is titled NON-NEGATIVE as before in Column 11. In the final column, column 18, are the total number of impressions after recalculation (abbreviated TOTAL RECALC. IMPRSS.) using integer values instead of fractional values as is the case in Column 12. The rows contain the media categories television, newspa- per and weekly magazine. In the fourth section, Table 13 contains columns one through six as in section one. However, the rows are the media categories, monthly magazine (MNTH.MG), business magazine (BUSN.MG), and consumer magazine (CONSMG). The fifth section, Table 14 contains columns seven through twelve, the same as section two, with three rows of media categories. The rows are monthly magazine, business magazine, and consumer magazine. The sixth section, Table 15 has columns thirteen through eighteen, the same as section three. There are three rows of media categories; monthly magazine, business magazine, and consumer magazine. The seventh section, Table 16, has columns one through six as in section one (media variables, reach, frequency, impressions, unit cost and cost per thousand). There are four rows of media categories which are Radio, Outdoor Advertising, Transit and Direct Mail. The eighth section Table 17 has columns seven through twelve as are in section two (number of units selected, total cost of units selected, <, constraint limits, non-negative and total impressions). The four rows of media categories are radio, outdoor advertising, transit advertising, and direct mail advertising. The ninth section Table 18 has columns thirteen through eighteen, as are in section three (number of integer units selected, total cost recalculated for integer value, <, constraint limits, non-negative, and total 53 impressions recalculated). The four rows of media categories are radio, outdoor, transit and direct mail advertising. The tenth section, Table 19, has the same column headings as are in section one. The row is for special advertising media variables. The eleventh section Table 20 has the same columns as section two with the special adver- tising media variables along its row. Also in this section is the area where the TOTAL BUDGET SPENT (in $1 ,000's), BUDGET LIMIT (in $1 ,000's), TOTAL NET BUDGET, and the total number of impressions (abbreviated, TOTAL IMPRSS. in $1 ,000's) , after optimization are calculated. The twelfth section, Table 21, has the same columns as section three with the row containing the special advertising media variables. AS in section eleven (Table 20), this twelfth section contains the area where the BUDGET SPENT, BUDGET LIMIT, TOTAL NET BUDGET and TOTAL IMPRSS. in 1 ,000's, after recalculation using integer values, are computed. Tables 10 through 21 represent section one through section twelve of the Lotus1-2-3 output (as detailed in Illustration 3) and follow on the next twelve pages. It is important to note that in the master spreadsheet file that some columns are set to zero before optimization and recalculation. This enables the user of the master spreadsheet file to follow WHAT‘S BEST! through their own optimization process. Problems can be compounded greatly when values are left in the masterfile. Thus, it is recommended that work done there be sent to output files. Also note, that in the following tables some cells show the abbreviated formula for that cell as determined in the instructional macro (eg. + $RADIO, which represents the total constraint limit placed on the mediacategory). Cells dependent on these Specific cells are indicated with the notation ”N.A.,” as that figure would not yet be determined. TABLE 10. — Master file, columns 1 thru 6, rows 1 thru 3 of computer printout MEDIA VARIABLES TELEVISION NET 15's NET 30's CAB 15's CAB 30's LOC 15's LOC 30‘s TOTAL AVG. NEWSPAPER FULL PG. HALF PG. 6X4“ PG. 8X4“ PG. 4 PG. INS. 6 PG. INS. TOTAL AVG. WEEK MG. FR COVER BK COVER 2nd PAGE 3rd PAGE EXT. FLAP TWO PAGE ONE PAGE TOTAL AVG. REACH 1 .0005 4,850 5,575 3,335 4,275 545 880 19,460 3,243 635 425 1 65 1 90 625 630 2,670 445 1 ,150 905 775 745 950 865 61 0 6,000 857 FREQ. 1.0 1.0 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0 2.0 2.0 3.0 3.0 3.0 3.0 1.0 2.0 2.0 IMPRSS. 1 ,000's 4,850 5,575 3,335 4,275 545 880 1 9,460 3,243 1 ,270 850 330 380 1 ,250 1 .260 5,340 890 345 2,71 5 2,325 2,235 950 1 ,730 1 .220 14,625 2,089 UNIT COST $1 000's 175.00 225.00 1 10.00 155.00 30.00 45.00 740.00 123.33 60.00 47.00 22.00 28.50 98.00 109.50 365.00 60.83 100.00 85.00 70.00 60.00 85.00 75.00 55.00 530.00 75.71 S C.P.M. 27.71 24.78 30.32 27.58 18.17 19.56 127.37 21.23 10.58 9.04 7.50 6.67 6.38 5.75 45.92 7.65 11.50 10.65 11.07 12.42 11.18 11.53 11.09 79.44 11.35 55 TABLE 11. — Master file, columns 7 thru 12, rows 1 thru 3 of computer printout #OF UNITS TOTAL COST SELECTED OF UNITS SELECTED MEDIA VARIABLES $1,000's TELEVISION NET 15's 0.00 0.00 NET 30's 0.00 0.00 CAB 15's 0.00 0.00 CAB 30's 0.00 0.00 LOC 15's 0.00 0.00 LOC 30's 0.00 0.00 TOTAL 0.00 AVG. 0.00 NEWSPAPER FULL PG. 0.00 0.00 HALF PG. 0.00 0.00 6X4” PG. 0.00 0.00 8X4” PG. 0.00 0.00 4 PG. INS. 0.00 0.00 6 PG. INS. 0.00 0.00 TOTAL 0.00 AVG. 0.00 WEEK MG. FR COVER 0.00 0.00 BK COVER 0.00 0.00 2nd PAGE 0.00 0.00 3rd PAGE 0.00 0.00 EXT. FLAP 0.00 0.00 TWO PAGE 0.00 0.00 ONE PAGE 0.00 0.00 TOTAL 0.00 AVG. 0.00 CONSTRAINTS $ LIMIT 2500 2500 2500 2500 AAAAAA 1 000 +$TV <500 <500 <500 <500 <500 <500 +$RADIO 400 400 400 400 400 400 400 AAAAAAA +$MAGAZINE NON- NEGATIVE 2,500 2,500 2,500 2,500 1 .000 1 .000 NA. 500 500 500 500 500 500 400 400 400 400 400 400 400 TOTAL IMPRSS. 000000 00 000000 00 0000000 0 56 TABLE 12. — Master file, columns 13 thru18, rows 1 thru 3 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE TELEVISION NET 15's 0 0.00 < 2500 2,500 0 NET 30's 0 0.00 < 2500 2,500 0 CAB 15's 0 0.00 < 2500 2,500 0 CAB 30's 0 0.00 < 2500 2,500 0 LOC 15's 0 0.00 < 1000 1,000 0 L00 30's 0 0.00 < 1000 1,000 0 TOTAL 0.00 NA. NA. 0 AVG. 0.00 0 NEWSPAPER FULL PG. 0 0.00 < 500 500 0 HALF PG. 0 0.00 < 500 500 0 6X4' PG. 0 0.00 < 500 500 0 8X4“ PG. 0 0.00 < 500 500 0 4 PG. INS. O 0.00 < 500 500 0 6 PG. INS. 0 0.00 < 500 500 0 TOTAL . 0.00 NA. NA. 0 AVG. 0.00 0 WEEK MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 20d PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 EXT. FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL - 0.00 NA NA. 0 AVG. 0.00 O TABLE 13. — Master file, columns 1 thru 6, rows 4 thru 6 of computer printout REACH 1,000's MEDIA VARIABLES MNTH. MG. FR COVER 1,005 BK COVER 885 2nd PAGE 665 3rd PAGE 580 EXT .FLAP 825 TWO PAGE 605 ONE PAGE 535 TOTAL 5,100 AVG. 729 BUSN. MG. FR COVER 775 BK COVER 725 2nd PAGE 665 3rd PAGE 660 TWO PAGE 685 ONE PAGE 595 TOTAL 4,1 05 AVG. 684 CONS. MG. FR COVER 445 BK COVER 400 TWO PAGE 355 ONE PAGE 310 4PG. INS. 380 TOTAL 1,890 AVG. 378 FREQ. 3.0 3.0 3.0 3.0 1.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0 2.0 IMPRSS. 1 ,000's 3,015 2,655 1,995 1,740 845 1,210 1,070 12,510 1,787 1 ,550 1 ,450 1 .330 1,320 1 ,370 1,190 8,210 1,368 890 800 71 0 620 760 3,780 756 UNIT COST $1 000's 1 15.00 98. 00 77.00 68.00 95.00 75.00 38.50 566.50 80.93 92.00 83.00 78.00 72.00 58.50 33.00 416.50 69.42 82.00 76.50 34.00 1 8.50 65.00 276.00 55.20 C.P.M. $ 8.74 9.03 8.64 8.53 8.68 8.07 13.90 65.58 9.37 8.42 8.73 8.53 9.17 11.71 18.03 64.59 10.77 5.43 5.23 1 0.44 1 6.76 5.85 43.70 8.74 55 TABLE 14. — Master file, columns 7 thru 12, rows 4 thru 6 of computer printout I #OF UNITS TOTAL COST SELECTED OF UNITS SELECTED MEDIA VARIABLES $1,000's MNTH. MG. FR COVER 0.00 0.00 BK COVER 0.00 0.00 2nd PAGE 0.00 0.00 3rd PAGE 0.00 0.00 EXT.FLAP 0.00 0.00 TWO PAGE 0.00 0.00 ONE PAGE 0.00 0.00 TOTAL 0.00 AVG. 0.00 BUSN. MG. FR COVER 0.00 0.00 BK COVER 0.00 0.00 2nd PAGE 0.00 0.00 3rd PAGE 0.00 0.00 TWO PAGE 0.00 0.00 ONE PAGE 0.00 0.00 TOTAL 0.00 AVG. 0.00 CONS. MG. FR COVER 0.00 0.00 BK COVER 0.00 0.00 TWO PAGE 0.00 0.00 ONE PAGE 0.00 0.00 4PG. INS. 0.00 0.00 TOTAL 0.00 AVG. 0.00 CONSTRAINTS 8 NON- LIMIT NEGATIVE < 400 400 < 400 400 < 400 400 < 400 400 < 400 400 < 400 400 < 400 400 +$MAGAZINE N.A. < 400 400 < 400 400 < 400 400 < 400 400 < 400 400 < 400 400 +$MAGAZINE N.A. s 400 400 < 400 400 < 400 400 < 400 400 < 400 400 +$MAGAZINE N.A. TOTAL IMPRSS. 0000000 00 000000 00 .00000 00 59 TABLE 15. — Master file, column513 thru18, rows 4 thru 6 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE ' UNIT COST 3 NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE MNTH. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 EXT.FLAP 0 0.00 < 400 ' 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 0.00 N.A. N.A. 0 AVG. 0.00 0 BUSN. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 0.00 N.A. N.A. 0 AVG. 0.00 0 CONS. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 4PG. INS. 0 0.00 < 400 400 0 TOTAL 0.00 N.A. N.A. 0 AVG. 0.00 0 60 TABLE 16. — Master file, columns 1 thru 6, rows 7 thru 10 of computer printout REACH 1,000's MEDIA VARIABLES RADIO SPOT 15's 58 SPOT 30's 73 SP. NEWS 44 SP. REPORT 28 TOTAL 203 AVG. 51 OUTDOOR 6M LEASE 1 2X25 6 14X48 12 ROTARY PLAN 12XZS 12 14X48 18 TOTAL 48 AVG. 12 TRANSIT BUS CARD 45 POSTERS 65 TOTAL 110 AVG. 55 DIRECT MAIL LEAFLETS 5 BROCHURE 25 . NEWSLETTER 12 TOTAL 42 AVG. 14 FREQ. 4.0 4.0 6.0 6.0 3.0 5.0 3.0 5.0 4.0 2.0 1.0 3.0 1.0 IMPRSS. 1 ,000's 232 292 264 1 68 956 239 18 60 36 90 204 51 180 130 310 155 75 12 92 31 UNIT COST $1 000's 1 8.00 32.50 9.00 1 2.75 72.25 1 8. 06 2.10 3.15 1 5.60 1 7.40 38.25 9.56 22.25 36.50 58.75 29.38 0.60 55.75 2.25 58.60 1 9.53 C.P.M. S 3.22 2.25 4.89 2.20 12.55 3.14 2.86 3.81 0.77 1.03 6.47 2.12 2.02 1.78 3.80 1.90 8.33 0.45 5.33 14.12 4.71 TABLE 17. — Master file, columns 7 thru 12, SELECTED MEDIA VARIABLES RADIO SPOT 15's 0.00 SPOT 30's 0.00 SP. NEWS 0.00 SP. REPORT 0.00 TOTAL AVG. OUTDOOR 6M LEASE 1 2X25 0.00 1 4X48 0.00 ROTARY PLAN 12x25 (100 1 4X48 0.00 TOTAL AVG. TRANSIT BUS CARD 0.00 POSTERS 0.00 TOTAL AVG. DIRECT MAIL LEAFLETS 0.00 BROCHURE 0. 00 NEWSLETTER 0.00 TOTAL AVG. #OF UNITS TOTAL COST OF UN ITS SELECTED $1 ,000's 100.00 75.00 100.00 100.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 61 CONSTRAINTS $ NON - LIMIT NEGATIVE < 150 100 < 150 100 < 150 100 < 150 100 +$RADIO N.A. < 200 200 < 200 200 < 200 200 < 200 200 +$OTHER N.A. < 300 300 < 300 300 +$OTHER N.A. < 15 15 < 200 200 < 15 15 +$OTHER N.A. rows 7 thru 10 of computer printout TOTAL IMPRSS. 0000 CO 62 TABLE 18.— Master file, columns 13 thru18, rows 7 thru10 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST 3 NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE RADIO SPOT 15's 0 100.00 < 150 100 0 SPOT 30's 0 75.00 < 150 100 0 SP. NEWS 0 100.00 < 150 100 0 SP. REPORT 0 1 00.00 < 1 50 1 00 0 TOTAL 0.00 N.A. N.A. 0 AVG. 0.00 0 OUTDOOR 6M LEASE 12x25 0 0.00 < 200 200 0 14X48 0 0.00 < 200 200 0 ROTARY PLAN 12x25 0 0.00 < 200 200 0 14X48 0 0.00 < 200 200 0 TOTAL 0.00 N.A. N.A. 0 AVG. 0.00 0 TRANSIT BUS CARD 0 0.00 < 300 300 0 POSTERS 0 0.00 < 300 300 0 TOTAL N.A. N.A. 0 AVG. DIRECT MAIL LEAFLETS 0 0.00 < 1 5 15 0 BROCHURE 0 0.00 < 200 200 0 NEWSLETTER 0 0.00 < 1 5 1 5 0 TOTAL 0.00 N.A. N.A. 0 AVG. 0.00 0 63 TABLE 19. — Master file, columns 1 thru 6, row 11 of computer printout REACH FREQ. IMPRSS. UNIT COST 1 ,000's 1 ,000's $1 000's MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 1 00 2.0 200 520.00 CATALOGUE 20 1.0 20 1 1.50 BANNERS 1 1.0 1 1 1.85 MAPS 25 2.0 50 88.75 IMPRINTS T-SHIRTS 1 4.0 4 5.00 B. STICK 5 2.0 10 3.70 POSTERS 5 2.0 1 0 1 0. 00 MENUS 1 1.0 1 4.35 KEYS 24 1.0 24 9.50 TOTAL 232 722 71 9.65 AVG. 26 80 79.96 C.P.M. 0.19 1.74 0.08 0.28 0.20 1.35 0.50 0.23 2.53 7.11 0.79 TABLE 20. — Master file, columns 7 thru 12, row 11 of computer printout #OF UNITS TOTAL COST OF UNITS SELECTED $1,000's SELECTED MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 0.00 CATALOGUE 0.00 BANNERS 0.00 MAPS 0.00 IMPRINTS T-SHIRTS 0.00 B. STICK 0.00 POSTERS 0.00 MENUS 0.00 KEYS 0.00 TOTAL AVG. 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 BUDGET SPENT $1 ,000's 0.00 64 CONSTRAINTS $ NON- LlMlT NEGATIVE < 1200 1,200 < 40 40 < 40 40 < 600 600 < 30 30 < 30 30 < 30 30 < 30 30 < 30 30 +$SPECIAL N.A. BUDGET TOTAL LIMIT NET $1 ,000's BUDGET +$BUDGET 0 TOTAL IMPRSS. 0000 00000 0 TOTAL IMPRSS. 1 ,000's 65 TABLE 21. — Master file, columns 13 thru 18, row 11 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST 5 NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 0 0.00 < 1200 1,200 0 CATALOGUE 0 0.00 < 40 40 0 BANNERS 0 0.00 < 40 40 0 MAPS 0 0.00 < 600 _ 600 0 IMPRINTS T-SHIRTS 0 0.00 < 30 30 0 B. STICK 0 0.00 < 30 30 0 POSTERS 0 0.00 < 30 30 0 MENUS 0 0.00 < 30 30 O KEYS 0 0.00 < 30 30 0 TOTAL 0.00 N.A. N.A. 0 AVG. 0.00 0 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1,000's $1 ,000's BUDGET 1 ,000's 0.00 N.A. 0 0 66 With an overview of the master spreadsheet file complete, a discussion of the inputs and outputs forthis research effort may be investigated. The first inputs into the master Spreadsheet file are the three budget levels and the three budget allocation strategy scenarios applied to each budget level. This produced nine different output files that had been through the WHAT'S BEST! optimization and recalculation process. The output files for the $12 million budget level were labeled Best12A, Best12B and Best12C (which breaks down; Best for WHATS BEST!, 12 for the $12 million budget level, and A for the first budget allocation strategy scenario). The output files forthe $5 million budget were labeled Best5A, BestSB, and BestSC. Meanwhile, the $1 million budget files were labeled Best1 A, Best1 B and Best1 C. The A, B and C represent each ofthe three different budget allocation strategies (see Table 5). This follows the labeling pattern used in Table 5. These output files may all be found in the appendices; Best12A in appendix A, Best12B in appendix B, Best12C in appendix C, Best5A in appendix D, BestSB in appendix E, BestSC in appendix F, Best1 A in appendix G, Best1 B in appendix H, and Best1C in appendix J. To produce these output files in the master spreadsheet file WHAT’S BEST! used 1,601 of 3,000 numeric cells, and 310 of 1,499 optimizable cells available. It also used 647 of 24,000 possible coefficients and 3,201 instructions ofthe 17,999 maximum that were available. Thus a substantial addition could be made to the master spreadsheet file without taxing the limits of the 1.2 version of WHATS BESTl. More powerful versions are available to deal with large scale . optimization problems. For each output file, the WHATS BEST! program generates an optimal solution after a certain number of attempts or tries to solve the linear program- ming matrix for which it has been invoked. Each of the files and the number of 67 tries WHAT'S BEST! took to find the most optimal and feasible (ie. maintaining the constraints) solution are presented in Table 22. The total number of tries to find an optimal solution does not indicate any correlation between the number of attempts (or tries) and the number of total impressions computed. It appears the more tries the more impressions in the case of the Best12 files, but the Best1 and Best5 files don't indicate this correlation to hold true. Table 22. - WHAT'S BEST! 's number of tries to find an optimal solution for each output file. File Name Feasible Solution Feasible Solution Optimal Solution Optimal Solution's After 100 Tries After 200 Tries in 1,000's Number of Tries Best12A 158,259.10 260,966.50 281 ,570.40 224 Best12B 233,339.90 283,932.50 307,015.30 229 Best1 20 104,756.70 188,692.50 206,008.20 226 BestSA 81,260.05 118,638.30 125,110.10 219 BestSB 110,756.20 129,186.20 137,115.10 211 BestSC 56,281.38 86,480.23 91,208.23 220 Best1 A 18,707.18 26,980.57 27,030.35 203 Best1 B 23,560.15 " ' ' 28,898.54 197 Best1 C 14,359.71 20,908.86 21,169.41 206 - All figures in columns 1, 2 and 3 are in thousands of impressions ( 1,000's) The remainder of this Results Chapter is dedicated to comparisons of results between fractional value and integer value solutions for each allocation strategy and budget level. Comparisons are only made between outputs from 68 files with like budgets. Thus, Best12 files are only compared to other Best12 files and not Best12 vs. Best5. The comparisons will be made with the aid of the output file macro that was created for each spreadsheet file. These output file macros contain the results after optimization and recalculation. A sample output file follows in Table 23. Table 23. - Sample ouput macro to display results for fractional and integer values. RESULTS OF OPTIMIZATION USING FRACTIONAL MEDIA UNITS Input Menu Output Menu Total Budget ( $1,000's ) - 2,000 Net Budget ($1,000's) - 0 Media Budget Budget Group Allocation Allocation Impressions Cost/ % $1 ,000's 1,000's Impression Television 10.0 200 3.032 0.0150 News/Radio 10.0 200 2,729 0.0014 Magazines 20.0 400 10.997 0.0027 Other media 20.0 400 4.081 0.0010 Special media 40.0 800 12.888 0.0016 Totals a 100.0 2,000 33.727 0.0017 RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Output Menu Total Budget ( $1,000's ) =2,000 Net Budget ( $1 ,000's ) .- 75 Media Budget Budget Group Allocation Allocation lmpressions Cost! % $1 ,000's 1 ,000's Impression l Television 10.0 I 150 3.000 0.0020 News/Radio 10.0 180 2,000 0.001 1 Magazines 20.0 400 10.997 0.0027 Other media 20.0 400 4.081 0.0010 Special media 40.0 795 12.777 0.0016 The first output file to be discussed is that of Best 12A. After optimization and recalculation with the budget level $12 million and using budget allocation 69 strategy A (see Table 5), 281,570,000 (rounded to nearest thousand) impres- sions were produced using the number of selected media units in fractional values, while using integer value media units, 272,114,000 impressions were generated. This is a difference of 9,456,000 impressions. There is also a difference in the budget remaining after optimization and recalculation. The use of fractional values allows all of the budget to be consumed, ie. a net budget of zero dollars. Meanwhile, the use of integer values leaves a net budget of $405,000. There is such a large difference in impression levels and net budget levels because during the rounding of all the fractional media units to integer media units a portion of the budget remains unspent or not allocated. For example, in the media category television the optimal solution generated by WHAT’S BEST! selects 22.73 units of cable 15-second spot advertisements (a fractional value), but during recalculation to integer values this 22.73 becomes 22 units of cable 15-second spot advertisements. For more detail refer to Appendix A for detail concerning fractional and integer values selected in the optimal solutions to the $12 million budget using allocation strategy A. This results in an allocation loss of $80,300 dollars as the unit cost of one cable 15- second spot advertisement is $1 10,000. The $80,300 is then considered part of the net budget afterthe recalculation process. A complete display of the results macro for Best12A is in Table 24 on the following page. The output file Best12B has a budget level of $1 2 million and uses budget allocation strategy B (see Table 5) and generates 307,015,000 impressions. After recalculation to integer values, it generates 298,356,000 impressions. A difference of 8,659,000 impressions. The net budget forfractional values is zero, but the net budget for integer values of media variables selected is $404,000. 70 Table 24. - Results macro for the spreadsheet $12 million budget, budget allocation strategy A RESULTS OF OPTIMIZATION USING FRACTIONAL MEDIA UNITS Input Menu Output Menu Total Budget ( $1 ,000's ) - 12,000 Net Budget ( $1.000's ) - 0 Media Budget Budget Group Allocation Allocation Impressions Cost! % $1 ,000's 1 ,000's Impression Television 25.0 3.000 89.653 0.0335 News/Radio 12.5 1,500 28.844 0.0520 Magazines 37.5 4.500 143.791 0.0313 Other media 12.5 . 1.500 17.867 0.0840 Special media 12.5 1.500 1.417 0.0558 Totals - 100.0 12.000 281.570 0.0426 RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Output Menu Total Budget ( $1.000's ) -12,000 Net Budget ( $1.000's ) -405 Media Budget Budget Group Allocation Allocation Impressions Cost/ % $1 ,000's 1,000's Impression Television 25.0 2.945 87.920 0.0335 News/Radio 12.5 1 .41 6 273.100 0.0519 Magazines 37.5 4.302 137.745 0.0312 Other media 12.5 1 .492 17.779 0.0839 Special media 12.5 1.440 1.360 0.0589 Totals - 1 00.0 1 1 .595 272.1 14 0.0426 Again, as in Best12A, the net budget computed after integer values are used is higher due to allocation loses during the rounding off process. The complete output results file for Best128 are related in Table 25 on the following page. The final output file forthis budget level, Best120 using $12 million dollars and allocation strategy 0 (see Table 5) has atotal impression level of 206,008,000 impressions. However, this total level of impressions uses fractional values for media units selected to arrive at the optimal solution. Using integer values for 71 Table 25. - Results macro for the spreadsheet file,$12 million budget. budget allocation strategy 8. RESULTS OF OPTIMIZATION USING FRACTIONAL MEDIA UNITS Input Menu Total Budget ($1.000's) - 12,000 Media Budget Group Allocation % Television 50.0 News/Radio 20.0 Magazines 20.0 Other media 5.0 Special media 5.0 Totals - 100.0 RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Total Budget ( $1,000's ) .12,000 Media Budget Group Allocation % Television 50.0 News/Radio 20.5 Magazines 20.5 Other media 5.0 Special media 5.0 Totals - 100.0 Output Menu Net Budget ($1.000's) - 0 Budget Allocation Impressions Cost! $1 ,000's 1 ,000's Impression 6,000 172.662 0.0348 2.400 40.783 0.0558 2.400 82.863 0.0290 600 9.977 0.0612 600 91 0 0.6596 12.000 307.015 0.0391 Output Menu Net Budget ( $1,000's ) -404 Budget Allocation Impressions Cost/ $1 ,000‘s 1 ,000's Impression 5,800 1 66.920 0.0347 2,277 38.81 6 0.0587 2.377 82.045 0.0290 589 9.715 0.0607 553 860 0.6426 1 1 .596 298.356 0.0389 the media units selected there are a total of 200,713,000 impressions. A difference of 5,296,000 impressions. The difference in total impression levels relates directly to the rounding off process as do the total net budget levels. For Best12C, using fractional values, there is a net budget of zero, and with integer values a net budget of $567,000. A complete display of the results for Best12C are portrayed in Table 26. 72 Table 26. - Results macro for the spreadsheet file, $12 million budget, budget allocation strategy C. RESULTS OF OPTIMIZATION USING FRACTTONAL MEDIA UNITS Input Menu Output Menu Total Budget ( $1,000's ) - 12,000 Net Budget ( $1.000‘s ) - 0 Media Budget Budget Group Allocation Allocation Impressions Cost! % $1 ,000's 1 ,000's Impression Television 10.0 1.200 36.382 0.0330 News/Radio 1 0.5 1 .200 24, 1 94 0.0496 Magazines 30.0 3.600 1 18.875 0.0303 Other media 25.0 3,000 24.532 0.1223 Special media 25.0 3.000 2.026 1.4809 Totals - 100.0 12,000 206.008 0.0583 RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Output Menu Total Budget ( $1,000’s ) -12,000 Net Budget ( $1,000's ) -567 Media Budget Budget Group Allocation Allocation Impressions Cost! % $1,000's 1,000's Impression Television 10.0 1.210 36.685 0.0330 News/Radio 10.0 1.416 23.032 0.0495 Magazines 30.0 3,476 1 14.945 0.0302 Other media 25.0 2.949 24.191 0.1219 Special media 25.0 2.658 1.860 0.4288 Totals - 100.0 I 11.433 272.114 0.0570 The output file Best12B generates the greatest number of total impres- sions (307,015,000 and 298,356,000) for both the functional and integer value results respectively. This compares to 281 ,570,000 impressions (fractional) and ' 272,114,000 impressions (integer) for Best12A and 206,008,000 impressions (fractional) and 200,713,000 (integer) for Best12C. The levels of impressions generated are directly related to the allocation scenarios that each output file had 73 assigned to it. Best128 producesthe greatest level of impressions because 50% of the budget is allocated to the media group, TV. Television is considered to have the highest potential for generating impressions. This is confirmed by the high reach and impression levels that the television media variables have in the data gathered for this study. When television reach and impressions values are contrasted with a media category like direct mail, there is an obvious advantage that television has over the other media categories in terms of the potential impression generation capabilities that exist within the media variables in television. Therefore, the more that is allocated to television the higher the impression levels will be. The cost per impression is lowest for Best12B at 0.0391 with 307,915,000 impressions generated. Next is Best12A at 0.0426 with 281,570,000 impres- sions, and then comes Best12C at 0.0583 with 200,713,000 impressions. The high impression producing media groups, TV and Magazines, consistently have the lowest cost per impression levels, followed by the media groups News/Radio and Other Media which are the middle range impression producers and have the next lowest Cost per Impression values. The media group, Special Media, trails all other groups with the highest Cost per Impression figures. In the output file for Best5A, the $5 million budget level is used along with budget allocation scenario A (see Table 5). The optimal solution gives a total impressions level of 125.1 10,000 impressions with a net budget of zero dollars. After recalculation from fractional values to integer values the total impressions level is 121 ,190,000, and a net budget of $156,000 remains. The difference in impression levels is 3,920,000. The differences are caused by two major allocation loses. The first in the media group, TV, where $40,000 remained, and 74 secondly, in Magazines, where $75,000 remained unallocated after the recalculation to integer values. This means that $1 ,210,000 was spent out of the possible $1,250,000 allocated to the media group TV. Meanwhile, $1,800,000 of the budget was allocated for the media group Magazines. out of a possible $1,875,000. The main budget allocation loss in the media group TV occured when 1 1.36 units of cable 15-second spot advertisements became 1 1 units after recalculation to integer values. The main budget allocation losses In the media group Magazines happened when 6.67 units of the 3rd page advertisement in the media category Weekly Magazine were converted to 6 units and when 3.57 units of the one page advertisements in the media category Monthly Magazine were converted to 3 units. A complete display of the results from the Best5A spreadsheet output file can be found in Table 27 on this and the following page. Table 27. - Results macro for the spreadsheet file,$5 million budget, budget allocation strategy A. RESULTS OF OPTIMIZATION USING FRACTIONAL MEDIA UNITS Input Menu Output Menu Total Budget ( $1,000's ) - 5,000 Net Budget ($1.000‘s) - 0 Media Budget Budget Group Allocation Allocation Impressions Cost! % $1 ,000's 1 ,000's Impression Television 25.0 1 .250 37.898 0.0330 News/Radio 12.5 625 14.404 0.0434 Magazines 37.5 1 .875 64.918 0.0289 Other media 12.5 625 7.348 0.0851 Special media 12.5 625 542 1.1523 Totals - 100.0 5.000 125.110 0.0400 75 Table 27 (cont'd) RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Output Menu Total Budget ( $1,000'S ) =5,000 Net Budget ( $1 ,000's ) =156 Media Budget Budget Group Allocation Allocation Impressions Cost/ % $1 ,000's 1,000's Impression Television 25.0 1 .945 36.685 0.0330 News/Radio 12.5 624 14.384 0.0434 Magazines 37.5 1.800 62.370 0.0289 Other media 12.5 613 7.235 0.0848 Special media 12.5 596 516 1.1557 Totals . 100.0 4.844 121.190 0.0400 For the output file BestSB that has a $5 million budget and the budget allocation scenario 8 (see Table 5) the optimal solution found was a total impressions level of 137,155,000 with a zero net budget. Afterthe recalculation to integer values the impressions level is 131,929,000 with a net budget of $184,000. Of this $184,000 dollars, $171,000 results from budget allocation losses when fractional values are converted to integer values for these media groups selected during optimization. The media group TV was allocated $2,500,000 and used $2,420,000, a net of $80,000. The media group Magazines was allocated $1 ,000,000 and used $948,000, a net of $52,000. And third the media group News/Radio was also allocated $1 million dollars and used $961 .000, a net of $39,000. The results of BestSB are found in Table 28 following page. In the output file for BestSC, the $5 million budget level is used in conjunction with budget allocation scenario C (see Table 5). The optimal solution has atotal impressions level of 91 ,208,000 impressions with a net budget of zero 76 Table 28. - Results macro for the spreadsheet file, $5 million budget, budget allocation strategy 8. RESULTS OF OPTIMIZATION USING FRACTIONAL MEDIA UNITS Input Menu Output Menu Total Budget ( $1.000‘s ) - 5,000 Net Budget ($1.000's) - 0 Media Budget Budget Group Allocation Allocation Impressions Cost/ % $1 ,000's 1 ,000's Impression Television 50.0 2.500 75.795 0.0330 News/Radio 20.0 1 .000 20.783 0.0480 Magazines 20.0 1.000 36.863 0.0277 Other media 5.0 250 4.977 0.0613 Special media 5.0 250 331 0.7550 Totals . 100.0 5,000 137.155 0.0365 RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Output Menu Total Budget ( $1,000's ) -5,000 Net Budget ( $1 ,000's ) .184 Media Budget Budget Group Allocation I} Allocation Impressions Cost! % $1 ,000's 1 ,000's Impression Television 50.0 2.420 73.370 0.0330 News/Radio 20.0 961 1 9.978 0.0481 Magazines 20.0 949 34.240 0.0277 Other media 5.0 245 4.025 0.0610 Special media 5.0 241 316 0.7638 Totals - 100.0 4.816 131.929 0.0365 when the fractional values are used during optimization. After recalculation, the total impression level is 85,863,000, with a net budget remaining of $229,000, using integer values. The reason forthe differences again lie in the rounding off process. In terms of dollars left unspent among the media groups after recalculation, there is $35,000 left in the media group Other (Outdoor, Transit and Direct Mail), another $45,000 left unallocated for the media group Special, and 77 $60,000 unspent in the media group TV. The major budget allocation losses occur in the media group Magazines where $1,407,500 was spent out of a $1,500,000 total. The $92,000 left unallocated is due to rounding in the media category Weekly Magazine where 3.50 front covers and 6.67 third pages became 3 and 6 respectively. The results forthe output file, BestSC can be found in Table 29. Table 29. - Results macro for the spreadsheet file, $5 million budget. budget allocation strategy C. RESULTS OF OPTIMIZATION USING FRACTIONAL MEDIA UNITS Input Menu Output Menu Total Budget ( $1,000’s ) - 5,000 Net Budget ($1.000's) - 0 Media Budget Budget Group Allocation Allocation Impressions Cost! % $1 ,000's 1 ,000's Impression Television 10.0 500 15.159 0.0330 News/Radio 10.0 500 1 1.808 0.0423 Magazines 30.0 1,500 53.129 0.0282 Other media 25.0 1.250 10.305 0.1223 Special media 25.0 1.250 807 1 .5491 Totals - 100.0 5,000 91.208 0.0548 RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Output Menu Total Budget ( $1 ,000's ) -5.000 Net Budget ( $1 ,000's ) -229 Media Budget Budget Group Allocation Allocation Impressions Cost! % $1 ,000's 1 ,000's Impression Television 10.0 440 13.340 0.0330 News/Radio 10.0 504 1 1.844 0.0426 Magazines 30.0 1 .408 49.820 0.0283 Other media 25.0 1.215 10.093 0.1204 Special media 25.0 1,205 766 1.5732 Totals - 100.0 4.771 85.863 0.0556 78 The Best5C output file produces the fewest number of total impressions, fractional or integer value, in comparison to Best5A and BestSB. BestSB produces the most impressions, 137,155,000 and 131 ,929,000. fractional and integer respectively. This is due to the budget allocation strategy that has 50%, or $2,500,000 allocated to the media group TV. The cost perimpression is also lowest for BestSB at 0.0365 with 131 ,929,000 impressions, then follows BestSA at 0.0400 with 121,190,000 impressions, and then comes BestSC at 0.0556 with 85,863 impressions. These figures occur for BestSB as it follows a high impression generating budget allocation strategy where 50% ofthe budgetdoesto TV, with the low generating media groups Other and Special media, each only getting 5% of the budget. In the output file for Best1A, the budget level is $1 million dollars and is used with budget allocation strategy scenario A (see Table 5). The optimal solution gives a total impression level of 27,030,000 impressions and a net budget of zero. After the recalculation process from fractional values to integer values, the total impression level is 24,122,000, with a net budget remaining of $123,000. The difference in impression levels is 2,908,000. The differences are caused by three major budget allocation loses. First in the media group News/ Radio where $35,000 remained, then in the groups TV and Magazine, where $30,000 remained for each mediagroup. This means that of a possible $750,000 allocated among these media groups only $655,000 was actually used up after recalculation to integer values. The main loss in media group News/Radio occurred when 0.52 full page newspaper advertisements were rounded to zero. The losses in the media groups TV and Magazines came from 2.27 Cable 15- second spot television advertisements being rounded to 2.0, and from 5.68 one 79 page business magazine advertisements being converted to 5.0. A complete table of results macro from Best1A is available in Table 30. Table 30. - Results macro forthe spreadsheet file, $1 million budget. budget allocation strategy A. RESULTS OF OPTIMIZATION USING FRACTIONAL MEDIA UNITS Input Menu Total Budget ( $1 ,000's ) - 1,000 Media Group Television News/Radio Magazines Other media Special media Totals . Budget Allocation % 25.0 12.5 37.5 12.5 12.5 100.0 RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Total Budget ( $1.000's ) -1.000 Media Group Television News/Radio Magazines Other media Special media Totals . Budget Allocation % 25.0 12.5 37.5 12.5 12.5 100.0 Output Menu Net Budget ( $1 ,000's ) - 0 Budget Allocation Impressions Cost/ $1 ,000‘s 1 ,000's Impression 250 7.653 0.0330 125 3.844 0.0366 375 13.791 0.0273 125 2.867 0.0612 125 251 0.4971 1 .000 27.030 0.0370 Output Menu Net Budget ( $1.000's ) -123 Budget Allocation Impressions Cost! $1 ,000's 1 ,000's Impression 220 6.670 0.0330 90 2.640 0.0341 345 12.655 0.0273 1 10 1 .925 0.0569 113 232 0.4853 877 24.122 0.0364 For the output file Best1B, that has a $1 million dollar budget and the budget allocation strategy Scenario B (see Table 5), the optimal solution found was a total impressions level of 28,899,000 with a net budget of zero. After 80 completing the recalculation process the impression level 25,592,000 impres- sions. There was a net budget of $98,000 remaining forthis spreadsheet output file after recalculation. This $98,000 is a result of budget allocation losses among two main media groups. The media group TV was allocated $500,000 of the budget, and used only $440,000, a difference of $60,000. The $60,000 dollar loss occured when 4.55 units of the 15-second spot cable television advertisements were rounded to 4 units. The media group Magazines was allocated $200,000 of the budget. of which only $159,000 was spent, for a net remainder of $41 .000. The $41 .000 budget allocation loss happened in large part to a fractional 1 .67 3rd page weekly magazine advertisements being converted to 1.0. as .67 of the unit cost of $60,000 approximatelyis $40,200. This makes up most of the $41,000 of the lost budget allocations in this media group. The results forthe spreadsheet output file Best1B can be found in Table 31. The first part of the table for the results after optimization using fractional media units are found on this page while the results after optimization using integer media units are continued in the table on the following page. Table 31. - Results macro forthe spreadsheet file, $1 million budget, budget allocation strategy B. RESULTS OF OPTIMIZATION USING FRACTIONAL MEDIA UNITS Input Menu Output Menu Total Budget ( $1 ,000's ) - 1,000 Net Budget ( $1 ,000's ) - 0 Media Budget Budget Group Allocation Allocation Impressions Cost! % $1 ,000's 1 ,000's Impression Television 50.0 500 15.159 0.0330 News/Radio 20.0 200 5.458 0.0366 Magazines 20.0 200 7.331 0.0273 Other media 5.0 50 818 0.0611 Special media 5.0 50 132 0.3799 Totals - 100.0 1.000 28.899 0.0346 81 Table 31 (cond't.) RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Output Menu Total Budget ( $1.000's ) =1,000 Net Budget ( $1 ,000's ) .98 Media Budget Budget Group Allocation Allocation Impressions Cost/ % $1 ,000's 1 ,000's Impression Television 50.0 440 13.340 0.0330 News/Radio 20.0 204 5.494 0.0371 Magazines 20.0 159 5.805 0.0274 Other media 5.0 50 825 0.0611 Special media 5.0 49 128 0.3797 Totals - 100.0 I 902 25.592 0.0352 In the file Best1C. the $1 million budget level is used in conjunction with the budget allocation strategy scenario C (see Table 5). The optimal solution has a total impressions level of 21 ,169,000 with a net budget of zero, when using fractional values. After recalculation, the total impression level is 19,018,000 impressions along with a net budget remaining of $79,000, using integer values. The reason for the difference of 2,151,000 impressions lie in the rounding off process. In terms of dollars unallocated after recalculation, the greatest portion is in the media group Magazines. In this media group, $48,000 is left unused due to the rounding of 4.55 one page business magazine advertisements to 4.0 and 2.50 3rd page weekly magazine advertisements being rounded to 2.0. The business magazine media variable has $18,000 and the weekly magazine media variable has $30,000 that is not allocated after the rounding-off process in the media group Magazines. A table of results for Best1 C can be found in Table 32 on the following page. 82 Table 32. - Results macro for the spreadsheet file. $1 million budget. budget allocation strategy C. RESULTS OF OPTIMIZATION USING FRACTTONAL MEDIA UNITS Input Menu Output Menu Total Budget ( $1.000's ) - 1,000 Net Budget ($1.000's) - 0 Media Budget Budget Group Allocation Allocation Impressions Cost! % $1 ,000's 1 ,000's Impression Television 10.0 100 3.032 0.0330 News/Radio 10.5 100 2.729 0.0366 Magazines 30.0 300 10.997 0.0273 Other media 25.0 250 4.081 0.0613 Special media 25.0 250 331 0.7550 Totals - 100.0 1.000 21.169 0.0472 RESULTS OF OPTIMIZATION USING INTEGER MEDIA UNITS Input Menu Output Menu Total Budget ( $1.000's ) -1,000 Net Budget ( $1 ,000's ) -79 Media Budget Budget Group Allocation Allocation impressions Cost! % $1 ,000's 1 ,000's Impression Television 10.0 110 3.335 0.0330 News/Radio 10.0 72 2,1 12 0.0341 Magazines 30.0 252 9.230 0.0273 Other media 25.0 245 4.025 0.0610 Special media 25.0 241 316 0.7638 Totals - 100.0 921 19.018 0.0484 In summary. the output file Best1B produces the greatest number of total impressions for fractional values (28,899,000) and integer values (25,592,000). This compares to 27,030,000 impressions (fractional) and 24,122,000 impres- sions (integer) for Best1A and 21,169,000 and 19,018,000 impressions. frac- tional and integer respectively. for Best1 C. As with the other output files Best12 and Best5, the levels of impressions produced are directly related to the 83 allocation scenarios that each output file follows. Best1B generates the highest level of impressions due to the fact that it is allotted 50% of the total budget for the media group TV. This group has the greatest potential for generating impressions with its high levels of reach and impressions. However, allocating all the budget to these few media groups will have dire effects on the overall media advertising strategy that must be well rounded and cover all potential advertising strategies and media variables. Thus, part of the budget is destined to go to the lower potential impression generating media groups. The Cost per Impression is lowest for Best1 B at 0.0346 with 28,899,000 impressions generated. Next is Best1 A at 0.0370 with 27,030,000 impressions, followed by Best1C at 0.472 with 21,169,000 impressions. The higher impres- sion generating media groups also have the lower Cost per Impressions levels. as indicated previously. This focuses the attention on the way the budget is allocated and further illustrates the need fora ”best" allocation strategy. However this optimization of total impressions is only one goal of the total advertising process. From all the output files Best12A through Best1C, it is evident that an almost endless possible combination of media variables would satisfy the basic conditions Specified. 80, rather than developing all such combinations and sorting them to find the best single optimal solution, which is practically prohibitive given time and money constraints, it is necessary to determine what best suits a marketing manager's individual budget allocation and budget level situation. CHAPTER V SUMMARY This research effort undertook the task of successfully applying a commercially developed linear programming package, WHAT'S BESTl. to a media selection problem. The problem was divided on the basis of three budget levels ($12 million, $5 million, and $1 million) and three budget allocation strategies (A, the mixed media allocation strategy; B, the high impression media generating strategy; and C, the low impression media generating strategy), then a master spreadsheet was developed in Lotus 1-2-3. The spreadsheet output files created were Best12A, Best12B, Best12C. Best5A, Best5B, Best5C. Best1A, Best1 B and Best1C. Each spreadsheet file had a possible 59 media variables to select from. contained in 1 1 media categories which were compiled into five media groups. Advertising dollars were allocated to each media variable, based on a combination of reach, frequency, and unit cost. The objective cell was based on total impressions (impressions being a function of reach and frequency) _ and was designated as the cell to contain the formula to be maximized. Master spreadsheet files were created (as in Tables 10 through 21 ). and the WHAT’S BEST! linear programming package invoked for each of the nine files (three budget levels each constrained by three allocation strategies). WHAT’S BEST! 84 85 successfully completed finding an optimal solution for each of the nine output files. However. the optimal solution found in each case yielded fractional values in the adjustable cells (Number of Units Selected). A fractional unit, or units (eg. 3.36 Newspaper advertisements) is not acceptable to a purchaser of media advertisements. So a recalculation process was then invoked to provide integer values forthe Number of Units Selected. This produced satisfactory results but did not fully utilize promotion budgets available. Another optimization process, or an updated release of WHAT’S BESTl, may be able to reduce the net budget levels closer to zero. In summarizing this research effort, there are three main areas to discuss. They are: 1) an evaluation ofthe computer spreadsheet and linear programming package, 2) implications of the research, and 3) recommendations based on the research. in evaluating the application of the WHAT’S BESTl’s linear programming package to the master spreadsheet developed on Lotus 1-2-3, there are three main questions to be pondered. First, did the master spreadsheet and the linear programming package fulfill the roles for which they were intended? From the successful application of WHAT’S BEST! to the master spreadsheet file and its subsequent nine output files. allowing for recalculation, it can be stated that both roles were fulfilled. The linear programming package found optimal solutions to all the different scenarios and followed all the constraints placed in the master spreadsheet. However, WHATS BEST! version 1.2 is limited by its inability to produce non-fractional results in the adjustable cells. It's only integer values are on options and there aren’t enough of these on cells available in this version of WHAT’S BEST! to suggest an alternate format in the spreadsheet files. Efforts can be made to overcome this limitation and the recalculation to integer values 86 is one such attempt. But the precise results of the optional solution must be sacrificed to complete the recalculation effort. The good news is that the sacrificing of the optimal results in this fashion may not have to continue. From discussions with General Optimization, Inc. of Chicago it appears that a new and vastly different version of WHAT’S BEST! is under development in to test phases. It will allow for integer values to be assigned to adjustable cells as one of its many new features. In evaluating the computer spreadsheet, there are a number of problems which must be addressed. The master spreadsheet was created so that scaling problems were overcome. A scaling problem results when the difference between the largest value and the smallest value is too great. Thus, figures are placed in the master spreadsheet as 175, but are read in the $1 ,000’s ($175,000). The master spreadsheet was also created in such a way that unbounded problems would not occur. An unbounded problem is one in which there are no limitations on the adjustable cells. Unbounded cells can be increased infinitely and produce an optimizing error. With the spreadsheet free of these formulation problems, WHAT’S BEST! was able to do its task success- fully on the Lotus 1-2-3 spreadsheet software program. The second question to ponder is: Are the results usable? As the research effort used differing budget levels and allocation scenarios based loosely on information gleaned from a variety of sources but borrowing heavily from previous budgets for state and foreign national tourist organizations found in the WM (1988), it may not be wise to translate the results of this research effort directly to an actual media selection and advertising budget allocation situation. However, the management decision process, crucial to the success of any media selection and advertising budget allocation problem, could 87 be significantly aided by these results. The master spreadsheet developed and the linear programming package used are in no way intended to replace the management decision process, but rather to enhance it. The master spreadsheet file will aid in making tough problems a little easier to handle. The master spreadsheet becomes a base from which to make further decisions. For example, in the Best12files, a marketer may decide against using the full page newspaper advertisement that is a part of each Best12 file’s optimal situation. This may be a decision based on past experience or learned knowledge that a full page newspaper advertisement will not be as effective in the overall campaign. Perhaps the marketer will decide to put the money allocated for the full page into quarter page newspaper advertisements. The master spreadsheet file proves its worth as a base for aiding management decisions, because of its adaptability to quickly assess the impacts of alternatives. The master spreadsheet may also aid management decisions as a guideline. The marketer and/or management team develop what they believe is a solid advertising-allocation plan. The master spreadsheet is used as a guideline to search out areas the marketer may have overlooked. It can be used to double-check that too much or too little is not placed in one media variable or category of media. The value of this spreadsheet and Optimization process may also be demonstrated by its use in small tourism business situations. The marketer in this instance may not have the access to the personnel or resources that a state tourism and travel organization or large private company would have. The master spreadsheet file can aid significantly in this marketer’s decision-making situation. The third question to be posed in evaluating this research is: Can practical use be made of this linear programming package. WHATS BESTl? To the 88 average decision maker in a small to mid-size tourism and travel business, the concept of linear programming models may seem to be beyond contemplation. However, this research was completed, the spreadsheet files were developed. and the linear programming package was used without having an in-depth knowledge of the concepts of linear programming and without an extensive background in the mathematics associated with linear programming algorithms. With some basic study of the concept of model formulation and an idea of how linear programming works and acts, the average decision maker can make excellent use of this valuable tool. For that matter, even those involved with large organizations and businesses could benefit from the WHAT’S BEST! linear programming package. A working knowledge of Lotus 1-2-3 is very helpful. Even so, knowing a few basic commands like file retrieve, file save, print, and how to enter formulas is all that is really necessary to get started. Furthermore, with the instructional macro developed in the master spreadsheet, it becomes a simple matter of inputting the desired numbers, percentages, or budget level in to the instructional screen presented within the master spreadsheet. Thus, this tool development can be used by tourism managers with minimal computer skills. In terms of implications of this research effort, there are two main areas that can be explored. First, there are acknowledged limitations to this research effort and to using linear programming for media selection. However. the objective of this research was to demonstrate a use for the linear programming package WHAT'S BESTl, and that has been done. In consideration of the limitations. some of the major ones discussed in the literature review and reiterated here have been approached with some success. The recalculation process and the use of the WBCALC range are also important in overcoming some of these limitations. 89 A limitation to linear programming in the past has been that it cannot account for media discounts related to volume purchases. To avoid this, discounts were placed in media package buys in the instance of radio spots and radio report sponsorships. Thus. the unit cost reflects a package buy ratherthan Single per-spot or per-sponsorship cost. This was also done for direct-mail advertising variables and some of the specialty media variables like keys and bumper stickers. Another limitation is that of reduced exposure value for repeat media usage. In an attempt to approach this problem, advertising account executives were asked for input into frequency figures. This was to avoid overestimation of frequency figures. thereby adding to the reduced exposure effect. Also, by constraining each media variable and then each category, it was suggested by one account executive that this might also help in the reduced exposure effect. This ensures that not all budget funds for a given media category are allocated to just one variable. Admittedly, it does not completely overcome the problem, but there are preventative measures available to ensure a limiting of the exposure-reduction effect. A further limitation is that of the problem with fractional purchases of a media variable. The simplex method of the basic linear programming algorithm used in version 1.2 of WHAT'S BEST! will not guarantee the purchase of full pages in a magazine or complete television commercials. The optimal solution may be 3.25 one-page magazine advertisements or 7.832 15-second spot television commercials. To overcome this problem, the results of the number of media variables selected were set in a recalculation range format (WBCALC) that doesn't allow values in the range to be adjusted by WHAT'S BEST! so that they can be recalculated after the optimization process. To overcome this problem 90 completely, integer programming is necessary. It guarantees that nonfractional units are selected. WHAT'S BESTl, as indicated previously, offers the integer programming option. but compared to its maximum program limit for optimizable cells (400) or coefficients (6,400), there are only 40 integer on cells available in the version 1.2, used in this research effort. A second area to be explored in future research is that of up-to-date data. A problem in the past has been that researchers using linear programming for media selection were not privy to up-to-date data. And those at large advertising firms who may have had data that were current and were using linear program- ming models would not release crucial data for general use. This research effort in no way claims to have up-to-date unit costs, but with access to advertising account executives and the availability of relatively accurate trade papers like the Wm and the 8mm newspapers. some very workable unit cost figures were obtained. in the real world, a cooperative agreement between a tourism and travel business and a local advertising firm would be far easier to establish to obtain actual cost and exposure data. Also, in terms of updating unit costs or, for that matter, any other figure in the master spreadsheet file, the process is very simple because the spreadsheet was created to adapt to such a likelihood as changing cost values. The real advantage in using this tool is that, once the basic model is created , new data can be quickly inserted and their impact analyzed. The final area to discuss in terms of summarizing this research effort is that of recommendations from what was learned. There are five main recommen- dations that are thought to be of value in furthering research in the area of media selection by linear programming using WHATS BESTl. 91 First. more media variables need to be added. Fifty-nine media variables are used in the master spreadsheet file. This number of media variables could be increased to 75 or even 100. Consideration should be given to breaking down categories. For example, networktelevision could be broken down into variables that address special events (e.g., Super Bowl) and seasonal buys. Seasonal buys would help to overcome the limitation of advertisement scheduling in linear programming efforts. Furthermore. magazine advertisement could be broken down into color and black-and-white advertisement. Color is becoming more popular and affordable in newspaper; it should be given consideration. Transit advertising can be broken down into more media variables according to different bus systems in a regional area. Not only can the 59 variables used in the master Spreadsheet file be broken down or expanded upon, but new media variables and categories can be added. For example. trade shows, point-of-purchase displays, and co-op advertisements could be introduced. Second, figures such as reach, frequency, and unit cost can be strength- ened with continuing efforts to keep them up-to-date. Following Mug Age’e “Media Works” section is of great value in this effort. “Media Works” covers magazines, radio, and television, and may include some newspaper data. The most accurate and up-to-date figures are important to the practicality and use of the models, so it is crucial to stay on top of these. Third, a more detailed set of macros developed for the master spread- sheet file on Lotus 1-2-3 would be helpful to aid in altering the spreadsheets and manipulating data. Detailed macros could be instructional in nature or could aid in the adaptation of the master spreadsheet file to differing situations. They could select only certain media categories or variables to be used in the advertising budget allocation plan. For example, a small tourism and travel business might 92 not be able to afford television advertising. This media category could then be frozen out of the optimization process. A fourth recommendation is that media variables could be defined more closely in terms of geographic area reached, which is very important to large national tourism and travel organizations or state agencies looking to target a particular geographic market. Media variables could also be defined in terms of the demographics and characteristics of the target group they reach. For example, Vogue and We are both targeted to females, but each reaches a different segment of the population. An important part of this recom- mendation is the setting up of appropriate constraints in an effort not to go overboard with a certain media variable and to make sure that there is not too much audience duplication and that the exposure reduction effect is not overly enhanced. A fifth and final recommendation is that integer programming methods Should be used to ensure the best possible results. To implement this recommen- dation, it is necessaryto obtain the updated version of WHAT’S BESTl, however, details on its potential release are not available at this time. Other minor recommendations include using a more current version of Lotus 1-2-3, which incorporates a number of time-saving features that would be beneficial to the models, especially if more detailed macros are to be developed. Another minor recommendation is that. to speed optimization calculations, the installation of an 80287 Math Co-Processor will speed calculations by about three times. While making minor adjustments in the master spreadsheet file , time can grow long waiting for WHAT'S BEST! to recalculate large ranges of adjustable cells. 93 A further recommendation for those with little or no linear programming experience isto read Saul l. Gass’s nlll r i ' r i (1970). It is quite easy to understand, well laid out, and has informative and useful graphs and examples. As well, it has a detailed bibliography for further study. BIBLIOGRAPHY BIBLIOGRAPHY Aaker, David A., and Myers, John G. W. 3rd ed. Englewood Cliffs, N.J.: Prentice-Hall, 1987. ”Advertising Insert.” W. 19 May 1986, p. M-11. Anderson, David R. Sweeney, Dennis J.; and Williams, Thomas A. We St Paul Minn-I West Publishing Co., 1974. Bass, Frank M., and Lonsdale, Ronald T. ”An Exploration of Linear Programming in Media Selection” WWW (May 1966): 179. Bearden-Mason, Michelle. ”Arizona Seeks Image Beyond Cactus and Cowboys.” MW, 5 September 1985, p. 20. Bunn, Derek W. W. New York: John Wiley and Sons, 1982. Burkhart, A. J., and Medlik, S. Tourism: W EuluLe. 2nd ed. London: Heinemann, 1981. Charnes, A.; Cooper, W.; and Henderson, A. ' ' Emgmmjeg. New York: John Wiley and Sons. 1953. Cleaver, Joanne Y. "Industry Takes Road to Recovery in 1985.” Misuse, 22 August 1985, p. 15. Daellenbach, Hans G., and Bell, Earl J. ' ' ELQgLemmjng. Englewood Cliffs, N.J.: Prentice-Hall, 1970. "Datafiles.” W, 22 December 1986, p. 21. Davis, Kenneth R. W. 5th ed. New York: John Wiley and Sons, 1985. Dean Sandra Linville WWW Quemeee. Wilmington, Delaware: Enterprise Publishing, 1980. Endicott, R. Craig. ”The Top 200.” AmniejngAge, 20 August 1990, p . 45 . 94 95 Engel, James F., and Warshaw, Martin R. ”Allocating Advertising Dollars by Linear Programming” MW (September 1964): 42. , and Kinnear, Thomas C. W. 5th ed. Homewood, "L: Richard D. Irwin, 1983. Fisher, Christy. ”Paper's Outlook a Little Rocky.” AmnisjngAge, 20 August 1990, p. S-14. ”For the Record.” AW, 6 August 1990, p. 33. (a) ”For the Record.” MM, 27 August 1990, p. 35. (b) Foxhall, Gordon. W. Totowa, N.J.: Frank Cass and Co., 1985. Frey, Albert Wesley. W. New York: Ronald Press Company, 1955. Gass, Saul l. W- New York: McGraw-Hill Book Co., 1970. _- ° ' ' . 3rd ed. New York: McGraw-Hill Book Co., 1969. Gensch, D. H. ”Computer Models in Advertising Media Selection. W WWovemberwGB): 414. Graham, Irvin. Wm. 2nd ed. New York: Fairchild Publications, 1969. Hadley, G. W. Reading, Mass: Addison-Wesley, Hodge, Bartow, and Clements, James P. W. Englewood Cliffs, N.J.: Prentice-Hall, 1986. Horton, Cleveland. ”California Seeks Tourism Rush.” Wm, 18 March 1985, p. 20. Howard, Dennis R., and Crompton, John L. ' ° ' . Dubuque, Iowa: Wm. C. Brown Co., 1980. Hughes, Ann J., and Grawiog, Dennis E. mi 93W. Reading, Mass.: Addison- -Wesley, 1973. 96 impact (Lesco Corporation), 1990, pp. 42, 52, 66. Jarke, Matthias. tn o: u . ._ it..f ' :o .. in- -m or M. New York: John Wiley and Sons,1986. Kaufmann, Louis C. W. 2nd ed. San Diego: Harcourt Brace Jovanovich, 1987. Kelly, John R. W. New York: John Wiley and Sons, 1985. Lapin. Lawrence L. W New York: Harcourt Brace Jovanovich, 1976. ”Last Minute News.” W, 6 August 1990, p. 8. (a) ”Last Minute News.” W. 20 August 1990, p. a. (b) ”Last Minute News.” AdvenisingAgg, 27 August 1990, p. 8. (c) Lee. Sang M. UneaLQmimizatimJoLManaoemem New York: Mason Charter Publishers, 1976. Lilien, Gary L., and Kotler, Philip. W. New York: Harper and Row, 1983. Loomba, Narendra Paul. ' m ‘ ° r’ 2nd ed. New York: Macmillan, 1976. Martin, Francis F. nggutgLMgggiingjngfimuiaiign. New York: John Wiley and Sons, 1968. Mayo, Edward J., Jr., and Jarvis, Lance P. Tne Psyghglggy 91 Leisure Iravel. Boston: CBI Publishing, 1981. "Media Works.” AdvenisingAgg, 1 June 1987, p. 78. (a) "Media Works." mm, 6 March 1989, p. 60. (b) ”Media Works.” W 7 August 1989, p. 46. (c) ”Media Works.” W, 30 July 1990, p. 32. (a) ”Media Works.” AdvenisingAge, 6 August 1990, p. 25. (b) 97 Meyers, Janet. ”South Carolina Tests Ads in Cleveland.” AppenjsingAgg, 2 December 1985, p. 85. . ”News.” Amnisingjsgp, 20 August 1990, p. 16. ”Newswatch.” AdvertisingAgg, 30 July 1990, p. 12. (a) ”Newswatch.” AgypnipingAge, 20 August 1990, p. 12. (b) Nylen. David W. Advenisinq: WWW Cincinnati: South-Western Publishing, 1975. Palmer, Kenneth H. Emgiamming. New YOIk: John Wiley and Sons, 1984. EELSQQaLIQIJQfl. New York: Artistic Greetings, 1990. Bielly. Robert T. W. Wheaton. |||.: Merton House Publishing, 1980. Ritchie, J. R. Brent, and Goeldner, Charles R. lravel, Tgpr risn], an ng W. New York: John Wiley and Sons, 1987. We. Mequon, Wisc.: Sales Guide, 1990. Salkin, Gerald, and Kornbluth, Jonathon. flagging. London: Haymarket Publishing, n.d. Savage, Sam L. Ine ABC's pi thimizaiipn using WHATS 8531! Chicago: General Optimization, 1985. Schewe, Charles I., and Smith, Reuben M. ' Applications. 2nd ed. New York: McGraw-Hill Book Co., 1983. Schmoll, G. A. W. London: Tourism International Press, 1977. Schniederjans, Marc J. LinganfigaLEmgLaimnjng. Princeton, N.J.: Petrocell Books, 1984. Snyder, Jesse. ”Michigan Touts Financial Strength to Tourists and, Business." MW 28 November 1983, p. 86. ' .Wilmette, lll.: NationalRegister Publishing 00., May 1988. 98 Starr, Nona S. Marketingjgfihplmflngustni. Wellesley, Mass.: lCTA CTC Certification Text Series, 1984. Stasch, Stanley F. ”Linear Programming and Space-Time Considerations in Media Selection. WW (December 1965): 40. Throsby, C. D. Wing. New York: Random House, 1970. US. Travel Data Center. W. Washington, DC: Travel Industry Association of America, 1989. Wahab, Salah; Crampon, L. J.; and Rothfield, L. M. Wm. London: Tourism International Press, 1976. Williams. H. P. ModeLBuiIdinanMatnematicaLEmgramming 2nd ed- New York: John Wiley and Sons, 1985. Wu. Nesa. and Coppins. Richard. W New York: McGraw-Hill, 1981. APPENDICES APPENDIX A BEST 12.A SPREADSHEET F ILE; as it represents the optimal solution output file for a $12 million dollar budget and the budget allocation strategy A (mixed media strategy). 99 100 APPENDIX A — Best12.A, columns 1 thru 6, rows 1 thru 3 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's 3 MEDIA VARIABLES TELEVISION NET 15's 4,850 1 .0 4,850 175.00 27.71 NET 30's 5,575 1.0 5,575 225.00 24.78 CAB 15’s 3.335 1.0 3,335 1 10.00 30.32 CAB 30's 4,275 1.0 4,275 155.00 27.58 L00 1 5'5 545 1 .0 545 30.00 18.1 7 L00 30's 880 1.0 880 45.00 19.56 TOTAL 1 9,460 1 9,460 740.00 1 27.37 AVG. 3,243 3,243 123.33 21 .23 NEWSPAPER FULL PG. 635 2.0 1,270 60.00 10.58 HALF PG. 425 2.0 850 47.00 9.04 6X4“ PG. 165 2.0 330 22.00 7.50 8X4“ PG. 190 2.0 380 28.50 6.67 4 PG. INS. 625 2.0 1,250 98.00 6.38 6 PG. INS. 630 2.0 1,260 109.50 5.75 TOTAL 2,670 5,340 365.00 45.92 AVG. 445 890 60.83 7.65 WEEK MG. FR COVER 1,150 3.0 345 100.00 11.50 BK COVER 905 3.0 2,715 85.00 1 0.65 2nd PAGE 775 3.0 2,325 70. 00 11.07 3rd PAGE 745 3.0 2,235 60. 00 12.42 EXT. FLAP 950 1.0 950 85.00 11.18 TWO PAGE 865 2.0 1,730 75.00 11.53 ONE PAGE 610 2.0 1,220 55.00 1 1.09 TOTAL 6,000 14,625 530.00 79.44 AVG. 857 2,089 75.71 1 1.35 101 APPENIX A — Best12.A, columns 7 thru 12, rows 1 thru 3 of computer printout #OF UNITS TOTAL COST SELECTED MEDIA VARIABLES TELEVISION NET 15's 2.86 NET 30's 0.00 CAB 15's 22.73 CAB 30's 0.00 LOC 15's 0.00 LOC 30's 0.00 TOTAL AVG. NEWSPAPER FULL PG. 8.33 HALF PG. 10.64 6X4” PG. 5.68 8X4” PG. 0.00 4 PG. INS. 0.00 6 PG. INS. 0.00 TOTAL AVG. WEEK MG. FR COVER 5.00 BK COVER 5.88 2nd PAGE 7.14 3rd PAGE 8.33 EXT. FLAP 0.00 TWO PAGE 0.00 ONE PAGE 0.00 TOTAL AVG. OF UN TTS SELECTED $1 ,000's 500.00 0. 00 2500.00 0.00 0.00 0.00 3000.00 500.00 500. 00 500. 00 1 25. 00 0.00 0.00 0.00 1 125.00 187.50 500.00 500.00 500.00 500.00 0.00 0.00 0.00 2000.00 285.71 CONSTRAINTS 5 LIMIT 2500 2500 2500 2500 1 000 AAAAAA 500 500 500 500 500 500 AAAAAA < 1125 500 500 500 500 500 500 AAAAAAA < 2250 1000 ~ 3000 NON- NEGATIVE 2.500 1 .500 1 .000 1 .000 375 500 500 500 0000 500 500 500 250 TOTAL IMPRSS. 1 3,857 0 75,795 0 O 0 89,652 1 4,942 10,583 9,043 1,875 21.501 3,584 17,250 15,971 16,607 18,625 0 0 0 68,453 9,779 102 APPENDIX A — Best12.A, columns13 thru18, rows 1 thru 3 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE TELEVISION NET 15's 3 525.00 < 2500 1,975 14,550 NET 30's 0 0.00 < 2500 2,500 0 CAB 15's 22 2420.00 < 2500 80 73,370 CAB 30's 0 0.00 < 2500 1,500 0 LOC 15's 0 0.00 < 1000 1,000 0 LOC 30's 0 0.00 < 1000 1,000 0 TOTAL 2945.00 < 3000 55 87,920 AVG. 490.83 1 4,653 NEWSPAPER , FULL PG. 8 480.00 < 500 20 10,160 HALF PG. 10 470.00 < 500 30 8,500 6X4“ PG. 5 110.00 < 500 390 1,650 8X4“ PG. 0 0.00 < 500 500 0 4 PG. INS. 0 0.00 < 500 500 0 6 PG. INS. 0 0.00 < 500 500 0 TOTAL 1060.00 < 1 125 0 20,31 0 AVG. 176.67 3,385 WEEK MG. FR COVER 5 500.00 < 500 0 17,250 BK COVER 5 425.00 < 500 75 13,575 2nd PAGE 7 490.00 < 500 10 16,275 3rd PAGE 8 480.00 < 500 20 17,880 EXT. FLAP O 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 500 0 ONE PAGE 0 0.00 < 500 500 0 TOTAL 1895.00 < 2250 355 64,980 AVG. 270.71 9,283 103 APPENDIX A — Best12.A, columns 1 thru 6, rows 4 thru 6 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's 3 MEDIA VARIABLES MNTH. MG. FR COVER 1,005 3.0 3,015 1 15.00 8.74 BK COVER 885 3.0 2,655 98.00 9.03 2nd PAGE 665 3.0 1,995 77.00 8.64 3rd PAGE 580 3.0 1,740 68.00 8.53 EXT.F LAP 825 1 .0 845 95.00 8.68 TWO PAGE 605 2.0 1,210 75.00 8.07 ONE PAGE 535 2.0 1 ,070 38.50 13.90 TOTAL 5,1 00 12,51 0 566.50 65.58 AVG. 729 1 ,787 80.93 9.37 BUSN. MG. FR COVER 775 2.0 1,550 92.00 8.42 BK COVER 725 2.0 1,450 83.00 8.73 2nd PAGE 665 2.0 1,330 78.00 8.53 3rd PAGE 660 2.0 1,320 72.00 9.17 TWO PAGE 685 2.0 1,370 58.50 11.71 ONE PAGE 595 2.0 1,190 33.00 18.03 TOTAL 4,105 8,210 416.50 64.59 AVG. 684 1 .368 69.42 1 0.77 CONS. MG. FR COVER 445 2.0 890 82. 00 5.43 BK COVER 400 2.0 800 76.50 5.23 TWO PAGE 355 2.0 710 34.00 1 0.44 ONE PAGE 310 2.0 620 18.50 1 6.76 4PG. INS. 380 2.0 760 65.00 5.85 TOTAL 1 .890 3,780 276.00 43.70 AVG. 378 ' 756 55.20 8.74 104 APPENDIX A— Best12.A, columns 7 thru 12, rows 4 thru 6 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATNE MNTH. MG. FR COVER 4.35 500.00 < 500 0 13,109 BK COVER 5.10 500.00 < 500 0 13,546 2nd PAGE 0.00 0.00 < 500 500 0 3rd PAGE 0.00 0.00 < 500 500 O EXT.FLAP 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 500 0 ONE PAGE 12.99 500.00 < 500 0 13,896 TOTAL 1500.00 < 2250 750 40,551 AVG. 214.29 5,793 BUSN. MG. FR COVER 0.00 0.00 < 500 500 0 BK COVER 0.00 0.00 < 500 500 0 2nd PAGE 0.00 0.00 < 500 500 0 3rd PAGE 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 300 0 ONE PAGE 15.15 500.00 < 500 0 18,030 TOTAL 500.00 < 2250 1,750 18,030 AVG. 83.33 3,005 CONS. MG. FR COVER 0.00 0.00 < 500 500 0 BK COVER 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 500 0 ONE PAGE 27.03 500.00 < 500 0 16,757 4PG. INS. 0.00 0.00 < 500 500 0 TOTAL 500.00 < 2250 1,750 16,757 AVG. 100.00 3,351 105 APPENDIX A — Best12.A, columns 13 thru18, rows 4 thru 6 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST 5 NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE MNTH. MG. FR COVER 4 460.00 < 500 40 12,060 BK COVER 5 490.00 < 500 10 13,275 20d PAGE 0 0.00 < 500 500 0 3rd PAGE 0 0.00 < 500 500 O EXT .FLAP 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 500 0 ONE PAGE 12 462.00 < 500 38 12,840 TOTAL 1412.00 < 2250 838 38,175 AVG. 201.71 5,454 BUSN. MG. FR COVER 0 0.00 < 500 500 0 BK COVER 0 0.00 < 500 500 0 2nd PAGE 0 0.00 < 500 500 0 3rd PAGE 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 300 0 ONE PAGE 15 495.00 < 500 5 17,850 TOTAL 495.00 < 2250 1,755 17,850 AVG. 82.50 2,975 CONS. MG. FR COVER 0 0.00 < 500 500 0 BK COVER 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 500 0 ONE PAGE 27 499.50 < 500 1 16,740 4PG. INS. 0 0.00 < 500 500 0 TOTAL 499.50 < 2250 1,751 16,740 AVG. 99.90 3,348 106 APPENDIX A — Best12.A, columns 1 thru 6, rows 7 thru10 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's $ MEDIA VARIABLES RADIO SPOT 1 5'5 58 4.0 232 1 8. 00 3.22 SPOT 30's 73 4.0 292 32. 50 2.25 SP. NEWS 44 6.0 264 9.00 4.89 SP. REPORT 28 6.0 1 68 12.75 2.20 TOTAL 203 956 72. 25 1 2.55 AVG. 51 239 18.06 3.14 OUTDOOR 6M LEASE 12X25 6 3.0 18 2.10 2.86 14X48 ' 12 5.0 60 3.15 3.81 ROTARY PLAN ‘ 12X25 12 3.0 36 15.60 0.77 14X48 18 5.0 90 17.40 1.03 TOTAL 48 204 38.25 6.47 AVG. 12 51 9.56 2.12 TRANSIT BUS CARD 45 4.0 180 22.25 2.02 POSTERS 65 2.0 1 30 36. 50 1 .78 TOTAL 1 10 31 0 58. 75 3.80 AVG. 55 155 29.38 1 .90 DIRECT MAIL LEAFLETS 5 1 .0 5 0.60 8.33 BROCHURE 25 3.0 75 55. 75 0.45 NEWSLETTER 12 1.0 12 2.25 5.33 TOTAL 42 92 58. 60 ‘ 1 4.1 2 AVG. 14 31 19.53 4.71 107 APPENDIX A — Best12.A, columns 7 thru 12, rows 7 thru 10 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1,000's LIMIT NEGATIVE RADIO SPOT 15's 4.1 7 75.00 < 150 75 967 SPOT 30's 0.00 0.00 < 150 150 0 SP. NEWS 16.67 150.00 < 150 0 4,400 SP. REPORT 1 1.76 150.00 < 150 0 1,976 TOTAL 375.00 < 1 125 ' 750 7,343 AVG. 93.75 1,836 OUTDOOR 6M LEASE 12X25 238.10 500.00 < 500 0 4,286 14X48 1 58.73 500.00 < 500 0 9,524 ROTARY PLAN 12X25 0.00 0.00 < 500 500 0 14X48 0.00 0.00 < 500 500 0 TOTAL 1000.00 < 1 125 125 13,810 AVG. 250.00 3,453 TRANSIT BUS CARD 20.22 450.00 < 600 150 3,640 POSTERS 0.00 0.00 < 600 600 0 TOTAL 450.00 < 1 125 675 3,640 AVG. 225.00 1 .820 DIRECT MAIL LEAFLETS 83.33 50.00 < 50 0 417 BROCHURE 0.00 0.00 < 600 600 0 NEWSLETTER 0.00 0.00 < 50 50 0 TOTAL 50.00 < 1 125 1,075 417 AVG. 1 6.67 139 108 APPENDIX A— Bes112.A, columns13 thru18, rows 7 thru10 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST 3 NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE RADIO SPOT 15's 4 72.00 < 150 78 928 SPOT 30's 0 0.00 < 150 150 0 SP. NEws 17 144.00 < 150 6 4,224 SP. REPORT 12 140.25 < 150 10 1,848 TOTAL 356.25 < 1125 769 7,000 AVG. 89.06 1 ,750 OUTDOOR 6M LEASE 12X25 238 499.80 < 500 0 4,284 14X48 158 497.70 < 500 2 9,480 ROTARY PLAN 12X25 0 0.00 < 500 500 0 14X48 0 0.00 < 500 500 0 TOTAL 997.50 < 1125 128 13,764 AVG. 249.38 3,441 TRANSIT BUS CARD 20 445.00 < 600 155 3,600 POSTERS 0 0.00 < 600 600 0 TOTAL 445.00 < 1125 680 3,600 AVG. 222.50 1,800 DIRECT MAIL LEAFLETS 83 49.80 < 50 0 415 BROCHURE 0 0.00 < 600 600 0 _ NEWSLETTER 0 0.00 < 50 50 - 0 TOTAL 49.80 < 1125 1,075 415 AVG. _ 1 6.60 138 109 APPENDIX A — Best12.A, columns 1 thru 6, row11 of computer printout REACH FREQ. IMPRSS. UNIT COST 1 ,000's 1 ,000's $1 000's MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 1 00 2.0 200 520.00 CATALOGUE 20 1.0 20 1 1.50 BANNERS 1 1.0 1 11.85 MAPS 25 2.0 50 88. 75 IMPRINTS T-SHIRTS 1 4.0 4 5.00 B. STICK 5 2.0 10 3.70 POSTERS 5 2.0 1 0 1 0.00 MENUS 1 1.0 1 4.35 KEYS 24 1.0 24 9.50 TOTAL 232 722 71 9.65 AVG. 26 80 79. 96 C.P.M. $ 0.19 1.74 0.08 0.28 0.20 1.35 0.50 0.23 2.53 7.11 0.79 APPENDIX A — Best12.A, columns 7 thru 12, row 11 of computer printout #OF UNITS TOTAL COST SELECTED OF UNITS SELECTED MEDIA VARIABLES $1 ,000's SPECIAL MEDIA DIRECTORY 0.00 0.00 CATALOGUE 6.96 80.00 BANNERS 0.00 0.00 MAPS 1 1.49 1020.00 IMPRINTS T-SHIRTS 20.00 1 00.00 B. STICK 27.03 100.00 POSTERS 1 0.00 1 00.00 MENUS 0.00 0.00 KEYS 10.53 100.00 TOTAL 1 500.00 AVG. 1 66.67 BUDGET SPENT $1 ,000's 1 2000.00 110 CONSTRAINTS $ NON- LIMIT NEGATIVE < 2400 2,400 < 80 0 < 80 80 < 1200 180 < 100 0 < 100 0 < 100 0 < 100 100 < 100 0 < 1500 0 BUDGET TOTAL LIMIT NET $1 ,000's BUDGET < 12,000 0 TOTAL IMPRSS. 139 575 80 270 1 00 253 1,41 7 157 TOTAL IMPRSS. 1 ,000's 281,570 111 APPENDIX A — Best12.A, columns 13 thru 18, row 11 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST 5 NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 0 0.00 < 2400 2,400 0 CATALOGUE 6 69.00 < 80 1 1 120 BANNERS 0 0.00 < 80 80 0 MAPS 1 1 976.25 < 1200 224 550 IMPRINTS T-SHIRTS 20 1 00.00 < 1 00 0 80 B. STICK 27 99.90 < 100 0 270 POSTERS 1 0 100.00 < 100 0 1 00 MENUS 0 0.00 < 100 100 O KEYS 1 1 95.00 < 100 5 240 TOTAL 1440.15 < 1 500 60 1 .360 AVG. 1 60.02 1 51 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1 ,000's $1 ,000's BUDGET 1,000‘s 11595.20 < 12,000 405 272,1 14 APPENDIX B BEST 12.8 SPREADSHEET FILE; as it represents the optimal solution output file , for a $12 million dollar budget and the budget allocation strategy 8 (high impression media generating strategy). 112 113 APPENDIX B — Best12.B, columns 1 thru 6, rows 1 thru 3 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000‘s 1 ,000's $1 000's 3 MEDIA VARIABLES TELEVISION NET 15's 4,850 1 .0 4,850 175.00 27.71 NET 30's 5,575 1.0 5,575 225.00 24.78 CAB 15's 3,335 1.0 3,335 1 10.00 30.32 CAB 30's 4,275 1.0 4,275 155.00 27.58 LOC 15's 545 1.0 545 30.00 18.17 LOC 30's 880 1.0 880 45.00 19.56 TOTAL 1 9,460 1 9,460 740.00 127.37 AVG. 3,243 3,243 123.33 21 .23 NEWSPAPER FULL PG. 635 2.0 1,270 60.00 10.58 HALF PG. 425 2.0 850 47.00 9.04 6X4” PG. 165 2.0 330 22.00 7.50 8X4“ PG. 190 2.0 380 28.50 6.67 4 PG. INS. 625 2.0 1,250 98.00 6.38 6 PG. INS. 630 2.0 1,260 109.50 5.75 TOTAL 2,670 5,340 365.00 45.92 AVG. 445 890 60.83 7.65 WEEK MG. FR COVER 1,150 3.0 345 100.00 11.50 BK COVER 905 3.0 2,71 5 85. 00 1 0.65 2nd PAGE 775 3.0 2,325 70.00 11.07 3rd PAGE 745 3.0 2,235 60.00 12.42 EXT. FLAP 950 1.0 950 85.00 1 1.18 TWO PAGE 865 2.0 1,730 75.00 11.53 ONE PAGE 610 2.0 1,220 55.00 1 1.09 TOTAL 6,000 14,625 530.00 79.44 AVG. 857 2,089 75.71 1 1.35 114 APPENDIX B — Best12.B, columns 7 thru 12, rows 1 thru 3 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED s NON- MEDIA VARIABLES $1,000's LIMIT NEGATIVE TELEVISION NET 15's 14.29 2500.00 < 2500 0 69,286 NET 30's 0.00 0.00 < 2500 2.500 0 CAB 15's 22.73 2500.00 < 2500 0 75,795 CAB 30's 6.45 1000.00 < 2500 1,500 27,581 LOC 15's 0.00 0.00 < 1000 1,000 0 L00 30's 0.00 0.00 < 1000 1,000 0 TOTAL 6000.00 < 6000 0 172,662 AVG. 1000.00 28,777 NEWSPAPER FULL PG. 8.33 500.00 < 500 0 10,583 HALF PG. 10.64 500.00 < 500 0 9,043 6X4” PG. 22.73 500.00 < 500 0 7,500 8X4” PG. 10.53 300.00 < 500 200 4,000 4 PG. INS. 0.00 0.00 < 500 500 0 6 PG. INS. 0.00 0.00 < 500 500 0 TOTAL 1800.00 < 1800 0 31.126 AVG. 300.00 5,188 WEEK MG. FR COVER 5.00 500.00 < 500 0 17,250 BK COVER 0.00 0.00 < 500 500 0 2nd PAGE 2.86 200.00 < 500 300 6,643 3rd PAGE 8.33 500.00 < 500 0 18,625 EXT. FLAP 0.00 0.00 < 500 500 0 Two PAGE 0.00 0.00 < 500 500 0 ONE PAGE 0.00 0.00 < 500 500 0 ' TOTAL 1200.00 < 1200 0 42,518 AVG. 171.43 6.074 115 APPENDIX B — Best12.B, columns 13 thru18, rows 1 thru 3 01 computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE TELEVISION NET 15’s 14 2450.00 < 2500 50 67,900 NET 30's 0 0.00 < 2500 2,500 0 CAB 15's 23 2420.00 < 2500 80 73,370 CAB 30's 6 930.00 < 2500 1,570 25,650 LOC 15’s 0 0.00 < 1000 1,000 0 LOC 30's 0 0.00 < 1000 1,000 0 TOTAL 5800.00 < 6000 0 166,920 AVG. 966.67 27,820 NEWSPAPER FULL PG. 8 480.00 < 500 20 10,160 HALF PG. 10 470.00 < 500 30 8,500 6X4” PG. 22 484.00 < 500 16 7,260 8X4“ PG. 10 285.00 < 500 215 3,800 4 PG. INS. 0 0.00 < 500 500 0 6 PG. INS. 0 0.00 < 500 500 0 TOTAL 1 719.00 < 1800 81 29,720 AVG. 286.50 4,953 WEEK MG. FR COVER 5 500.00 < 500 0 17,250 BK COVER 0 0.00 < 500 500 0 2nd PAGE 3 210.00 < 500 290 6,975 3rd PAGE 8 480.00 < 500 20 17,880 EXT. FLAP 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 500 0 ONE PAGE 0 0.00 < 500 500 0 TOTAL 1 190.00 < 1200 1 0 42,1 05 AVG. 170.00 6,015 116 APPENDIX B — Best12.B, columns 1 thru 6, rows 4 thru 6 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's 5 MEDIA VARIABLES MNTH. MG. FR COVER 1,005 3.0 3,015 1 15.00 8.74 BK COVER 885 3.0 2,655 98.00 9.03 2nd PAGE 665 3.0 1,995 77.00 8.64 3rd PAGE 580 3.0 1,740 68.00 8.53 EXT.F LAP 825 1.0 845 95.00 8.68 TWO PAGE 605 2.0 1,210 75.00 8.07 ONE PAGE 535 2.0 1,070 38.50 13.90 TOTAL 5,100 12,510 566.50 65.58 AVG. 729 1 ,787 80.93 9.37 BUSN. MG. FR COVER 775 2.0 1,550 92.00 8.42 BK COVER 725 2.0 1,450 83.00 8.73 2nd PAGE 665 2.0 1.330 78.00 8.53 3rd PAGE 660 2.0 1,320 72. 00 9.17 TWO PAGE 685 2.0 1,370 58.50 11.71 ONE PAGE 595 2.0 1 ,1 90 33.00 18.03 TOTAL 4,105 8,210 416.50 64.59 AVG. 684 1 .368 69.42 1 0.77 CONS. MG. FR COVER 445 2.0 890 82.00 5.43 BK COVER 400 2.0 800 76.50 5.23 TWO PAGE 355 2.0 710 34.00 10.44 ONE PAGE 310 2.0 620 18.50 16.76 4PG. INS. 380 2.0 760 65.00 5.85 TOTAL 1 .890 3,780 276.00 43.70 AVG. 378 756 55.20 8.74 117 APPENDIX B — Best12.B, columns 7 thru 12, rows 4 thru 6 of computer printout #OF UN ITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE MNTH. MG. FR COVER 0.00 0.00 < 500 500 0 BK COVER 0.00 0.00 < 500 500 0 2nd PAGE 0.00 0.00 < 500 500 0 3rd PAGE 0.00 0.00 < 500 500 0 EXT.FLAP 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 500 0 ONE PAGE 5.19 200.00 < 500 300 5,558 TOTAL 200.00 < 1200 1,000 5.558 AVG. 28.57 794 BUSN. MG. FR COVER 0.00 0.00 < 500 500 0 BK COVER 0.00 0.00 < 500 500 0 2nd PAGE 0.00 0.00 < 500 500 0 3rd PAGE 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 300 0 ONE PAGE 15.15 500.00 < 500 0 18,030 TOTAL 500.00 < 1200 700 18,030 AVG. 83.33 3,005 CONS. MG. FR COVER 0.00 0.00 < 500 500 0 BK COVER 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 500 0 ONE PAGE 27.03 500.00 < 500 0 16,757 4PG. INS. 0.00 0.00 < 500 500 0 TOTAL 500.00 < 1200 700 16,757 AVG. 1 00.00 3,351 118 APPENDIX B — Best12.B, columns 13 thru18, rows 4 thru 6 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE MNTH. MG. FR COVER 0 0.00 < 500 500 0 BK COVER 0 0.00 < 500 500 0 2nd PAGE 0 0.00 < 500 500 0 3rd PAGE 0 0.00 < 500 500 0 EXT .FLAP 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 500 0 ONE PAGE 5 192.50 < 500 308 5,350 TOTAL 192.50 < 1200 1,008 5,350 AVG. 27.50 764 BUSN. MG. FR COVER 0 0.00 < 500 500 0 BK COVER 0 0.00 < 500 500 0 2nd PAGE 0 0.00 < 500 500 0 3rd PAGE 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 300 0 ONE PAGE 15 495.00 < 500 5 17,850 TOTAL 495.00 < 1 200 705 1 7,850 AVG. 82.50 2,975 CONS. MG. FR COVER 0 0.00 < 500 500 0 BK COVER 0 0.00 <, 500 500 0 TWO PAGE 0 0.00 < 500 500 0 ONE PAGE 27 499.50 < 500 1 16,740 4PG. INS. 0 0.00 < 500 500 0 TOTAL 499.50 < 1200 701 16,740 AVG. 99.90 3.348 119 APPENDIX B— Bestt2.B, columns 1 thru 6, rows 7 thru10 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's 3 MEDIA VARIABLES RADIO SPOT 15's 58 4.0 232 18.00 3.22 SPOT 30's 73 4.0 292 32.50 2.25 SP. NEWS 44 6.0 264 9.00 4.89 SP. REPORT 28 6.0 168 12. 75 2.20 TOTAL 203 956 72.25 12.55 AVG. 51 239 18.06 3.14 OUTDOOR 6M LEASE 12X25 6 3.0 18 2.10 2.86 14X48 12 5.0 60 3.15 3.81 ROTARY PLAN 12X25 12 3.0 36 15.60 0.77 14X48 18 5.0 90 17.40 1.03 TOTAL 48 204 38.26 6.47 AVG. 12 51 9.56 2.12 TRANSIT BUS CARD 45 4.0 180 22.25 2.02 POSTERS 65 2.0 130 36.50 1.78 TOTAL 110 310 58.75 3.80 AVG. 55 155 29.38 1.90 DIRECT MAIL LEAFLETS 5 1.0 s 0.60 8.33 BROCHURE 25 3.0 75 55. 75 0.45 NEWSLETTER 12 1.0 12 2.25 5.33 TOTAL 42 92 58.60 14.12 AVG. 14 31 19.53 4.71 120 APPENDIX B — Best12.B, columns 7 thru 12, rows 7 thru 10 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE RADIO SPOT 15's 8.33 150.00 < 150 O 1.933 SPOT 30's 4.62 150.00 < 150 0 1.348 SP. NEWS 16.67 150.00 < 150 0 4,400 SP. REPORT 1 1.76 150.00 < 150 0 1,976 TOTAL 600.00 < 1800 1,200 9,657 AVG. 1 50.00 2,414 OUTDOOR 6M LEASE 12X25 0.00 0.00 < 500 500 0 14X48 142.86 450.00 < 500 50 8,571 ROTARY PLAN 12X25 0.00 0.00 < 500 500 0 14X48 0.00 0.00 < 500 500 0 TOTAL 450.00 < 450 0 8,571 AVG. 1 12.50 2,143 TRANSIT BUS CARD 4.49 100.00 < 600 500 809 POSTERS 0.00 0.00 < 600 600 0 TOTAL 100.00 < 450 350 809 AVG. 50.00 405 DIRECT MAIL LEAFLETS 83.33 50.00 < 50 0 417 BROCHURE 0.00 0.00 < 600 600 0 NEWSLETTER 0.00 0.00 < 50 50 0 TOTAL 50.00 < 450 400 417 AVG. 1 6.67 1 39 121 APPENDIX B -— Best12.B, columns13 thru18, rows 7 thru10 01 computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE RADIO SPOT 15's 8 144.00 < 150 6 1,856 SPOT 30's 4 130.00 < 150 20 1,168 SP. NEWS 16 144.00 < 150 6 4,224 SP. REPORT 1 1 140.25 < 150 10 1,848 TOTAL 558.25 < 1800 1,242 9,096 AVG. 139.56 2,274 OUTDOOR 6M LEASE 12X25 0 0.00 < 500 500 0 1 4X48 1 43 450.45 < 500 50 8,580 ROTARY PLAN 12X25 0 0.00 < 500 500 0 14X48 0 0.00 < 500 500 0 TOTAL 450.45 < 450 0 8,580 AVG. 112.61 2,145 TRANSIT BUS CARD 4 89.00 < 600 511 720 POSTERS O 0.00 < 600 600 0 TOTAL 89.00 < 450 361 720 AVG. 44.50 360 DIRECT MAIL LEAFLETS 83 49.80 < 50 0 415 BROCHURE 0 0.00 < 600 600 0 NEWSLETTER 0 0.00 < 50 50 0 TOTAL 49.80 < 450 400 415 AVG. 1 6.60 1 38 122 APPENDIX B— Best12.B, columns 1 thru 6, row 11 of computer printout REACH FREQ. IMPRSS. UNIT COST 1 ,000's 1 ,000's $1 000's MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 1 00 2.0 200 520.00 CATALOGUE 20 1.0 20 1 1.50 BANNERS 1 1.0 1 11.85 MAPS 25 2.0 50 88. 75 IMPRINTS T-SHIRTS 1 4.0 4 5.00 B. STICK 5 2.0 10 3.70 POSTERS 5 2.0 1 0 1 0. 00 MENUS 1 1.0 1 4.35 KEYS 24 1.0 24 9.50 TOTAL 232 722 71 9.65 AVG. 26 80 79. 96 C.P.M. S 0.1 9 1 .74 0.08 0.28 0.20 1 .35 0.50 0.23 2.53 7.11 0.79 123 APPENDIX B— Best12.B, columns 7 thru 12, row 11 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATNE SPECIAL MEDIA DIRECTORY 0.00 0.00 < 2400 2,400 0 CATALOGUE 6.96 80.00 < 80 0 139 BANNERS 0.00 0.00 < 80 80 0 MAPS 1 .35 120.00 < 1200 1,080 68 IMPRINTS T-SHIRTS 20.00 100.00 < 100 0 80 B. STICK 27.03 100.00 < 100 0 270 POSTERS 10.00 100.00 < 100 0 100 MENUS 0.00 0.00 < 100 100 0 KEYS 10.53 100.00 < 100 0 253 TOTAL 600.00 < 600 0 910 AVG. 66.67 1 01 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1 ,000's $1 ,000's BUDGET 1 ,000's 12000.00 < 12,000 0 307,015 124 APPENDIX B — Best12.B, columns 13 thru 18, row 11 01 computer printout #OF UNITS TOTAL @INTEGER @INT. RECALC. UNIT COST MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 0 0.00 CATALOGUE 6 69.00 BANNERS 0 0.00 MAPS 1 88.75 IMPRINTS T-SHIRTS 20 100.00 B. STICK 27 99.90 POSTERS ' 1 0 100.00 MENUS 0 0.00 KEYS 1 0 95.00 TOTAL 552.65 AVG. 61.41 BUDGET SPENT $1 ,000's 11596.15 CONSTRAINTS $ NON- LIMIT NEGATIVE < 2400 2,400 < 80 1 1 < 80 80 < 1200 1.1 1 1 < 100 0 < 100 0 < 100 0 < 1 00 1 00 < 100 5 < 600 47 BUDGET TOTAL LIMIT NET $1 ,000's BUDGET < 12,000 404 TOTAL RECALC. IMPRSS. 1 20 50 80 270 1 00 240 860 96 TOTAL IMPRSS. 1 ,000's 298,356 APPENDIX C BEST 120 SPREADSHEET FILE; as it represents the optimal solution output file for a $12 million dollar budget and the budget allocation strategy C (low impression media generating strategy). 125 126 APPENDIX C — Best12.C, columns 1 thru 6, rows 1 thru 3 of computer printout REACH FREQ. IMPRSS. 1,000's 1,000's MEDIA VARIABLES TELEVISION NET 15's 4,850 1.0 4,850 NET 308 5,575 1.0 5.575 CAB 15's 3,335 1.0 3,335 CAB 30's 4,275 1.0 4,275 LOC 15's 545 1.0 545 Loc 30's 880 1.0 880 TOTAL 19,460 19,460 AVG. 3.243 3,243 NEWSPAPER . FULL PG. 635 2.0 1,270 HALF PG. 425 2.0 850 6X4” PG. 165 2.0 330 8X4” PG. 190 20 380 4 PG. INS. 625 2.0 1.250 6 PG. INS. 630 2.0 1,260 TOTAL 2.670 5,340 AVG. 445 890 WEEK MG. FR COVER 1,150 3.0 345 BK COVER 905 3.0 2,715 2nd PAGE 775 3.0 2,325 3rd PAGE 745 3.0 2,235 EXT. FLAP 950 1.0 950 TWO PAGE 865 2.0 1,730 ONE PAGE 610 2.0 1,220 TOTAL 6,000 14,625 AVG. 857 2,089 UNIT COST $1 000's 175.00 225.00 1 10.00 155.00 30.00 45.00 740.00 1 23.33 60.00 47. 00 22. 00 28.50 98.00 1 09.50 365.00 60.83 1 00.00 85. 00 70. 00 60. 00 85.00 75.00 55.00 530.00 75.71 C.P.M. $ 27.71 24.78 30.32 27.58 18.1 7 19.56 1 27.37 21 .23 10.58 9.04 7.50 6.67 6.38 5.75 45.92 7.65 11.50 10.65 11.07 12.42 11.18 11.53 11.09 79.44 1 1 .35 127 APPENDIX C — Best12.C, columns 7 thru 12, rows 1 thru 3 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE TELEVISION NET 15's 0.00 0.00 < 2500 2,500 NET 30's 0.00 0.00 < 2500 2,500 0 CAB 15's 10.91 1200.00 < 2500 1,300 36,382 CAB 30's 0.00 0.00 < 2500 1,500 0 L00 15's 0.00 0.00 < 1000 1,000. LOC 30's 0.00 0.00 < 1000 1,000 0 TOTAL 1200.00 < 1200 0 36,382 AVG. 200.00 6,064 NEWSPAPER FULL PG. 8.33 500.00 < 500 0 10,583 HALF PG. 8.51 400.00 < 500 100 7,234 6X4" PG. 0.00 0.00 < 500 500 0 8X4” PG. 0.00 0.00 < 500 500 0 4 PG. INS. 0.00 0.00 < 500 500 0 6 PG. INS. 0.00 0.00 < 500 500 0 TOTAL 900.00 < 900 0 17,817 AVG. 1 50.00 2,970 WEEK MG. FR COVER 5.00 500.00 < 500 0 17,250 BK COVER 3.53 300.00 < 500 200 9,582 2nd PAGE 7.14 500.00 < 500 0 16,607 3rd PAGE 8.33 500.00 < 500 0 18,625 EXT. FLAP 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 500 0 ONE PAGE 0.00 0.00 < 500 500 0 TOTAL 1800.00 < 1800 0 62,064 AVG. 257.14 8.866 128 APPENDIX C— Best12.C, columns 13 thru 18, rows 1 thru 3 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST 3 NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE TELEVISION NET 15's 0 0.00 < 2500 2,500 0 NET 30's 0 0.00 < 2500 2,500 0 CAB 15's 11 1210.00 < 2500 1,290 36,385 CAB 30's 0 0.00 < 2500 1,500 0 L00 15's 0 0.00 < 1000 1,000 0 LOC 30's 0 0.00 < 1000 1,000 0 TOTAL 1210.00 < 1200 -10 36,385 AVG. 201 .67 6,064 NEWSPAPER FULL PG. 8 480.00 < 500 20 10,160 HALF PG. 8 376.00 < 500 124 6,800 6X4” PG. 0 0.00 < 500 500 0 8X4” PG. 0 0.00 < 500 500 0 4 PG. INS. o 0.00 < 500 500 o 6 PG. INS. o 0.00 < 500 500 0 TOTAL 856.00 < 900 44 16,960 AVG. 142.67 2,827 WEEK MG. FR COVER 5 500.00 < 500 0 17,250 BK COVER 3 255.00 < 500 245 8,145 2nd PAGE 7 490.00 < 500 10 16,275 3rd PAGE 8 480.00 < 500 20 17,880 EXT. FLAP 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 500 0 ONE PAGE 0 0.00 < 500 500 0 TOTAL 1725.00 < 1800 75 59,550 AVG. 246.43 8,507 129 APPENDIX C— Best12.C, columns 1 thru 6, rows 4 thru 6 of computer printout REACH FREQ. IMPRSS. 1 ,000's 1 ,000's MEDIA VARIABLES MNTH. MG. FR COVER 1,005 3.0 3,015 BK COVER 885 3.0 2,655 2nd PAGE 665 3.0 1,995 3rd PAGE 580 3.0 1,740 EXT .FLAP 825 1.0 845 TWO PAGE 605 2.0 1,210 ONE PAGE 535 2.0 1,070 TOTAL 5,100 12,510 AVG. 729 1,787 BUSN. MG. FR COVER 775 2.0 1,550 BK COVER 725 2.0 1,450 2nd PAGE 665 2.0 1,330 3rd PAGE 660 2.0 1,320 TWO PAGE 685 2.0 1,370 ONE PAGE 595 2.0 1,190 TOTAL 4,105 8,210 AVG. 684 1,368 CONS. MG. FR COVER 445 2.0 890 BK COVER 400 2.0 800 TWO PAGE 355 2.0 710 ONE PAGE 310 2.0 620 4PG. INS. 380 2.0 760 TOTAL 1 ,890 3,780 AVG. 378 756 UNIT COST $1 000's 1 15.00 98. 00 77. 00 68. 00 95. 00 75. 00 38.50 566.50 80. 93 92. 00 83.00 78. 00 72. 00 58. 50 33. 00 416.50 69.42 82. 00 76. 50 34.00 1 8. 50 65. 00 276.00 55.20 C.P.M. $ 8.74 9.03 8.64 8.53 8.68 8.07 13.90 65.58 9.37 8.42 8.73 8.53 9.17 11.71 18.03 64.59 10.77 5.43 5.23 10.44 16.76 5.85 43.70 8.74 130 APPENDIX C — Best12.C, columns 7 thru 12, rows 4 thru 6 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE MNTH. MG. FR COVER 0.00 0.00 < 500 500 0 BK COVER 3.06 300.00 < 500 200 8,128 2nd PAGE 0.00 0.00 < 500 500 0 3rd PAGE 0.00 0.00 < 500 500 0 EXT .FLAP 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 500 0 ONE PAGE 12.99 500.00 < 500 0 13,896 TOTAL 800.00 < 1800 1,000 22,024 AVG. 114.29 3,146 BUSN. MG. FR COVER 0.00 0.00 < 500 500 0 BK COVER 0.00 0.00 < 500 500 0 2nd PAGE 0.00 0.00 < 500 500 0 3rd PAGE 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 300 0 ONE PAGE 15.15 500.00 < 500 0 18,030 TOTAL 500.00 < 1800 1,300 18,030 AVG. 83.33 3,005 CONS. MG. FR COVER 0.00 0.00 < 500 500 0 BK COVER 0.00 0.00 < 500 500 0 TWO PAGE 0.00 0.00 < 500 500 0 ONE PAGE 27.03 500.00 < 500 0 16,757 4PG. INS. 0.00 0.00 < 500 500 0 TOTAL 500.00 < 1800 1,300 16,757 AVG. 100.00 3,351 131 APPENDIX C— Best12.C, columns 13 thru 18, rows 4 thru 6 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE MNTH. MG. FR COVER 0 0.00 < 500 500 0 BK COVER 3 294.00 < 500 206 7,965 2nd PAGE 0 0.00 < 500 500 0 3rd PAGE 0 0.00 < 500 500 0 EXT.FLAP 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 500 0 ONE PAGE 12 462.00 < 500 38 12,840 TOTAL 756.00 < 1800 1,044 20,805 AVG. 108.00 2,972 BUSN. MG. FR COVER 0 0.00 < 500 500 0 BK COVER 0 0.00 < 500 500 0 2nd PAGE 0 0.00 < 500 500 0 3rd PAGE 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 300 0 ONE PAGE 15 495.00 < 500 5 17,850 TOTAL 495.00 < 1800 1,305 17,850 AVG. 82.50 2,975 CONS. MG. FR COVER 0 0.00 < 500 500 0 BK COVER 0 0.00 < 500 500 0 TWO PAGE 0 0.00 < 500 500 0 ONE PAGE 27 499.50 < 500 1 16,740 4PG. INS. 0 0.00 < 500 500 0 TOTAL 499.50 < 1800 1,301 16,740 AVG. 99.90 3,348 132 APPENDIX C — Best12.C, columns 1 thru 6, rows 7 thru 10 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's $ MEDIA VARIABLES RADIO SPOT 1 5's 58 4.0 232 18.00 3.22 SPOT 30's 73 4.0 292 32.50 2.25 SP. NEWS 44 6.0 264 9.00 4.89 SP. REPORT 28 6.0 1 68 12. 75 2.20 TOTAL 203 956 72.25 1 2.55 AVG. 51 239 18.06 3.14 OUTDOOR 6M LEASE 12X25 6 3.0 18 2.10 2.86 14X48 . 12 5.0 60 3.15 3.81 ROTARY PLAN 12X25 12 3.0 36 15.60 0.77 14X48 18 5.0 90 17.40 1 .03 TOTAL 48 204 38. 25 6.47 AVG. 12 51 9.56 2.12 TRANSIT BUS CARD 45 4.0 180 22.25 2.02 POSTERS 65 2.0 130 36.50 1 .78 TOTAL 1 10 310 58.75 3.80 AVG. 55 155 29.38 1 .90 DIRECT MAIL LEAFLETS 5 1 .0 5 0.60 8.33 BROCHURE 25 3.0 75 55. 75 0.45 NEWSLETTER 12 1.0 12 2.25 5.33 TOTAL 42 92 58.60 1 4.1 2 AVG. 14 31 19.53 ’ 4.71 133 APPENDIX C — Best12.C, columns 7 thru 12, rows 7 thru 10 of computer printout #OF UNITS TOTAL COST SELECTED MEDIA VARIABLES RADIO SPOT 15‘s 0.00 SPOT 30's 0.00 SP. NEWS 16.67 SP. REPORT 11.76 TOTAL AVG. OUTDOOR 6M LEASE 12X25 238.10 14X48 158.73 ROTARY PLAN 1 2X25 1 2.82 1 4X48 28.74 TOTAL AVG. TRANSIT BUS CARD 26.97 POSTERS 16.44 TOTAL AVG. DIRECT MAIL LEAFLETS 83.33 BROCHURE 0.00 NEWSLETTER 22.22 TOTAL AVG. OF UNITS SELECTED $1 ,000's 0.00 0.00 150.00 150.00 300.00 75.00 500.00 500.00 200.00 500.00 1 700.00 425.00 600.00 600.00 1 200. 00 600.00 50.00 0.00 50.00 100.00 33.33 CONSTRAINTS $ LIMIT 150 150 150 150 AAAA <500 <500 <500 < 2250 <600 <600 < 2250 <50 <600 <50 < 2250 NON - NEGATIVE 150 150 600 300 550 1 .050 600 2,150 TOTAL IMPRSS. 4,400 1,976 6,376 1 .594 4.286 9. 524 462 2,586 16,858 4,215 4,854 2.137 6.991 3,496 417 267 228 134 APPENDIX C — Best12.C, columns13 thru18, rows 7 thru 10 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE RADIO SPOT 1 5'5 0 0.00 < 150 150 0 SPOT 30's 0 0.00 < 150 150 0 SP. NEWS 16 144.50 < 150 6 4,224 SP. REPORT 1 1 140.25 < 150 10 1,848 TOTAL 284.75 < 900 616 6,072 AVG. 71.19 1.518 OUTDOOR 6M LEASE 12X25 238 499.80 < 500 0 4.284 14X48 1 59 497.70 < 500 2 9,480 ROTARY PLAN 12X25 13 202.80 < 500 297 468 14X48 28 487.20 < 500 13 2,520 TOTAL 1 687.50 < 2250 563 16,752 AVG. 421.88 4,188 TRANSIT BUS CARD 26 578.50 < 600 22 4,680 POSTERS 16 584.00 < 600 16 2,080 TOTAL 1 162.50 < 2250 1.088 6.760 AVG. 581.25 3.380 DIRECT MAIL LEAFLETS 83 49.80 < 50 0 415 BROCHURE 0 0.00 < 600 600 0 NEWSLETTER 22 49.50 < 50 1 '264 TOTAL 99.30 < 2250 2,152 679 AVG. 33.1 0 226 135 APPENDIX C — Best12.C, columns 1 thru 6, row 11 of computer printout REACH FREQ. IMPRSS. UNIT COST 1 ,000's 1 ,000’s $1 000's MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 1 00 2.0 200 520.00 CATALOGUE 20 1.0 20 1 1.50 BANNERS 1 1.0 1 11.85 MAPS 25 2.0 50 88.75 IMPRINTS T-SHIRTS 1 4.0 4 5.00 B. STICK 5 2.0 10 3.70 POSTERS 5 2.0 1 0 1 0.00 MENUS 1 1.0 1 4.35 KEYS 24 1 .0 24 9.50 TOTAL 232 722 71 9.65 AVG. 26 80 79. 96 C.P.M. 0.19 1.74 0.08 0.28 0.20 1 .35 0.50 0.23 2.53 7.11 0.79 APPENDIX C — Best12.C, columns 7 thru 12, row 11 of computer printout #OF UNITS TOTAL COST SELECTED MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 2.54 CATALOGUE 6.96 BANNERS 0.00 MAPS 13.52 IMPRINTS T-SHIRTS 20.00 B. STICK 27.03 POSTERS 1 0.00 MENUS 0.00 KEYS 10.53 TOTAL AVG. OF UNITS SELECTED $1 ,000's 1 320.00 80.00 0.00 1200.00 1 00.00 1 00.00 1 00.00 0.00 1 00.00 3000.00 333.33 BUDGET SPENT $1 .0005 12000.00 136 CONSTRAINTS $ NON- LIMIT NEGATIVE < 2400 1.080 < 80 0 < 80 80 < 1200 0 < 100 0 < 100 0 < 100 0 < 100 100 < 100 0 < 3000 0 BUDGET TOTAL LIMIT NET $1 ,000's BUDGET < 12.000 0 TOTAL IMPRSS. 508 1 39 676 80 270 1 00 253 2.026 225 TOTAL IMPRSS. 1 ,000's 206,008 137 APPENDIX C — Best12.C, columns 13 thru 18, row 11 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 2 1040.00 < 2400 1,360 400 CATALOGUE 6 69.00 < 80 11 120 BANNERS 0 0.00 < 80 80 0 MAPS 13 1 153.75 < 1200 46 650 IMPRINTS T-SHIRTS 20 100.00 < 100 0 80 B. STICK 27 99.90 < 100 0 270 POSTERS 1 0 100.00 < 1 00 0 100 MENUS 0 0.00 < 100 100 0 KEYS 11 95.00 < 100 5 240 TOTAL 2657.65 < 3000 342 1,860 AVG. 295.29 207 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1 ,000's $1 ,000's BUDGET 1 ,000's 11432.70 < 12,000 567 200,713 APPENDIX D BEST 5.A SPREADSHEET FILE; as it represents the optimal solution output file for a $5 million dollar budget and the budget allocation strategy A (mixed media strategy). . 138 139 APPENDIX D. — Best5.A, columns 1 thru 6, rows 1 thru 3 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's 5 MEDIA VARIABLES TELEVISION NET 15's 4.850 1.0 4.850 175.00 27.71 NET 30's 5.575 1.0 5,575 225.00 24.78 CAB 15's 3.335 1 .0 3.335 1 10.00 30.32 CAB 30’s 4,275 1.0 4.275 155.00 27.58 LOC 15's 545 1 .0 545 30.00 18.1 7 L00 30's 880 1.0 880 45.00 19.56 TOTAL 1 9,460 1 9.460 740.00 127.37 AVG. 3,243 3,243 123.33 21 .23 NEWSPAPER FULL PG. 635 2.0 1.270 60.00 10.58 HALF PG. 425 2.0 850 47.00 9.04 6X4" PG. 165 2.0 330 22.00 7.50 8X4“ PG. 190 2.0 380 28.50 6.67 4 PG. INS. 625 2.0 1.250 98.00 6.38 6 PG. INS. 630 2.0 1,260 109.50 5.75 TOTAL 2,670 5,340 365.00 45.92 AVG. 445 890 60.83 7.65 WEEK MG. FR COVER 1,150 3.0 345 100.00 11.50 BK COVER 905 3.0 2,715 85. 00 1 0.65 2nd PAGE 775 3.0 2.325 70.00 11.07 3rd PAGE 745 3.0 2.235 60. 00 12.42 EXT. FLAP 950 1.0 950 85.00 1 1.18 TWO PAGE 865 2.0 1,730 75.00 11.53 ONE PAGE 610 2.0 1.220 55.00 1 1.09 TOTAL 6,000 14,625 530.00 79.44 AVG. 857 2.089 75.71 1 1 .35 140 APPENDIX D. -— Best5.A. columns 7 thru 12, rows 1 thru 3 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE TELEVISION NET 15's 0.00 250.00 < 2500 2,500 0 NET 30's 0.00 0.00 < 2500 2,500 0 CAB 15's 11.36 1250.00 < 2500 1.250 37.898 CAB 30's 0.00 0.00 < 2500 2,500 0 LOC 15's 0.00 0.00 < 1000 1.000 0 LOC 30's 0.00 0.00 < 1000 1.000 0 TOTAL 1250.00 < 1250 0 37.898 AVG. 208.33 6,316 NEWSPAPER FULL PG. 7.81 468.75 < 500 31 9.922 HALF PG. 0.00 0.00 < 500 500 0 6X4” PG. 0.00 0.00 < 500 500 0 8X4“ PG. 0.00 0.00 < 500 500 0 4 PG. INS. 0.00 0.00 < 500 500 0 6 PG. INS. 0.00 0.00 < 500 500 0 TOTAL 468.75 < 468.75 0 9.922 AVG. 78.13 1,654 WEEK MG. FR COVER 4.00 400.00 < 400 0 13,800 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 1.96 137.50 < 400 262 4.567 3rd PAGE 6.67 400.00 < 400 0 14.900 EXT. FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 ‘ TOTAL 937.50 < 937.5 0 33.267 AVG. 133.93 4.752 141 APPENDIX D. — Best5.A. columns13 thru18, rows 1 thru 3 of computer printout #OF UNITS TOTAL @INTEGER @INT. RECALC. UNIT COST MEDIA VARIABLES TELEVISION NET 15's 0 0.00 NET 30's 0 0.00 CAB 15's 11 1210.00 CAB 30's 0 0.00 LOC 15's 0 0.00 LOC 30's 0 0.00 TOTAL 1210.00 AVG. 201.67 NEWSPAPER FULL PG. 8 480.00 HALF PG. 0 0.00 6X4” PG. 0 0.00 8X4" PG. 0 0.00 4 PG. INS. 0 0.00 6 PG. INS. 0 0.00 TOTAL 480.00 AVG. 80.00 WEEK MG. FR COVER 4 400.00 BK COVER 0 0.00 2nd PAGE 2 137.50 3rd PAGE 6 400.00 EXT. FLAP 0 0.00 TWO PAGE 0 0.00 ONE PAGE 0 0.00 TOTAL 900.00 AVG. 128.57 CONSTRAINTS $ LIMIT NEGATIVE < 2500 2,500 < 2500 2,500 < 2500 1,290 < 2500 2,500 < 1000 1,000 < 1000 1,000 < 1250 40 < 500 20 < 500 500 < 500 500 < 500 500 < 500 500 < 500 500 < 468.75 -11 < 400 0 < 400 400 < 400 260 < 400 40 < 400 400 < 400 400 < 400 400 < 937.5 38 TOTAL RECALC. IMPRSS. 36.685 6.114 10.160 0000 10.160 1 .693 13.800 4.650 13.410 31.860 4.551 142 APPENDIX D. — Best5.A. columns 1 thru 6, rows 4 thru 6 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's $ MEDIA VARIABLES MNTH. MG. FR COVER 1 .005 3.0 3.015 1 15.00 8.74 BK COVER 885 3.0 2,655 98.00 9.03 2nd PAGE 665 3.0 1,995 77.00 8.64 3rd PAGE 580 3.0 1,740 68.00 8.53 EXT .FLAP 825 1.0 845 95.00 8.68 TWO PAGE 605 2.0 1.210 75.00 8.07 ONE PAGE 535 2.0 1,070 38.50 13.90 TOTAL 5.100 12.510 566.50 65.58 AVG. 729 1 .787 80. 93 9.37 BUSN. MG. FR COVER 775 2.0 1.550 92.00 8.42 BK COVER 725 2.0 1,450 83.00 8.73 2nd PAGE 665 2.0 1.330 78.00 8.53 3rd PAGE 660 2.0 1.320 72.00 9.17 TWO PAGE 685 2.0 1.370 58.50 11.71 ONE PAGE 595 2.0 1,190 33.00 18.03 TOTAL 4,105 8.210 416.50 64.59 AVG. 684 1 .368 69.42 10.77 CONS. MG. FR COVER 445 2.0 890 82.00 5.43 BK COVER 400 2.0 800 76.50 5.23 TWO PAGE 355 2.0 710 34.00 1 0.44 ONE PAGE 310 2.0 620 18.50 16.76 4PG. INS. 380 2.0 760 65.00 5.85 TOTAL 1 .890 3,780 276.00 43.70 AVG. 378 756 55.20 8.74 143 APPENDIX D. — Best5.A. columns 7 thru 12, rows 4 thru 6 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE MNTH. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 EXT.FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 3.57 137.50 < 400 262 3,821 TOTAL 137.50 < 937.5 800 3,821 AVG. 19.64 546 BUSN. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 12.12 400.00 < 400 0 14,424 TOTAL 400.00 < 937.50 538 14,424 AVG. 66.67 2,404 CONS. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 21.62 400.00 < 400 0 13,405 4PG. INS. 0.00 0.00 < 400 400 0 TOTAL 400.00 < 937.5 538 13,405 AVG. 80.00 2.681 144 APPENDIX D. -— Best5.A. columns13 thru18, rows 4 thru 6 01 computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE MNTH. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 EXT .FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 3 115.50 < 400 285 3,210 TOTAL 115.50 < 937.5 822 3.210 AVG. 1 6.50 459 BUSN. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 12 396.00 < 400 0 14,280 TOTAL 396.00 < 937.50 542 14,280 AVG. 66.00 2.380 CONS. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 21 388.50 < 400 12 13.020 4PG. INS. 0 0.00 < 400 400 0 TOTAL 388.50 < 937.5 549 13.020 AVG. 77.70 2.604 145 APPENDIX D. — Best5.A. columns 1 thru 6, rows 7 thru10 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's $ MEDIA VARIABLES RADIO SPOT 15's 58 4.0 232 18.00 3.22 SPOT 30's 73 4.0 292 32.50 2.25 SP. NEWS 44 6.0 264 9.00 4.89 SP. REPORT 28 6.0 168 12.75 2.20 TOTAL 203 956 72. 25 12.55 AVG. 51 239 1 8. 06 3.14 OUTDOOR 6M LEASE 12X25 6 3.0 18 2.10 2.86 14X48 12 5.0 60 3.15 3.81 ROTARY PLAN 12X25 12 3.0 36 15.60 0.77 14X48 18 5.0 90 17.40 1.03 TOTAL 48 204 38.25 6.47 AVG. 12 51 9.56 2.12 TRANSIT BUS CARD 45 4.0 180 22.25 2.02 POSTERS 65 2.0 130 36. 50 1 .78 TOTAL 1 10 310 58. 75 3.80 AVG. 55 155 29.38 1 .90 DIRECT MAIL LEAFLETS 5 1 .0 5 0.60 8.33 BROCHURE 25 3.0 75 55. 75 0.45 NEWSLETTER 12 1 .0 12 2.25 5.33 TOTAL 42 92 58. 60 1 4.12 AVG. 14 31 19.53 4.71 146 APPENDIX D. — Best5.A. columns 7 thru12, rows 7 thru10 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE RADIO SPOT 1 5's 0.00 0.00 < 150 1 50 0 SPOT 30's 0.00 0.00 < 150 150 0 SP. NEWS 16.67 100.00 < 150 0 4,400 SP. REPORT 0.49 6.25 < 150 144 82 TOTAL 1 56.25 < 468.75 31 2 4.482 AVG. 39.06 1,121 OUTDOOR 6M LEASE 12X25 95.24 200.00 < 200 0 1 .714 14X48 63.49 200.00 < 200 0 3.810 ROTARY PLAN 12X25 0.00 0.00 < 200 200 0 14X48 0.00 0.00 < 200 200 0 TOTAL 400.00 < 468.75 69 5,524 AVG. 100.00 1.381 TRANSIT BUS CARD 9.44 21 0.00 < 300 90 1 .699 POSTERS 0.00 0.00 < 300 300 0 TOTAL 210.00 < 468.75 259 1 .699 AVG. 1 05.00 849 DIRECT MAIL LEAFLETS 25.00 15.00 < 1 5 0 1 25 BROCHURE 0.00 0.00 < 200 200 0 NEWSLETTER 0.00 0.00 < 15 15 0 TOTAL 1 5.00 < 468.75 454 1 25 AVG. 5.00 42 147 APPENDIX D. —- Best5.A. column513 thru18, rows7 thru10 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE RADIO SPOT 15's 0 0.00 < 150 150 0 SPOT 30's 0 0.00 < 150 150 0 SP. NEWS 16 144.00 < 150 6 4.224 SP. REPORT 0 0.00 < 150 150 0 TOTAL 144.00 < 468.75 325 4.224 AVG. 36.00 1.056 OUTDOOR 6M LEASE 12X25 95 199.50 < 200 1 1.710 14X48 63 1 98.45 < 200 2 3.780 ROTARY PLAN 12X25 0 50.00 < 200 200 0 14X48 0 $0.00 < 200 200 0 TOTAL 397.95 < 468.75 71 5.490 AVG. 99.49 1 .373 TRANSIT BUS CARD 9 200.25 < 300 100 1.620 POSTERS 0 0.00 < 300 300 0 TOTAL 200.25 < 468.75 269 1,620 AVG. 1 00.13 81 0 DIRECT MAIL LEAFLETS 25 15.00 < 15 0 1 25 BROCHURE 0 0.00 < 200 200 0 NEWSLETTER 0 0.00 < 15 1 5 0 TOTAL 1 5.00 < 468.75 454 1 25 AVG. 5.00 42 148 APPENDIX D. — Best5.A. columns 1 thru 6, r0w11 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's $ MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 1 00 3.0 300 520.00 0.1 9 CATALOGUE 20 3.0 60 1 1.50 1 .74 BANNERS 1 1.0 1 1 1.85 0.08 MAPS 75 4.0 300 143.75 0.52 IMPRINTS T-SHIRTS 1 6.0 6 5.00 0.20 B. STICK 5 4.0 20 3.70 1.35 POSTERS 5 2.0 1 0 1 0. 00 0.50 MENUS 1 1 .0 1 4.35 0.23 KEYS 24 1 .0 24 9.50 2.53 TOTAL 232 722 719.65 7.35 AVG. 26 80 79.96 0.82 149 APPENDIX D. — Best5.A. columns 7 thru 12. r0w11 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 0.00 0.00 < 1200 1.200 0 CATALOGUE 3.48 40.00 < 40 0 70 BANNERS 0.00 0.00 < 40 40 0 MAPS 5.24 465.00 < 600 135 262 IMPRINTS T-SHIRTS 6.00 30.00 < 30 0 24 B. STICK 8.11 30.00 < 30 0 81 POSTERS 3.00 30.00 < 30 0 30 MENUS 0.00 0.00 < 30 30 0 KEYS 3.16 30.00 < 30 0 76 TOTAL 625.00 < 625 0 542 AVG. 69.44 60 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1 ,000's $1 ,000's BUDGET 1,000's 5000.00 < 5.000 0 125,110 150 APPENDIX D. — Best5.A. columns13 thru18, rowtt 01 computer printout #OF UNITS TOTAL @INTEGER @INT. RECALC. VALUE UNIT COST MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 0 0.00 CATALOGUE 3 34.50 BANNERS 0 0.00 MAPS 5 443.75 IMPRINTS T-SHIRTS 6 30.00 B. STICK 8 29.60 POSTERS 3 30.00 MENUS 0 0.00 KEYS 3 28.50 TOTAL 596.35 AVG. 69.44 BUDGET SPENT $1 ,000's 4843.55 CONSTRAINTS $ NON- LIMIT NEGATIVE < 1200 1,200 < 40 6 < 40 40 < 600 156 < 30 0 < 30 0 < 30 0 < 30 30 < 30 2 < 625 29 BUDGET TOTAL LIMIT NET $1 ,000's BUDGET < 5.000 156 TOTAL RECALC. IMPRSS. TOTAL IMPRSS. 1 ,000's 121, 60 250 24 80 30 72 516 57 190 APPENDIX E BEST 5.B SPREADSHEET FILE; as it represents the optimal solution output file for a $5 million dollar budget and the budget allocation strategy B (high impression media generating strategy). 151 152 APPENDIX E. — Best5.B. columns 1 thru 6, rows 1 thru 3 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's $ MEDIA VARIABLES TELEVISION NET 15's 4.850 1 .0 4.850 175.00 27.71 NET 30's 5.575 1.0 5.575 225.00 24.78 CAB 15's 3,335 1.0 3.335 1 10.00 30.32 CAB 30's 4.275 1.0 4.275 155.00 27.58 LOC 15's 545 1.0 545 30.00 18.17 LOC 30's 880 1.0 880 45.00 19.56 TOTAL 1 9.460 19.460 740.00 127.37 AVG. 3.243 3.243 123.33 21 .23 NEWSPAPER FULL PG. 635 2.0 ‘ 1.270 60.00 10.58 HALF PG. 425 2.0 850 47.00 9.04 6X4“ PG. 165 2.0 330 22.00 7.50 8X4” PG. 190 2.0 380 28.50 6.67 4 PG. INS. 625 2.0 1.250 98.00 6.38 6 PG. INS. 630 2.0 1.260 109.50 5.75 TOTAL 2,670 5.340 365.00 45.92 AVG. 445 890 60.83 7.65 WEEK MG. FR COVER 1.150 3.0 345 100.00 11.50 BK COVER 905 3.0 2.71 5 85.00 1 0.65 2nd PAGE 775 3.0 2,325 70.00 11.07 3rd PAGE 745 3.0 2.235 60. 00 12.42 EXT. FLAP 950 1.0 950 85.00 11.18 TWO PAGE 865 2.0 1,730 75.00 11.53 ONE PAGE 610 2.0 1,220 55.00 1 1 .09 TOTAL 6.000 14,625 530.00 79.44 AVG. 857 2,089 75.71 1 1.35 153 APPENDIX E. — Best5.B. columns 7 thru 12, rows 1 thru 3 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE TELEVISION NET 15's 0.00 0.00 < 2500 2,500 0 NET 30's 0.00 0.00 < 2500 2,500 O CAB 15's 22.73 2500.00 < 2500 0 75,795 CAB 30's 0.00 0.00 < 2500 2.500 0 LOC 15's 0.00 0.00 < 1000 1,000 0 LOC 30's 0.00 0.00 < 1000 1,000 0 TOTAL 2500.00 < 2500 0 75,795 AVG. 416.67 12,633 NEWSPAPER FULL PG. 8.33 500.00 < 500 0 10.583 HALF PG. 5.32 250.00 < 500 250 4.521 6X4” PG. 0.00 0.00 < 500 500 0 8X4” PG. 0.00 0.00 < 500 500 0 4 PG. INS. 0.00 0.00 < 500 500 0 6 PG. INS. 0.00 0.00 < 500 500 0 TOTAL 750.00 < 750 0 15,105 AVG. 125.00 2,517 WEEK MG. FR COVER 1.00 100.00 < 400 300 3.450 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 6.67 400.00 < 400 0 14.900 ,EXT. FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 500.00 < 500 0 18.350 AVG. 71.43 2.621 154 APPENDIX E. — BestS.B , c0lumnst3 thru18, rows 1 thru 3 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE TELEVISION NET 15's 0 0.00 < 2500 2.500 0 NET 30's 0 0.00 < 2500 2.500 0 CAB 15's 22 2420.00 < 2500 80 73.370 CAB 30's 0 0.00 < 2500 2,500 0 LOC 15's 0 0.00 < 1000 1,000 0 LOC 30's 0 0.00 < 1000 1.000 0 TOTAL 2420.00 < 2500 80 73.370 AVG. 403.33 12,228 NEWSPAPER FULL PG. 8 480.00 < 500 20 10,160 HALF PG. 5 235.00 < 500 265 4.250 6X4“ PG. 0 0.00 < 500 500 0 8X4“ PG. 0 0.00 < 500 500 0 4 PG. INS. 0 0.00 < 500 500 0 6 PG. INS. 0 0.00 < 500 500 0 TOTAL 715.00 < 750 35 14.410 AVG. 1 19.18 2.402 WEEK MG. FR COVER 1 100.00 < 400 300 3,450 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 6 360.00 < 400 40 13.410 EXT. FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 460.00 < 500 40 16.860 AVG. 65.71 2,409 155 APPENDIX E. — Best5.B. columns 1 thru 6. rows 4 thru 6 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's $ MEDIA VARIABLES MNTH. MG. FR COVER 1.005 3.0 3.015 115.00 8.74 BK COVER 885 3.0 2,655 98.00 9.03 211d PAGE 665 3.0 1.995 77.00 8.64 3rd PAGE 580 3.0 1.740 68.00 8.53 EXT .FLAP 825 1.0 845 95.00 8.68 TWO PAGE 605 2.0 1.210 75.00 8.07 ONE PAGE 535 2.0 1,070 38.50 13.90 TOTAL 5.100 12.510 566.50 65.58 AVG. 729 1 .787 80. 93 9.37 BUSN. MG. FR COVER 775 2.0 1.550 92.00 8.42 BK COVER 725 2.0 1.450 83.00 8.73 2nd PAGE 665 2.0 1.330 78.00 8.53 3rd PAGE 660 2.0 1.320 72.00 9.17 TWO PAGE 685 2.0 1.370 58.50 11.71 ONE PAGE 595 2.0 1 .1 90 33. 00 1 8.03 TOTAL 4,105 8.210 416.50 64.59 AVG. 684 1 .368 69.42 1 0.77 CONS. MG. FR COVER 445 2.0 890 82.00 5.43 BK COVER 400 2.0 800 76.50 5.23 TWO PAGE 355 2.0 710 34.00 10.44 ONE PAGE 31 O 2.0 620 18. 50 1 6.76 4PG. INS. 380 2.0 760 65.00 5.85 TOTAL 1 .890 3.780 276.00 43.70 AVG. 378 756 55.20 8.74 156 APPENDIX E. — Best5.B. columns 7 thru 12. rows 4 thru 6 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE MNTH. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 EXT.FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 0.00 < 500 500 0 AVG. 0.00 0 BUSN. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 12.12 400.00 < 400 0 14,424 TOTAL 400.00 < 500 100 14,424 AVG. 66.67 2,404 CONS. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 5.41 100.00 < 400 0 3.351 4PG. INS. 0.00 0.00 < 400 400 0 TOTAL 100.00 < 500 400 3,351 AVG. 20.00 670 157 APPENDIX E. -- Best5.B. columns13 thru18, rows 4 thru 6 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST 3 NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE MNTH. MG. FR COVER 0 0.00 < 400 400 o BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 o EXT.FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 , 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 0.00 < 500 500 0 AVG. 0.00 0 BUSN. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 12 396.00 < 400 4 14,280 TOTAL 396.00 < 500 104 14,280 AVG. 66.00 2,380 CONS. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 5 92.50 < 400 308 3,100 4PG. INS. o 0.00 < 400 400 0 TOTAL 92.50 < 500 408 3,100 AVG. 1 8.50 620 158 APPENDIX E. — Best5.B. columns 1 thru 6. rows 7 thru10 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's 3 MEDIA VARIABLES RADIO SPOT 15's 58 4.0 232 18. 00 3.22 SPOT 30's 73 4.0 292 32.50 2.25 SP. NEWS 44 6.0 264 9.00 4.89 SP. REPORT 28 6.0 168 12.75 2.20 TOTAL 203 956 72.25 1 2.55 AVG. 51 239 18.06 3.14 OUTDOOR 6M LEASE 12X25 6 3.0 . 18 2.10 2.86 14X48 12 5.0 60 3.15 3.81 ROTARY PLAN 12X25 12 3.0 36 15.60 0.77 14X48 18 5.0 90 17.40 1.03 TOTAL 48 204 38.25 6.47 AVG. 12 51 9.56 2.12 TRANSIT BUS CARD 45 4.0 180 22.25 2.02 POSTERS 65 2.0 130 36.50 1 .78 TOTAL 1 1 0 31 0 58. 75 3.80 AVG. 55 155 29.38 1 .90 DIRECT MAIL LEAFLETS 5 1 .0 5 0.60 8.33 BROCHURE 25 3.0 75 55.75 0.45 _ NEWSLETTER 12 1.0 12 2.25 ‘ 5.33 TOTAL 42 92 58. 60 1 4.1 2 AVG. 14 31 19.53 4.71 159 APPENDIX E. —— Best5.B. columns 7 thru12, rows 7 thru10 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE RADIO SPOT 1 5'8 0.00 0.00 < 1 50 1 50 0 SPOT 30's 0.00 0.00 < 150 150 0 SP. NEWS 16.67 150.00 < 150 0 4,400 SP. REPORT 7.84 100.00 < 150 50 1 .318 TOTAL 250.00 < 750 31 2 5.718 AVG. 62.50 1 .429 OUTDOOR 6M LEASE 12X25 0.00 0.00 < 200 200 0 14X48 59. 52 187.50 < 200 12 3.571 ROTARY PLAN 12X25 0.00 0.00 < 200 200 0 14X48 0.00 0.00 < 200 200 0 TOTAL 187.50 < 187.5 0 3,571 AVG. 46.88 893 TRANSIT BUS CARD 2.13 47.50 < 300 253 384 POSTERS 0.00 0.00 < 300 300 0 TOTAL 47.50 < 187.5 140 384 AVG. 23.75 1 92 DIRECT MAIL LEAFLETS 25.00 15.00 < 1 5 0 1 25 BROCHURE 0.00 0.00 < 200 200 0 NEWSLETTER 0.00 0.00 < 15 15 0 TOTAL 15.00 < 187.5 173 125 AVG. 5.00 42 APPENDIX E. — Best5.B. columnst3 thru18, r0ws7 thru10 of computer printout #OF UNITS TOTAL @INTEGER @INT. RECALC. UNIT COST MEDIA VARIABLES RADIO SPOT 15's 0 0.00 SPOT 30's 0 0.00 SP. NEWS 16 144.00 SP. REPORT 8 102.00 TOTAL 246.00 AVG. 61.50 OUTDOOR 6M LEASE 1 2X25 0 0.00 1 4X48 59 1 85.85 ROTARY PLAN 1 2X25 0 0.00 1 4X48 0 0.00 TOTAL 185.50 AVG. 46.46 TRANSIT BUS CARD 2 44.50 POSTERS 0 0.00 TOTAL 44.50 AVG. 22.25 DIRECT MAIL LEAF LETS 25 1 5.00 BROCHURE 0 0.00 NEWSLETTER 0 0.00 TOTAL 1 5.00 AVG. 5.00 160 CONSTRAINTS $ LIMIT AAAA 150 150 150 150 750 200 200 < 200 200 187.5 < 300 < 300 200 <15 187.5 NEGATNE 150 150 504 200 1 4 200 200 256 300 143 200 1 5 173 TOTAL RECALC. IMPRSS. 4,224 1 .344 5,568 1 .392 3.540 3.540 885 360 360 1 80 125 1 25 42 161 APPENDIX E. — Best5.B. columns 1 thru 6. row 11 of computer printout REACH FREQ. IMPRSS. UNIT COST 1 ,000's 1 ,000's $1000's MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 1 00 3.0 300 520.00 CATALOGUE 20 3.0 60 1 1.50 BANNERS 1 1.0 1 11.85 MAPS 75 4.0 300 143.75 IMPRINTS T-SHIRTS 1 6.0 6 5.00 B. STICK 5 4.0 20 3.70 POSTERS 5 2.0 1 0 1 0. 00 MENUS 1 1.0 1 4.35 KEYS 24 1.0 24 9.50 TOTAL 232 722 71 9.65 AVG. 26 80 79. 96 C.P.M. 0.19 1.74 0.08 0.52 0.20 1.35 0.50 0.23 2.53 7.35 0.82 162 APPENDIX E. — Best5.B. columns 7 thru 12. r0w11 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 0.00 0.00 < 1200 1.200 0 CATALOGUE 3.48 40.00 < 40 0 70 BANNERS 0.00 0.00 < 40 40 0 MAPS 1.01 90.00 < 600 510 51 IMPRINTS T-SHIRTS 6.00 30.00 < 30 0 24 B. STICK 8.11 30.00 < 30 0 81 POSTERS 3.00 30.00 < 30 0 30 MENUS 0.00 0.00 < 30 30 0 KEYS 3.16 30.00 < 30 0 76 TOTAL 250.00 < 250 0 331 AVG. 27.78 37 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1 ,000's $1 ,000's BUDGET 1,000's 5000.00 < 5.000 0 137.155 163 APPENDIX E. — Best5.B. columns 13 thru 18. r0w11 of computer printout #OF UNITS TOTAL @INTEGER @INT. RECALC. VALUE UNIT COST MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 0 0.00 CATALOGUE 3 34.50 BANNERS 0 0.00 MAPS 1 88.75 IMPRINTS T-SHIRTS 6 30.00 B. STICK 8 29.60 POSTERS 3 30.00 MENUS 0 0.00 KEYS 3 28.50 TOTAL 241.35 AVG. 27.78 BUDGET SPENT $1.000's 4816.20 CONSTRAINTS TOTAL RECALC. $ NON- IMPRSS. LIMIT NEGATIVE < 1200 1.200 0 < 40 6 60 < 40 40 0 < 600 511 50 < 30 0 24 < 30 0 80 < 30 0 30 < 30 30 0 < 30 2 72 < 250 9 316 35 BUDGET TOTAL TOTAL LIMIT NET IMPRSS. $1 ,000's BUDGET 1 ,000's < 5.000 184 131.929 APPENDIX F BEST 50 SPREADSHEET FILE; as it represents the optimal solution output file for a $5 million dollar budget and the budget allocation strategy C (low impression media generating strategy). 164 165 APPENDIX F. — Best5.C. columns 1 thru 6, rows 1 thru 3 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1,000's $1 000’s $ MEDIA VARIABLES TELEVISION NET 15's 4.850 1.0 4.850 175.00 27.71 NET 30's 5.575 1.0 5.575 225.00 24.78 CAB 15's 3.335 1.0 3.335 1 10.00 30.32 CAB 30's 4,275 1.0 4,275 155.00 27.58 LOC 15's 545 1 .0 545 30. 00 18.1 7 LOC 30's 880 1.0 880 45.00 19.56 TOTAL 19.460 19.460 740.00 127.37 AVG. 3.243 3,243 123.33 21 .23 NEWSPAPER FULL PG. 635 2.0 1.270 60.00 10.58 HALF PG. 425 2.0 850 47.00 9.04 6X4” PG. 165 2.0 330 22.00 7.50 8X4” PG. 190 2.0 380 28.50 6.67 4 PG. INS. 625 2.0 1.250 98.00 6.38 6 PG. INS. 630 2.0 1.260 109.50 5.75 TOTAL 2,670 5,340 365.00 45.92 AVG. 445 890 60.83 7.65 WEEK MG. FR COVER 1,150 3.0 345 100.00 11.50 BK COVER 905 3.0 2.71 5 85.00 1 0.65 2nd PAGE 775 3.0 2.325 70.00 11.07 3rd PAGE 745 3.0 2.235 60.00 12.42 EXT. FLAP 950 1.0 950 85.00 11.18 TWO PAGE 865 2.0 1.730 75.00 11.53 ONE PAGE 610 2.0 1,220 55.00 1 1 .09 TOTAL 6.000 14,625 530.00 79.44 AVG. 857 2.089 75.71 1 1 .35 166 APPENDIX F. — Best5.C. columns 7 thru 12, rows 1 thru 3 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE TELEVISION NET 15's 0.00 0.00 < 2500 2.500 0 NET 30's 0.00 0.00 < 2500 2,500 0 CAB 15's 4.55 500.00 < 2500 2.000 15.159 CAB 30's 0.00 0.00 < 2500 2.500 0 LOC 15's 0.00 0.00 < 1000 1.000 0 LOC 30's 0.00 0.00 < 1000 1.000 0 TOTAL 500.00 < 500 0 15.159 AVG. 83.33 2.527 NEWSPAPER FULL PG. 5.83 350.00 < 500 150 10.583 HALF PG. 0.00 0.00 < 500 250 4,521 6X4” PG. 0.00 0.00 < 500 500 0 8X4” PG. 0.00 0.00 < 500 500 0 4 PG. INS. 0.00 0.00 < 500 500 0 6 PG. INS. 0.00 0.00 < 500 500 0 TOTAL 350.00 < 375 25 1 5.1 05 AVG. 58.33 2.51 7 WEEK MG. FR COVER 3.50 350.00 < 400 50 12.075 BK COVER 0.00 0.00 < 400 400 0 20d PAGE 0.00 0.00 < 400 400 0 3rd PAGE 6.67 400.00 < 400 0 14.900 EXT. FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 750.00 < 750 0 26.975 AVG. 107.14 3.854 167 APPENDIX F. —- Best5.C. column513 thru18, rows 1 thru 3 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATNE TELEVISION NET 15's 0 0.00 < 2500 2.500 0 NET 30's 0 0.00 < 2500 2,500 0 CAB 15's 4 440.00 < 2500 2.060 13.340 CAB 30's 0 0.00 < 2500 2.500 0 LOC15's 0 0.00 < 1000 1.000 0 LOC 30's 0 0.00 < 1000 1.000 0 TOTAL 440.00 < 500 60 13.340 AVG. 73.33 2.223 NEWSPAPER FULL PG. 6 360.00 < 500 140 7.620 HALF PG. 0 0.00 < 500 500 4.521 6X4“ PG. 0 0.00 < 500 500 0 8X4" PG. 0 0.00 < 500 500 0 4 PG. INS. 0 0.00 < 500 500 0 6 PG. INS. 0 0.00 < 500 500 0 TOTAL 3690.00 < 375 15 7.620 AVG. 60.00 1 .270 WEEK MG. FR COVER 3 300.00 < 400 100 10.350 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 6 360.00 < 400 40 13.410 EXT. FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 660.00 < 750 90 23,760 AVG. 94.29 3.394 168 APPENDIX F. — Best5.C. columns 1 thru 6, rows 4 thru 6 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's $ MEDIA VARIABLES MNTH. MG. FR COVER 1.005 3.0 3,015 115.00 8.74 BK COVER 885 3.0 2.655 98.00 9.03 2nd PAGE 665 3.0 1.995 77.00 8.64 3rd PAGE 580 3.0 1.740 68.00 8.53 EXT .FLAP 825 1.0 845 95. 00 8.68 TWO PAGE 605 2.0 1.210 75.00 8.07 ONE PAGE 535 2.0 1.070 38.50 13.90 TOTAL 5,100 12,510 566.50 65.58 AVG. 729 1 .787 80.93 9.37 BUSN. MG. FR COVER 775 2.0 1.550 92.00 8.42 BK COVER 725 2.0 1.450 83.00 8.73 2nd PAGE 665 2.0 1,330 78.00 8.53 3rd PAGE 660 2.0 1,320 72.00 9.17 TWO PAGE 685 2.0 1.370 58.50 11.71 ONE PAGE 595 2.0 1 .190 33.00 18.03 TOTAL 4,105 8,210 416.50 64.59 AVG. 684 1.368 69.42 10.77 CONS. MG. FR COVER 445 2.0 890 82.00 5.43 BK COVER 400 2.0 800 76. 50 5.23 TWO PAGE 355 2.0 71 0 34.00 1 0.44 ONE PAGE 310 2.0 620 18.50 16.76 4PG. INS. 380 2.0 760 65.00 5.85 TOTAL 1 .890 3,780 276.00 43.70 AVG. 378 756 55.20 8.74 169 APPENDIX F. — BestS.C. columns 7 thru 12, rows 4 thru 6 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE MNTH. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 EXT .FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 ' 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 0.00 < 750 500 0 AVG. 0.00 0 BUSN. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 12.12 400.00 < 400 0 14,424 TOTAL 400.00 < 750 350 14.424 AVG. 66.67 2.404 CONS. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 18.92 350.00 < 400 0 11,730 4PG. INS. 0.00 0.00 < 400 400 0 TOTAL 350.00 < 750 400 11.730 AVG. 70.00 2.346 170 APPENDIX F. — Best5.C. columnst3 thru18, rows 4 thru 6 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATNE MNTH. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 EXT.FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 0.00 < 750 750 0 AVG. 0.00 0 BUSN. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 12 396.00 < 400 4 14,280 TOTAL 396.00 < 750 354 14.280 AVG. 66.00 2.380 CONS. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 19 351.50 < 400 49 11,780 4PG. INS. 0 0.00 < 400 400 0 TOTAL 350.00 < 750 399 11.780 AVG. 70.00 2,356 171 APPENDIX F. — Best5.C. columns 1 thru 6. rows 7 thru10 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's $ MEDIA VARIABLES RADIO SPOT 1 5'5 58 4.0 232 1 8. 00 3.22 SPOT 30's 73 4.0 292 32.50 2.25 SP. NEWS 44 6.0 264 9.00 4.89 SP. REPORT 28 6.0 168 1 2. 75 2.20 TOTAL 203 956 72.25 12.55 AVG. 51 239 18.06 3.14 OUTDOOR 6M LEASE 12X25 6 3.0 18 2.10 2.86 14X48 12 5.0 60 3.15 3.81 ROTARY PLAN 12X25 12 3.0 36 15. 60 0.77 14X48 18 5.0 90 17.40 1.03 TOTAL 48 204 38.25 6.47 AVG. 12 51 9.56 2.12 TRANSIT BUS CARD 45 4.0 180 22. 25 2.02 POSTERS 65 2.0 130 36.50 1 .78 TOTAL 1 1 0 310 58. 75 3.80 AVG. 55 155 29.38 1 .90 DIRECT MAIL LEAFLETS 5 1 .0 5 0.60 8.33 BROCHURE 25 3.0 75 55. 75 0.45 NEWSLETTER 12 1.0 12 2.25 5.33 TOTAL 42 92 58. 60 14.12 AVG. 14 31 19.53 4.71 172 APPENDIX F. — Best5.C. columns 7 thru12, rows 7 thru10 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE RADIO SPOT 15's 0.00 0.00 < 1 50 150 0 SPOT 30's 0.00 0.00 < 150 150 0 SP. NEWS 16.67 150.00 < 150 0 4.400 SP. REPORT 0.00 0.00 < 150 150 0 TOTAL 150.00 < 375 225 4.400 AVG. 37.50 1 .1 00 OUTDOOR 6M LEASE 12X25 95.24 200.00 < 200 0 1 .714 14X48 63.49 200.00 < 200 0 3.81 0 ROTARY PLAN 12X25 1 .28 20.00 < 200 180 46 14X48 11.49 200.00 < 200 0 1,034 TOTAL 620.00 < 937.5 318 6.604 AVG. 155.00 1.651 TRANSIT BUS CARD 13.48 300.00 < 300 0 2.427 POSTERS 8.22 300.00 < 300 0 1.068 TOTAL 47.50 < 937.5 337 3,495 AVG. 23.75 1 .748 DIRECT MAIL LEAF LETS 25. 00 1 5.00 < 15 0 1 25 BROCHURE 0.00 0.00 < 200 200 0 NEWSLETTER 6.67 15.00 < 15 0 80 TOTAL 30.00 < 937.5 907 205 AVG. 1 0.00 68 173 APPENDIX F. — Best5.C. columns13 thru18, rows7 thru10 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. UNIT COST $ IMPRSS. MEDIA VARIABLES LIMIT NEGATNE RADIO SPOT 15's 0 0.00 < 150 1 50 0 SPOT 30's 0 0.00 < 150 150 0 SP. NEWS 16 144.00 < 150 6 4.224 SP. REPORT 0 0.00 < 150 1 50 0 TOTAL 144.00 < 375 231 4.224 AVG. 36.00 1 .056 OUTDOOR 6M LEASE 12X25 95 199.50 < 200 1 1 .71 0 14X48 63 1 98.45 < 200 2 3.780 ROTARY PLAN 12X25 1 1 5.60 < 200 184 36 14X48 1 1 191.40 < 200 9 990 TOTAL 604.95 < 937.5 333 6.516 AVG. 151.24 1,629 TRANSIT BUS CARD 13 289.25 < 300 11 2,340 POSTERS 8 292.00 < 300 8 1,040 TOTAL 581.25 < 937.5 356 3.380 AVG. 290.63 1 .690 DIRECT MAIL LEAF LETS 25 15.00 < 1 5 0 125 BROCHURE 0 0.00 < 200 200 0 NEWSLETTER 6 13.50 < 1 5 2 72 TOTAL 28.50 < 937.5 909 197 AVG. 9.50 66 174 APPENDIX F. — Best5.C. columns 1 thru 6. r0w11 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's $ MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 1 00 3.0 300 520.00 0.1 9 CATALOGUE 20 3.0 60 1 1.50 1.74 BANNERS 1 1.0 1 11.85 0.08 MAPS 75 4.0 300 143.75 0.52 IMPRINTS T-SHIRTS 1 6.0 6 5.00 0.20 B. STICK 5 4.0 20 3.70 1.35 POSTERS 5 2.0 1 0 1 0.00 0.50 MENUS 1 1.0 1 4.35 0.23 KEYS 24 1 .0 24 9.50 2.53 TOTAL 232 722 71 9.65 7.35 AVG. 26 80 79. 96 0.82 175 APPENDIX F. — Best5.C. columns 7 thru 12. r0w11 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED s NON- MEDIA VARIABLES $1,000's LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 0.94 0.00 < 1200 710 188 CATALOGUE 3.48 40.00 < 40 0 70 BANNERS 0.00 0.00 < 40 40 0 MAPS 6.76 90.00 < 600 0 338 IMPRINTS . T-SHIRTS 6.00 30.00 < 30 o 24 B. STICK 8.11 30.00 < 30 o 81 POSTERS 3.00 30.00 < 30 0 30 MENUS 0.00 0.00 < 30 30 0 KEYS 3.16 30.00 < 30 o 76 TOTAL 1250.00 < 1250 o 807 AVG. 138.89 90 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1,000's $1,000's BUDGET 1,000’s $000.00 < 5.000 0 91,208 176 APPENDIX F. — Best5.C. columns13 thru18, r0w11 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 1 520.00 < 1200 680 200 CATALOGUE 3 34.50 < 40 6 60 BANNERS 0 0.00 < 40 40 0 MAPS 6 532.50 < 600 68 300 IMPRINTS T-SHIRTS 6 30.00 < 30 0 24 B. STICK 8 29.60 < 30 O 80 POSTERS 3 30.00 < 30 0 30 MENUS 0 0.00 < 30 30 0 KEYS 3 28.50 < 30 2 72 TOTAL 1205.10 < 1250 0 766 AVG. 138.89 85 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1 ,000's $1 ,000's BUDGET 1,000's 4771.30 < 5.000 229 85.863 APPENDIX G BEST 1.A SPREADSHEET FILE; as it represents the optimal solution output tile for a $1 million dollar budget and the budget allocation strategy A (mixed media strategy). 177 178 APPENDIX G. — Best1.A. columns 1 thru 6. rows 1 thru 3 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's $ MEDIA VARIABLES TELEVISION NET 15's 4.850 1 .0 4.850 175.00 27.71 NET 30's 5.575 1.0 5.575 225.00 24.78 CAB 15's 3.335 1 .0 3.335 1 10.00 30.32 CAB 30's 4.275 1.0 4.275 155.00 27.58 LOC 15's 545 1.0 545 30.00 18.17 LOC 30's 880 1.0 880 45.00 19.56 TOTAL 1 9,460 1 9.460 740.00 127.37 AVG. 3.243 3.243 123.33 21 .23 NEWSPAPER FULL PG. 635 2.0 1.270 60.00 10.58 HALF PG. 425 2.0 850 47.00 9.04 6X4” PG. 165 2.0 330 22.00 7.50 8X4” PG. 190 2.0 380 28.50 6.67 4 PG. INS. 625 2.0 1.250 98.00 6.38 6 PG. INS. 630 2.0 1.260 109.50 5.75 TOTAL 2,670 5.340 365.00 45.92 AVG. 445 890 60.83 7.65 WEEK MG. FR COVER 1.150 3.0 345 100.00 11.50 BK COVER 905 3.0 2.71 5 85.00 1 0.65 2nd PAGE 775 3.0 2.325 70.00 11.07 3rd PAGE 745 3.0 2,235 60.00 12.42 EXT. FLAP 950 1.0 950 85.00 1 1.18 TWO PAGE 865 2.0 1.730 75.00 11.53 ONE PAGE 610 2.0 1.220 55.00 11.09 TOTAL 6.000 14.625 530.00 79.44 AVG. 857 2.089 75.71 1 1 .35 179 APPENDIX G. — Best1.A. columns 7 thru 12. rows 1 thru 3 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATNE TELEVISION NET 15's 0.00 0.00 < 2500 2.500 0 NET 30's 0.00 0.00 < 2500 2.500 0 CAB 15's 2.27 250.00 < 2500 2,250 7.580 CAB 30's 0.00 0.00 < 2500 2.500 0 LOC15's 0.00 0.00 < 1000 1.000 0 LOC 30's 0.00 0.00 < 1000 1.000 0 TOTAL 250.00 < 250 0 7.580 AVG. 41.67 0 NEWSPAPER FULL PG. 0.52 31.25 < 500 469 661 HALF PG. 0.00 0.00 < 500 500 0 6X4” PG. 0.00 0.00 < 500 500 0 8X4“ PG. 0.00 0.00 < 500 500 0 4 PG. INS. 0.00 0.00 < 500 500 0 6 PG. INS. 0.00 0.00 < 500 500 0 TOTAL 31 .25 < 93.75 63 661 AVG. 5.21 1 10 WEEK MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < '400 400 0 3rd PAGE 3.13 187.50 < 400 213 6.984 EXT. FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 187.50 < 187.5 0 6.984 AVG. 26.79 998 180 APPENDIX G. - Bestt.A. columns 13 thru 18, rows 1 thru 3 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE TELEVISION NET 15's 0 0.00 < 2500 2.500 0 NET 30's 0 0.00 < 2500 2.500 0 CAB 15's 2 220.00 < 2500 2.280 6.670 CAB 30's 0 0.00 < 2500 2.500 0 LOC15'S 0 0.00 < 1000 1.000 0 LOC 30's 0 0.00 < 1000 ' 1.000 0 TOTAL 220.00 < 250 30 6.670 AVG. 36.67 1.1 12 NEWSPAPER FULL PG. 0 0.00 < 500 500 0 HALF PG. 0 0.00 < 500 500 0 6X4“ PG. 0 0.00 < 500 500 0 8X4" PG. 0 0.00 < 500 500 0 4 PG. INS. 0 0.00 < 500 500 0 6 PG. INS. 0 0.00 < 500 500 0 TOTAL 0.00 < 93.75 94 0.00 AVG. 0.00 0.00 WEEK MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 3 180.00 < 400 220 6.705 EXT. FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 180.00 < 187.5 8 6.705 AVG. 25.71 958 181 APPENDIX G. — BEST1.A. columns 7 thru 12, rows 4 thru 6 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATNE MNTH. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 EXT.FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 0.00 < 187.5 188 0 AVG. 0.00 0 BUSN. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 5.68 187.50 < 400 213 6.761 TOTAL 187.50 < 187.5 0 6.761 AVG. 31 .25 1.127 CONS. MG. FR COVER 0.00 0.00 < 400 400 O BK COVER 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 4PG. INS. 0.00 0.00 < 400 400 0 TOTAL 0.00 < 187.5 188 0 ' AVG. 0.00 0 182 APPENDIX G. — Best1.A. columns 1 thru 6. rows 4 thru 6 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's $ MEDIA VARIABLES MNTH. MG. FR COVER 1.005 3.0 3.015 1 15.00 8.74 BK COVER 885 3.0 2.655 98.00 9.03 2nd PAGE 665 3.0 1.995 77.00 8.64 3rd PAGE 580 3.0 1.740 68.00 8.53 EXT.FLAP 825 1 .0 845 95.00 8.68 TWO PAGE 605 2.0 1.210 75.00 8.07 ONE PAGE 535 2.0 1.070 38.50 13.90 TOTAL 5.100 12.510 566.50 65.58 AVG. 729 1 .787 80.93 9.37 BUSN. MG. FR COVER 775 2.0 1,550 92. 00 8.42 BK COVER 725 2.0 1.450 83.00 8.73 2nd PAGE 665 2.0 1.330 78.00 8.53 3rd PAGE 660 2.0 1.320 72.00 9.17 TWO PAGE 685 2.0 1.370 58.50 11.71 ONE PAGE 595 2.0 1.190 33.00 18.03 TOTAL 4,105 8.210 416.50 64.59 AVG. 684 1 .368 69.42 1 0.77 CONS. MG. FR COVER 445 2.0 890 82.00 5.43 BK COVER 400 2.0 800 76.50 5.23 TWO PAGE 355 2.0 710 34.00 10.44 ONE PAGE 310 2.0 620 18.50 16.76 4PG. INS. 380 2.0 760 65.00 5.85 TOTAL 1 .890 3.780 276.00 43.70 AVG. 378 756 55.20 8.74 LL 183 APPENDIX G. — BEST1.A. columns 13 thru 18, rows 4 thru 6 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATNE MNTH. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 EXT .FLAP O 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 ' 400 0 TOTAL 0.00 < 187.5 188 0 AVG. 0.00 0 BUSN. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 5 165.00 < 400 235 5,950 TOTAL 165.00 < 187.5 23 5.950 AVG. 27.50 992 CONS. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 4PG. INS. 0 0.00 < 400 400 0 TOTAL 0.00 < 187.5 188 0 AVG. 0.00 0 184 APPENDIX G. — Bestt.A, columns 1 thru 6, rows 7 thru10 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's 310005 $ MEDIA VARIABLES RADIO SPOT 15's 58 4.0 232 18.00 3.22 SPOT 30's 73 4.0 292 32.50 2.25 SP. NEWS 44 6.0 264 9.00 4.89 SP. REPORT 28 6.0 168 1 2. 75 2.20 TOTAL 203 956 72.25 12.55 AVG. 51 239 18.06 3.14 OUTDOOR 6M LEASE 12X25 6 3.0 18 2.10 2.86 14X48 12 5.0 . 60 3.15 3.81 ROTARY PLAN 12X25 12 3.0 36 15.60 0.77 14X48 18 5.0 90 17.40 1.03 TOTAL 48 204 38.25 6.47 AVG. 12 51 9.56 2.12 TRANSIT BUS CARD 45 4.0 180 22.25 2.02 POSTERS 65 2.0 130 36. 50 1 .78 TOTAL 1 10 310 58.75 3.80 AVG. 55 155 29.38 1 .90 DIRECT MAIL LEAFLETS 5 1 .0 5 0.60 8.33 BROCHURE 25 3.0 75 55. 75 0.45 NEWSIEITER 12 1 .0 12 2.25 5.33 TOTAL 42 92 58.60 14.1 2 AVG. 14 31 19.53 4.71 185 APPENDIX G. — BEST1.A. columns 7 thru 12, rows 7 thru 10 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE RADIO SPOT 15's 0.00 0.00 < 150 1 00 0 SPOT 30's 0.00 0.00 < 150 100 0 SP. NEWS 10.42 93.75 < 150 56 2.750 SP. REPORT 0.00 0.00 < 150 100 0 TOTAL 93.75 < 93.75 0 2.750 AVG. 23.44 688 OUTDOOR 6M LEASE 12X25 0.00 0.00 < 200 200 0 14X48 29.76 93.75 < 200 1 06 1 .786 ROTARY PLAN 12X25 0.00 0.00 < 200 200 0 14X48 0.00 0.00 < 200 200 0 TOTAL 93.75 < 93.75 0 1.786 AVG. 23.44 447 TRANSIT BUS CARD 0.73 16.25 < 300 284 131 POSTERS 0.00 0.00 < 300 300 0 TOTAL 16.25 < 93.75 77 131 AVG. 8.1 3 66 DIRECT MAIL LEAFLETS 25.00 15.00 < 15 0 1 25 BROCHURE 0.00 0.00 < 200 200 0 NEWSLETTER 0.00 0.00 < 15 15 0 TOTAL 15.00 < 93.75 79 125 AVG. 5.00 42 186 APPENDIX G.— BEST1.A. columns 13 thru18, rows 7 thru10 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE RADIO SPOT 15's 0 0.00 < 150 100 0 SPOT 30's 0 0.00 < 150 100 0 SP. NEWS 10 90.00 < 150 60 2,640 SP. REPORT 0 0.00 < 150 100 0 TOTAL 90.00 < 93.75 4 2,640 AVG. 22.50 660 OUTDOOR 6M LEASE 12X25 0 0.00 < 200 200 0 14X48 30 94.50 < 200 106 1.800 ROTARY PLAN 12X25 0 0.00 < 200 200 0 14X48 0 0.00 < 200 200 0 TOTAL 94.50 < 93.75 -1 1.800 AVG. 23.63 450 TRANSIT BUS CARD 0 0.00 < 300 300 0 POSTERS 0 0.00 < 300 300 0 TOTAL 0.00 < 93.75 94 0.00 AVG. 0.00 0.00 DIRECT MAIL LEAFLETS 25 1 5.00 < 15 0 125 BROCHURE 0 0.00 < 200 200 0 NEWSLETTER 0 0.00 < 1 5 15 0 TOTAL 1 5.00 < 93.75 79 1 25 AVG. 5.00 42 187 APPENDIX G. -- Best1.A. columns 1 thru 6, r0w11 of computer printout 1,000's MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 100 CATALOGUE 20 BANNERS 1 MAPS 25 IMPRINTS T-SHIRTS 1 B. STICK 5 POSTERS 5 MENUS 1 KEYS 24 TOTAL 232 AVG. 26 IMPRSS. 1 ,000's 2.0 200 1.0 20 1.0 1 2.0 50 4.0 4 2.0 10 2.0 10 1 .0 1 1 .0 24 722 80 UNIT COST $1 000's 520.00 1 1.50 1 1.85 88.75 5.00 3.70 10.00 4.35 9.50 719.65 79.96 C.P.M. 0.19 1.74 0.08 0.28 0.20 1 .35 0.50 0.23 2.53 7.11 0.79 188 APPENDIX G. — BEST1.A. columns 7 thru 12. row 11 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 0.00 0.00 < 1200 1.200 0 CATALOGUE 3.48 40.00 < 40 0 70 BANNERS 0.00 0.00 < 40 40 0 MAPS 0.00 0.00 < 600 600 0 IMPRINTS T-SHIRTS 0.00 0.00 < 30 30 0 B. STICK 8.11 30.00 < 30 0 81 POSTERS 2.50 25.00 < 30 5 25 MENUS 0.00 0.00 < 30 30 0 KEYS 3.16 30.00 < 30 0 76 TOTAL 125.00 < 125 0 252 AVG. 13.89 28 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1,000's $1,000's BUDGET 1,000's 1,000.00 < 1000 0 27.030 189 APPENDIX G. — BEST1.A. columns 13 thru 18. row 11 01 computer printout #OF UNITS TOTAL @INTEGER @INT. RECALC. VALUE UNIT COST MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 0 0.00 CATALOGUE 3 34.50 BANNERS 0 0.00 MAPS 0 0.00 IMPRINTS T—SHIRTS 0 0.00 B. STICK 8 29.60 POSTERS 2 20.00 MENUS 0 0.00 KEYS 3 28.50 TOTAL 1 12.60 AVG. 1 2.51 BUDGET SPENT $1 ,000's 877.10 CONSTRAINTS TOTAL RECALC. $ NON- IMPRSS. LIMIT NEGATIVE < 1200 1.200 0 < 40 6 60 < 40 40 0 < 600 600 O < 30 30 0 < 30 0 80 < 30 10 20 < 30 30 0 < 30 2 72 < 125 12 232 26 BUDGET TOTAL TOTAL LIMIT NET IMPRSS. $1 ,000's BUDGET 1 ,000's 1,000 123 24.122 APPENDIX H BEST 1.B SPREADSHEET FILE; as it represents the optimal solution output file for a $1 million dollar budget and the budget allocation strategy B (high impression media generating strategy strategy). 190 191 APPENDIX H. — Best1.B. columns 1 thru 6. rows 1 thru 3 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000‘s 1 ,000's $1000's $ MEDIA VARIABLES TELEVISION NET 15's 4.850 1.0 4.850 175.00 27.71 NET 30's 5.575 1.0 5.575 225.00 24.78 CAB 15's 3.335 1 .0 3.335 1 10.00 30.32 CAB 30's 4.275 1.0 4.275 155.00 27.58 LOC 15's 545 1.0 545 30.00 18.17 LOC 30's 880 1.0 880 45.00 19.56 TOTAL 1 9.460 19.460 740.00 127.37 AVG. 3.243 3.243 123.33 21 .23 NEWSPAPER FULL PG. 635 2.0 1.270 60.00 10.58 HALF PG. 425 2.0 850 47.00 9.04 6X4“ PG. 165 2.0 330 22.00 7.50 8X4" PG. 190 2.0 380 28.50 6.67 4 PG. INS. 625 2.0 1.250 98.00 6.38 6 PG. INS. 630 2.0 1.260 109.50 5.75 TOTAL 2.670 5.340 365.00 45.92 AVG. 445 890 60.83 7.65 WEEK MG. FR COVER 1.150 3.0 345 100.00 11.50 BK COVER 905 3.0 2.715 85.00 10.65 2nd PAGE 775 3.0 2.325 70.00 11.07 3rd PAGE 745 3.0 2.235 60.00 12.42 EXT. FLAP 950 1.0 950 85.00 1 1.18 TWO PAGE 865 2.0 1,730 75.00 11.53 ONE PAGE 610 2.0 1.220 55.00 1 1 .09 TOTAL 6.000 14.625 530.00 79.44 AVG. 857 2,089 75.71 1 1 .35 192 APPENDIX H. — Best1.B. columns 7 thru 12, rows 1 thru 3 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE TELEVISION NET 15‘s 0.00 0.00 < 2500 2.500 0 NET 30's 0.00 0.00 < 2500 2.500 0 CAB 15's 4.55 500.00 < 2500 2.000 15,159 CAB 30's 0.00 0.00 < 2500 2,500 0 LOC15's 0.00 0.00 < 1000 1.000 0 LOC 30's 0.00 0.00 < 1000 1.000 0 TOTAL 500.00 < 500 0 15.159 AVG. 83.33 2.527 NEWSPAPER FULL PG. 0.83 50.00 < 500 450 1,058 HALF PG. 0.00 0.00 < 500 500 0 6X4“ PG. 0.00 0.00 < 500 500 0 8X4“ PG. 0.00 0.00 < 500 500 0 4 PG. INS. 0.00 0.00 < 500 500 0 6 PG. INS. 0.00 0.00 < 500 500 0 TOTAL 50.00 < 150 100 1.058 AVG. 8.33 176 WEEK MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 1.67 100.00 < 400 300 3.725 EXT. FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 100.00 < 100 0 3.725 AVG. 14.29 532 193 APPENDIX H. — BestI.B. columns 13 thru 18. rows 1 thru 3 of computer printout #OF UNITS TOTAL @INTEGER @INT. RECALC. UNIT COST MEDIA VARIABLES TELEVISION NET 15's 0 0.00 NET 30's 0 0.00 CAB 15's 4 440.00 CAB 30's 0 0.00 LOC 15's 0 0.00 LOC 30's 0 0.00 TOTAL 440.00 AVG. 73.33 NEWSPAPER FULL PG. 1 60.00 HALF PG. 0 0.00 6X4” PG. 0 0.00 8X4" PG. 0 0.00 4 PG. INS. 0 0.00 6 PG. INS. 0 0.00 TOTAL 60.00 AVG. 1 0.00 WEEK MG. FR COVER 0 0.00 BK COVER 0 0.00 2nd PAGE 0 0.00 3rd PAGE 1 60.00 EXT. FLAP 0 0.00 TWO PAGE 0 0.00 ONE PAGE 0 0.00 TOTAL 60.00 AVG. 8.57 CONSTRAINTS $ LIMIT A A A A A A A A A A A A AAAAAAA 2500 2500 2500 2500 1 000 1 000 500 500 500 500 500 500 500 150 400 400 400 400 400 400 400 100 NEGATNE 2.500 2.500 2.060 2.500 1 .000 1 .000 60 440 500 500 500 500 500 90 400 400 400 340 400 400 400 40 TOTAL RECALC. IMPRSS. 13.340 2.223 1 .270 0000 1,270.00 211.67 2.235 319 194 APPENDIX H. — Best1.B. columns 1 thru 6. rows 4 thru 6 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1 000's $ MEDIA VARIABLES MNTH. MG. FR COVER 1.005 3.0 3.015 1 15.00 8.74 BK COVER 885 3.0 2.655 98.00 9.03 2nd PAGE 665 3.0 1.995 77.00 8.64 3rd PAGE 580 3.0 1.740 68.00 8.53 EXT .FLAP 825 1.0 845 95.00 8.68 TWO PAGE 605 2.0 1.210 75. 00 8.07 ONE PAGE 535 2.0 1.070 38.50 13.90 TOTAL 5.1 00 12,51 0 566.50 65.58 AVG. 729 1 .787 80.93 9.37 BUSN. MG. FR COVER 775 2.0 1.550 92.00 8.42 BK COVER 725 2.0 1,450 83.00 8.73 2nd PAGE 665 2.0 1.330 78.00 8.53 3rd PAGE 660 2.0 1.320 72.00 9.17 TWO PAGE 685 2.0 1.370 58.50 11.71 ONE PAGE 595 2.0 1 .190 33.00 18.03 TOTAL 4.105 8,210 416.50 64.59 AVG. 684 1 .368 69.42 1 0.77 CONS. MG. FR COVER 445 2.0 890 82. 00 5.43 BK COVER 400 2.0 800 76.50 5.23 TWO PAGE 355 2.0 710 34.00 1 0.44 ONE PAGE 310 2.0 620 18.50 1 6.76 4PG. INS. 380 2.0 760 65.00 5.85 TOTAL 1 .890 3.780 276.00 43.70 AVG. 378 756 55.20 8.74 195 APPENDIX H. — Best1.B. columns 7 thru 12. rows 4 thru 6 01 computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE MNTH. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 EXT .FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 ' 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 0.00 < 100 100 0 AVG. 0.00 0 BUSN. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 3.03 100.00 < 400 300 3,606 TOTAL 100.00 < 100 0 3.606 AVG. 16.67 601 CONS. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 4PG. INS. 0.00 0.00 < 400 400 0 TOTAL 0.00 < 1 00 1 00 0 AVG. 0.00 0 196 APPENDIX H. — BEST1.B. columns 13 thru 18. rows 4 thru 6 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE MNTH. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 EXT .FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 0.00 < 100 100 0 AVG. 0.00 0 BUSN. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 3 99.00 < 400 301 3,570 TOTAL 99.00 < 100 1 3,570 AVG. 16.50 595 CONS. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 4PG. INS. 0 0.00 < 400 400 0 TOTAL 0.00 < 100 100 0 AVG. 0.00 O 197 APPENDIX H. — Best1.B. columns 1 thru 6. r0w11 of computer printout REACH FREQ. IMPRSS. UNTT COST C.P.M. 1 ,000's 1 ,000's $1 000's $ MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 100 3.0 300 520.00 0.1 9 CATALOGUE 20 3.0 60 1 1.50 1.74 BANNERS 1 1.0 1 11.85 0.08 MAPS 75 4.0 300 143.75 0.52 IMPRINTS T-SHIRTS 1 6.0 6 5.00 0.20 B. STICK 5 4.0 20 3.70 1.35 POSTERS 5 2.0 1 0 1 0. 00 0.50 MENUS 1 1.0 1 4.35 0.23 KEYS 24 1.0 24 9.50 2.53 TOTAL 232 722 71 9.65 7.35 AVG. 26 80 79. 96 0.82 198 APPENDIX H. — Best1.B. columns 7 thru 12, row 11 of computer printout #OF UNITS TOTAL COST SELECTED MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 0.00 CATALOGUE 0.00 BANNERS 0.00 MAPS 0.00 IMPRINTS T-SHIRTS 0.00 B. STICK 8.11 POSTERS 0.00 MENUS 0.00 KEYS 2.1 1 TOTAL AVG. OF UNITS SELECTED $1 ,000's 0.00 0.00 0.00 0.00 0.00 30.00 0.00 0.00 20.00 50.00 5.56 BUDGET SPENT $1 ,000's 1 .000.00 CONSTRAINTS TOTAL IMPRSS. $ NON- LIMIT NEGATIVE < 1200 1,200 0 < 40 40 0 < 40 40 0 < 600 600 0 < 30 30 0 < 30 0 81 < 30 30 0 < 30 30 0 < 30 10 51 < 50 0 132 15 BUDGET TOTAL TOTAL LIMIT NET IMPRSS. $1,000's BUDGET 1,000's < 1000 0 28,899 199 APPENDIX H. — BEST1.B. columns 13 thru 18, row 11 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 0 0.00 < 1200 1,200 0 CATALOGUE 0 0.00 < 40 40 0 BANNERS 0 0.00 < 40 40 0 MAPS 0 0.00 < 600 600 0 IMPRINTS T-SHIRTS 0 0.00 < 30 30 0 B. STICK 8 29.60 < 30 0 80 POSTERS 0 0.00 < 30 30 0 MENUS 0 0.00 < 30 30 0 KEYS 2 19.00 < 30 11 48 TOTAL 48.60 < 50 1 128 AVG. 5.40 14 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1 ,000‘s $1 ,000's BUDGET 1 ,000's 902.00 1 .000 98 25,592 200 APPENDIX H. -- Best1.B. columns 1 thru 6. rows 7 thru10 01 computer printout REACH FREQ. IMPRSS. UNTT COST C.P.M. 1 ,000's 1 .000's $1 000's $ MEDIA VARIABLES RADIO SPOT 15's 58 4.0 232 1 8. 00 3.22 SPOT 30's 73 4.0 292 32.50 2.25 SP. NEWS 44 6.0 264 9.00 4.89 SP. REPORT 28 6.0 168 1 2. 75 2.20 TOTAL 203 956 72.25 1 2.55 AVG. 51 239 1 8. 06 3.14 OUTDOOR 6M LEASE 12X25 6 3.0 18 2.10 2.86 14X48 12 5.0 60 3.15 3.81 ROTARY PLAN 12X25 12 3.0 36 15.60 0.77 14X48 18 5.0 90 17.40 1.03 TOTAL 48 204 38.25 6.47 AVG. 12 51 9.56 2.12 TRANSIT BUS CARD 45 4.0 180 22.25 2.02 POSTERS 65 2.0 130 36. 50 1 .78 TOTAL 1 1 0 31 0 58. 75 3.80 AVG. 55 155 29.38 1 .90 DIRECT MAIL LEAFLETS 5 1 .0 5 0.60 8.33 BROCHURE 25 3.0 75 55. 75 0.45 NEWSLETTER 12 1.0 12 2.25 5.33 TOTAL 42 92 58.60 - 1 4.1 2 AVG. 14 31 19.53 4.71 201 APPENDIX H. — Best1.B. columns 7 thru12, rows 7 thru10 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE RADIO SPOT 15's 0.00 0.00 < 150 1 00 0 SPOT 30's 0.00 0.00 < 150 100 0 SP. NEWS 16.67 150.00 < 150 0 4,400 SP. REPORT 0.00 0.00 < 150 1 00 0 TOTAL 150.00 < 1 50 ' 0 4,400 AVG. 37.50 1 .100 OUTDOOR 6M LEASE 12X25 0.00 0.00 < 200 200 0 14X48 11.90 37.50 < 200 163 714 ROTARY PLAN 12X25 0.00 0.00 < 200 200 0 14X48 0.00 0.00 < 200 200 0 TOTAL 37.50 < 37.5 0 714 AVG. 9.38 1 79 TRANSIT BUS CARD 0.00 0.00 < 300 300 0 POSTERS 0.00 0.00 < 300 300 0 TOTAL 0.00 < 37.5 38 0 AVG. 0.00 0 DIRECT MAIL LEAFLETS 20.83 12.50 < 15 3 1 04 BROCHURE 0.00 0.00 < 200 200 0 NEWSLETTER 0.00 0.00 < 1 5 1 5 0 TOTAL 12.50 < 37.5 25 1 04 AVG. 4.1 7 35 202 APPENDIX H.— BEST1.B. columns 13 thru 18. rows 7 thru 10 of computer printout #OF UNITS TOTAL @INTEGER @INT. RECALC. UNIT COST MEDIA VARIABLES RADIO SPOT 15's 0 0.00 SPOT 30's 0 0.00 SP. NEWS 16 144.00 SP. REPORT 0 0.00 TOTAL 144.00 AVG. 36.00 OUTDOOR 6M LEASE 1 2X25 0 0.00 1 4X48 1 2 37.80 ROTARY PLAN 1 2X25 0 0.00 1 4X48 0 0.00 TOTAL 37.80 AVG. 9.45 TRANSIT BUS CARD 0 0.00 POSTERS 0 0.00 TOTAL 0.00 AVG. 0.00 DIRECT MAIL LEAFLETS 21 1 2.60 BROCHURE 0 0.00 NEWSLETTER 0 0.00 TOTAL 12.60 AVG. 4.20 CONSTRAINTS $ LIMIT 150 150 150 150 AAAA < 150 < 200 < 200 < 200 < 200 < 37.5 < 300 < 300 < 37.5 < 15 < 200 < 37.5 NEGATNE 100 100 100 200 1 62 200 200 300 300 200 1 5 25 TOTAL RECALC. IMPRSS. 4.224 4.224 1 .056 720 720 1 80 105.00 35.00 APPENDIX J BEST 1.C SPREADSHEET FILE; as it represents the optimal solution output file for a $1 million dollar budget and the budget allocation strategy C (Iow impression media generating strategy). 203 204 APPENDIX J. — Best1.C. columns 1 thru 6. rows 1 thru 3 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's $ MEDIA VARIABLES TELEVISION NET 15's 4.850 1 .0 4.850 175.00 27.71 NET 30's 5.575 1.0 5.575 225.00 24.78 CAB 15's 3.335 1 .0 3.335 1 10.00 30.32 CAB 30's 4.275 1.0 4.275 155.00 27.58 LOC 15's 545 1.0 545 30.00 18.17 LOC 30's 880 1.0 880 45.00 19.56 TOTAL 1 9.460 1 9.460 740.00 1 27.37 AVG. 3.243 3.243 123.33 21 .23 NEWSPAPER FULL PG. 635 2.0 1.270 60.00 10.58 HALF PG. 425 2.0 850 47.00 9.04 6X4“ PG. 165 2.0 330 22.00 7.50 8X4” PG. 190 2.0 380 28.50 6.67 4 PG. INS. 625 2.0 1.250 98.00 6.38 6 PG. INS. 630 2.0 1.260 109.50 5.75 TOTAL 2.670 5.340 365.00 45.92 AVG. 445 890 60. 83 7.65 WEEK MG. FR COVER 1,150 3.0 345 100.00 11.50 BK COVER 905 3.0 2.715 85.00 10.65 2nd PAGE 775 3.0 2.325 70.00 11.07 3rd PAGE 745 3.0 2.235 60.00 12.42 EXT. FLAP 950 1.0 950 85.00 1 1.18 TWO PAGE 865 2.0 1.730 75.00 11.53 ONE PAGE 610 2.0 1.220 55. 00 1 1 .09 TOTAL 6.000 14.625 530.00 79.44 AVG. 857 2.089 75.71 1 1.35 205 APPENDIX J. — Best1.C. columns 7 thru 12. rows 1 thru 3 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE TELEVISION NET 15's 0.00 0.00 < 2500 2.500 0 NET 30's 0.00 0.00 < 2500 2.500 0 CAB 15's 0.91 100.00 < 2500 2.400 3.032 CAB 30's 0.00 0.00 < 2500 2.500 0 LOC15's 0.00 0.00 < 1000 1.000 0 LOC 30's 0.00 0.00 < 1000 1.000 0 TOTAL 100.00 < 100 0 3,032 AVG. 16.67 505 NEWSPAPER FULL PG. 0.42 25.00 < 500 475 529 HALF PG. 0.00 0.00 < 500 500 0 6X4“ PG. 0.00 0.00 < 500 500 0 8X4" PG. 0.00 0.00 < 500 500 0 4 PG. INS. 0.00 0.00 < 500 500 0 6 PG. INS. 0.00 0.00 < 500 500 0 TOTAL 25.00 < 75 50 529 AVG. 4.17 88 WEEK MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 2.50 150.00 < 400 250 5.588 EXT. FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 150.00 < 150 0 5,588 AVG. 21.43 798 206 APPENDIX J. — Best1.C. columns 13 thru 18. rows 1 thru 3 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATNE TELEVISION NET 15's 0 0.00 < 2500 2.500 0 NET 30's 0 0.00 < 2500 2.500 0 CAB 15's 1 110.00 < 2500 2.390 3.335 CAB 30's 0 0.00 < 2500 2.500 0 LOC15's 0 0.00 < 1000 1.000 0 LOC 30's 0 0.00 < 1000 1.000 0 TOTAL 110.00 < 100 -10 3.335 AVG. 18.33 556 NEWSPAPER FULL PG. ‘ 0 0.00 < 500 500 0 HALF PG. 0 0.00 < 500 500 0 6X4“ PG. 0 0.00 < 500 500 0 8X4" PG. 0 0.00 < 500 500 0 4 PG. INS. 0 0.00 < 500 500 0 6 PG. INS. 0 0.00 < 500 500 0 TOTAL 0.00 < 75 75 0 AVG. 0.00 0 WEEK MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 2 120.00 < 400 280 4,470 EXT. FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 120.00 < 150 30 4,470 AVG. 17.14 . 639 207 APPENDIX J. — Best1.C. columns 1 thru 6. rows 4 thru 6 01 computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's $ MEDIA VARIABLES MNTH. MG. FR COVER 1.005 3.0 3.015 115.00 8.74 BK COVER 885 3.0 2.655 98.00 9.03 2nd PAGE 665 3.0 1.995 77.00 8.64 3rd PAGE 580 3.0 1.740 68.00 8.53 EXT.FLAP 825 1 .0 8.45 95.00 8.68 TWO PAGE 605 2.0 1.210 75.00 8.07 ONE PAGE 535 2.0 1.070 38.50 13.90 TOTAL 5.100 12,510 566.50 65.58 AVG. 729 1 .787 80.93 9.37 BUSN. MG. FR COVER 775 2.0 1.550 92. 00 8.42 BK COVER 725 2.0 1.450 83.00 8.73 2nd PAGE 665 2.0 1.330 78.00 8.53 3rd PAGE 660 2.0 1.320 72.00 9.17 TWO PAGE 685 2.0 1.370 58.50 11.71 ONE PAGE 595 2.0 1 .1 90 33. 00 18.03 TOTAL 4,105 8.210 416.50 64.59 AVG. 684 1 .368 69.42 1 0.77 CONS. MG. FR COVER 445 2.0 890 82.00 5.43 BK COVER 400 2.0 800 76.50 5.23 TWO PAGE 355 2.0 710 34.00 10.44 ONE PAGE 31 0 2.0 620 18.50 16.76 4PG. INS. 380 2.0 760 65.00 5.85 TOTAL 1 .890 3.780 276.00 43.70 AVG. 378 756 55.20 8.74 208 APPENDIX J. — Best1.C. columns 7 thru 12. rows 4 thru 6 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE MNTH. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 EXT.FLAP 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 TOTAL 0.00 < 150 150 0 AVG. 0.00 0 BUSN. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 2nd PAGE 0.00 0.00 < 400 400 0 3rd PAGE 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 4.55 150.00 < 400 250 5,409 TOTAL 150.00 < 150 0 5.409 AVG. 25.00 902 CONS. MG. FR COVER 0.00 0.00 < 400 400 0 BK COVER 0.00 0.00 < 400 400 0 TWO PAGE 0.00 0.00 < 400 400 0 ONE PAGE 0.00 0.00 < 400 400 0 4PG. INS. 0.00 0.00 < 400 400 0 TOTAL 0.00 < 150 150 0 AVG. 0.00 0 209 APPENDIX J. — BEST1.C. columns 13 thru 18, rows 4 thru 6 01 computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST 3 NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE MNTH. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 EXT.FLAP 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 TOTAL 0.00 < 150 150 0 AVG. 0.00 0 BUSN. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 2nd PAGE 0 0.00 < 400 400 0 3rd PAGE 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 4 132.00 < 400 268 4,760 TOTAL 132.00 < 150 18 4,760 AVG. 22.00 793 CONS. MG. FR COVER 0 0.00 < 400 400 0 BK COVER 0 0.00 < 400 400 0 TWO PAGE 0 0.00 < 400 400 0 ONE PAGE 0 0.00 < 400 400 0 4PG. INS. 0 0.00 < 400 400 0 TOTAL . 0.00 < 150 150 0 AVG. 0.00 0 210 APPENDIX J. — Best1.C. columns 1 thru 6. rows 7 thru10 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1 ,000's 1 ,000's $1000's $ MEDIA VARIABLES RADIO SPOT 1 5'8 58 4.0 232 18.00 3.22 SPOT 30's 73 4.0 292 32.50 2.25 SP. NEWS 44 6.0 264 9.00 4.89 SP. REPORT 28 6.0 168 12.75 2.20 TOTAL 203 956 72. 25 12.55 AVG. 51 239 18.06 3.14 OUTDOOR 6M LEASE 12X25 6 3.0 18 2.10 2.86 14X48 12 5.0 60 3.15 3.81 ROTARY PLAN 12X25 12 3.0 36 15.60 0.77 14X48 18 5.0 90 17.40 1.03 TOTAL 48 204 38.25 6.47 AVG. 12 51 9.56 2.12 TRANSIT BUS CARD 45 4.0 180 22.25 2.02 POSTERS 65 2.0 130 36. 50 1 .78 TOTAL 1 1 0 310 58.75 3.80 AVG. 55 155 29.38 1 .90 DIRECT MAIL LEAFLETS 5 1 .0 5 0.60 8.33 BROCHURE 25 3.0 75 55. 75 0.45 NEWSLETTER 12 1 .0 12 2.25 5.33 TOTAL 42 92 58. 60 14.12 AVG. 14 31 19.53 4.71 211 APPENDIX J. -— Best1.C. columns 7 thru12, rows 7 thru10 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED $ NON- MEDIA VARIABLES $1 ,000's LIMIT NEGATIVE RADIO SPOT 15's 0.00 0.00 < 150 100 0 SPOT 30's 0.00 0.00 < 150 100 0 SP. NEWS 8.33 75.00 < 150 75 2,200 SP. REPORT 0.00 0.00 < 150 100 0 TOTAL 75.00 < 75 0 2.200 AVG. 1 8.75 550 OUTDOOR 6M LEASE 12X25 0.00 0.00 < 200 200 0 14X48 59.92 187.50 < 200 12 3,571 ROTARY PLAN 12X25 0.00 0.00 < 200 200 0 14X48 0.00 0.00 < 200 200 0 TOTAL 187.50 < 187.5 0 3.571 AVG. 46.88 893 TRANSIT BUS CARD 2.13 47.50 < 300 253 384 POSTERS 0.00 0.00 < 300 300 0 TOTAL 47.50 < 187.5 140 384 AVG. 23.75 1 92 DIRECT MAIL LEAFLETS 25.00 15.00 < 1 5 0 1 25 BROCHURE 0.00 0.00 < 200 200 0 NEWSLETTER 0.00 0.00 < 15 15 0 TOTAL 15.00 < 187.5 173 125 AVG. 5.00 42 212 APPENDIX J.— BEST1.C. columns 13 thru18, rows 7 thru10 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATIVE RADIO SPOT 15's 0 0.00 < 150 1 00 0 SPOT 30's 0 0.00 < 150 100 0 SP. NEWS 8 72.00 < 150 78 2,1 12 SP. REPORT 0 0.00 < 150 100 0 TOTAL 72.00 < 75 3 2,1 12 AVG. 18.00 528 OUTDOOR 6M LEASE 12X25 , 0 0.00 < 200 200 0 14X48 59 185.85 < 200 14 3.540 ROTARY PLAN 12X25 0 0.00 < 200 200 0 14X48 0 0.00 < 200 200 0 TOTAL 185.85 < 187.5 2 3,540 AVG. 46.46 885 TRANSIT BUS CARD 2 44.50 < 300 256 360 POSTERS 0 0.00 < 300 300 0 TOTAL 44.50 < 187.5 140 360 AVG. 22.25 1 80 DIRECT MAIL LEAFLETS 25 15.00 < 15 0 125 BROCHUIE 0 0.00 < 200 200 0 NEWSLETTER 0 0.00 < 15 1 5 0 TOTAL 15.00 < 187.5 173 125 AVG. 5.00 42 213 APPENDIX J. —— Best1.C. columns 1 thru 6, r0w11 of computer printout REACH FREQ. IMPRSS. UNIT COST C.P.M. 1,000's 1,000's $1000's 3 MEDIA VARIABLES SPECIAL MEDIA DIRECTORY 100 3.0 300 520.00 0.19 CATALOGUE 20 3.0 60 1 1.50 1 .74 BANNERS 1 1.0 1 11.85 0.08 MAPS 75 4.0 300 143.75 0.52 IMPRINTS . T-SHIRTS 1 6.0 6 5.00 0.20 B. STICK 5 4.0 20 3.70 1.35 POSTERS 5 2.0 10 10.00 0.50 MENUS 1 1.0 1 4.35 0.23 KEYS 24 1 .0 24 9.50 2.53 TOTAL 232 722 719.65 7.35 AVG. 26 80 79. 96 0.82 214 APPENDIX J. —- Best1.C. columns 7 thru 12. row 11 of computer printout #OF UNITS TOTAL COST CONSTRAINTS TOTAL SELECTED OF UNITS IMPRSS. SELECTED s NON- MEDIA VARIABLES $1,000's LIMIT NEGATIVE SPECIAL MEDIA DIRECTORY 0.00 0.00 < 1200 1,200 o CATALOGUE 3.48 40.00 < 40 0 7o BANNERS 0.00 0.00 < 40 40 o MAPS 1.01 90.00 < 600 510 51 IMPRINTS T—SHIRTS 6.00 30.00 < 30 o 24 B. STICK 8.11 30.00 < 30 0 81 POSTERS 3.00 30.00 < 30 0 30 MENUS 0.00 0.00 < 30 30 o KEYS 3.16 30.00 < 30 0 76 TOTAL 250.00 < 250 0 332 AVG. 27.78 37 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT ' NET IMPRSS. $1,000's $1,000's BUDGET 1,000's 1,000.00 < 1000 0 21,169 215 APPENDIX J. --— BEST1.C. columns 13 thru 18. row 11 of computer printout #OF UNITS TOTAL CONSTRAINTS TOTAL @INTEGER @INT. RECALC. RECALC. VALUE UNIT COST $ NON- IMPRSS. MEDIA VARIABLES LIMIT NEGATNE SPECIAL MEDIA DIRECTORY 0 0.00 < 1200 1.200 0 CATALOGUE 3 34.50 < 40 0 60 BANNERS 0 0.00 < 40 40 0 MAPS 1 88.75 < 600 510 50 IMPRINTS T-SHIRTS 6 30.00 < 30 0 24 B. STICK 8 29.60 < 30 0 80 POSTERS 3 30.00 < 30 0 30 MENUS 0 0.00 < 30 30 0 KEYS 3 28.50 < 30 0 72 TOTAL 241.35 < 250 9 316 AVG. 26.82 35 BUDGET BUDGET TOTAL TOTAL SPENT LIMIT NET IMPRSS. $1 ,000's $1 ,000's BUDGET 1,000's 920.70 1.000 79 19,018 IES "‘IiiiiiliiTiiIIIiIIII-i"