The Gains from Advertising - Assignment Example

Instructional Manual 1. Modeling the spreadsheet a. When you open the spreadsheet, type what each variable represents. Let A represent Full daily papers, Let B represent Evening News TV spot, Let C represent Radio Spot, Let D represent Facebook advertisements. The next step is to create a coefficient table. b. Formulate the objective function. We can either maximize the gains from advertising by ensuring the highest value of impacts or minimize the costs incurred. The TV spot has the highest level of impact but also incur the highest costs. In order to get a clear picture during optimization, we can rank the impact and cost effectiveness of the different Medias by dividing the unit costs by the level of impact, in order to estimate the cost for every impact that each advertisement media has. Full-page daily paper 1,400/75 = 18.7 Evening News TV spot 3500/100 = 35 Radio Spot 310/60 = 5.17 Face book advertisement 500/50 = 10 This is inserted in cells F4;F7 From this information, it can be seen that Radio spot is the most cost and impact effective, whereas Evening News TV spot is the most expensive in terms of cost per impact. The objective is to maximize impacts. The highest impacts can only be achieved using the most cost effective advertisements. This includes the Radio spot and Face book. However, there are some constraints on the minimum number for daily paper and TV. c. Formulate your constraints Introducing the relevant limits changes the allocation. These limits are as follows Paper 048 TV 2856 Radio 56 280 Facebook D= 28 These are input into cells E4 to E7. d. Specify the objective function: Determining the number of advertisements Introducing the relevant limits changes the allocation. These limits are as follows Paper 048 TV 2856 Facebook= 28 Radio 56< X> 280 The number of adverts will be derived from the minimum and attempt to meet the available funds’ limitation as follows. TV 28 * 100 * 35 =98,000 Radio 56* 60 * 5.17 = 17,371 Facebook 28* 50 * 10 = 14000 Total = 129,371 Balance = 28,629 Paper = 28,629/ 18.7 = 1511.7, 1511.7/75 = 20 Advertisements This balance can either be directed towards Newspaper which has no minimum in order to include all Medias, or included in radio advertisements which have a high impact. Advertisement media Number of advertisements TV 28 Radio 56 Face book 28 Paper 20 These are inserted into cells I36 to I39. e. Introducing the target cell The target cell will represent the objective, which is to find the number of advertisements for each media which will maximize the level of impact. The cell which is designated as target or objective function is G4 in sheet 2. The next step involves finding the formula to calculate the number of impacts in cell G4 (sheet 2). This is the sum of unit impact rating * the number of units purchased for each medium (number of newspaper ads * impact + number of TV ads * impact + the number of radio ads * impact + the number of facebook days * impact). This formula is =SUMPRODUCT(B3;B4,C3;C4,D3;D4,E3;E4). f. invoke the optimizer We find the solver add in in data tab in the excel spreadsheet and input cell G4 (see sheet 2), which is our target cell/ objective function. Before hitting the enter icon, we must find the decision variable and the constraints. g. Specifying decision variables The numbers of respective mediums are decision variables in this case. This are known as changing files in excel. Here, select B3:E3 (Sheet 2). To ensure non-negativity values, we chose >= in the constraint dialog box and select zero as the constraint. h. Specify the constraints and model type Therefore B3….E3 >=0. Other constraints include the limits on the number of mediums. In this case we select B3:E3 in the cell reference, and input B8:E8 in the constraint region, leaving the dividing sign as Read More