Saving the Blob fish

Documentation
The spreadsheet model- Saving the Blob fish has been constructed for the use by employees of FoGAFiDE and Mr. Angus Dei.
Created on September 30, 2012 by Neha Bedi

Blob fishes are currently facing extinction due to deep sea fishing and the motive of FoGAFiDE is to support a bill to restrict deep sea fishing. Thus, to get this message circulated through public, mass media approach was considered. The purpose of this spreadsheet model is to allocate funds of $158000 to different advertising alternatives in a way that will maximise the impact achieved.
This particular model can be used for determining how much funds should be allocated, considering the constraints, to each advertising medium in order to optimise average impact. It also helps in finding out the total and individual costs of advertisements.

Layout of model (in blocks): 1) Requirements- minimum and maximum amount of ads required can be entered in this block for each advertising medium. The Cost per unit of each medium is also entered in the requirements area. 2) Resources available- This is the funds available section and would act as a constraint so the advertisements price doesn’t increase the funds available. 3) Calculation area- when solver is run this part of spreadsheet shows the number unit of advertisements that should be bought and their total cost price 4) Report area that tells the impact of each advertisement and the average impact.

Instructions: 1) Enter the variable minimum and maximum amount of ads required for each type of advertising and their variable cost prices respectively. All three have to be allocated in their respective columns B,C and D. Only numbers or currency can be entered in this area of the spreadsheet. 2) Enter the variable amount of funds to be allocated in the resource availability block in cell C11. This cell is formatted to currency format. These amounts can be changed if needed and solver can be re-run.
Note: the variable amounts from step 1 & 2 can be changed to suit your requirements 3) Leave the changing cells blank as solver will determine those.
Next, Go to the data tab and click on Solver. Set objective to $B$29. The target cell represents the average impact rating per unit of advertising purchased. There is also a cell note that shows when you let the cursor hover on the top right hand corner of the C19 cell that says it’s the target cell.
Note: any cell that has a little red triangle on the top right hand corner has a comment that states the nature or content of cell.

For equal to: click “max”.
By changing Variable cells: Select the Unit of ads bought cells reference B15 to B19.

For the current information available, constraints have already been entered and the solving method used is GRG Nonlinear. These constraints can be changed if needed by simply deleting the constraints that are not needed and adding new ones through the change, add and delete options.
Tick the ‘make unconstrained variables non-negative’ option.

Note: In options of the Solver window, make sure that ignore integer constraints has been UNTICKED.

When you click on Add constraint, the above window pops-up. Click on the cell reference box and select the cell you are referring to and select whether the value should be greater than, lesser than, zero or integer. Next click on constraint box and select the cell that contains relevant information of the constraint. For e.g.: to restrict the maximum number of advertisements of radio to less than 280. Lastly, click Ok and Solve.

Then the following window will pop up and state that a solution had been found. Click ok and solver returns your optimal solution in the changing cells and optimal objective of average impact in the target cell. The ‘Resources left’ column D shows the funds that are not needed.

The unit impact ratings have already been entered in cell B23 to B26.
The formulas are already entered into the worksheet to calculate the cost of advertisements, impact of advertisements and the final average impact (displayed in cell B29).

The average impact rating per unit of advertising purchased (i.e. total impact/total number of units).

Note: Keep a back up copy of the file on a USB or secondary hard drive in case of information loss e.g.: accidental deletion of file.

Assumptions for this spread sheet model are: 1) The impact rating cannot be changed. 2) The minimum advertisements requirement for daily paper and Facebook advertisements is zero. 3) There has to be one Evening news TV spot about Blob fish every day for 28 days. 4) The minimum advertisements required for Evening news and Radio spot are 28 and 56, respectively. 5) The maximum advertisements required are Evening news-28, The Age Paper-48, and the Radio spot-280. 6) The maximum funds available are $158000. 7) The age paper costs- $1000, Evening news costs- $4000, Radio spot costs- $310 and Facebook ads cost- $500. 8) The figure below shows all the formulas and functions used to derive the solution.
E.g.: average impact= total impact of adverts/total units of ads bought
Cost of advert= unit of adverts bought*cost per unit of advert
Target cell= sum of costs of all advertisements

All the formulas used in the spreads sheet model can be accessed by pressing Ctrl + ‘ together.

Names Used in spread sheet- can be accessed from Formulas tab under ‘Names manager’.

Question 2: Optimal solution
The optimal solution or the average impact rating for this model is 74 (Refer to BlobfishQ1 sheet of the workbook). Therefore, if the Angus purchases the units of advertisements suggested by solver, it will reach (impact) about 74 people in the audience out of a total of 100.
Thus, to get this impact Angus should advertise in:
28 units of The Age paper
28 units of Evening news
56 units of Radio Spot
0 units of Facebook advertisements
The total cost for these advertisements would be $157,360 and unused resources of $640.

Question 3: If Unit price is negotiable on one type of minimum number of advertisement According to the solution obtained from Solver, The number of Radio spots and Facebook ads have been restricted a minimum of 56 and 0 respectively.
The price elasticity of demand measures the responsiveness of quantity demanded to a change in price all other factors being constant. The quantity demanded is elastic if the price change causes a change in quantity demanded. It is inelastic when the price change does not affect or causes less change in the quanity demanded.
Suppose Angus can negotiate a lower price of $200 on the radio spot. The new value in entered in cell D7 and the solver is re run. There is no change observed in the radio spot units but it does increase the units of the daily paper. Thus, Angus is not able to buy more these advertisements.

The reason for the demand being relatively inelastic in the case of Radio spots might be due to factors such as: * Availability of substitutes such as The Age Paper and its impact rating being higher compared to radio spots. * The degree of necessity of the advertisement may also affect the demand. Since there are other advertising options available, radio spots are not particularly necessary as the minimum requirement has already been met. * The funds available being restricted to $158000. Since this advertisement has the approx. medium impact rating, Angus might not be able to reach more audience by purchasing extra Radio spots. Therefore, even if he can negotiate a lower unit price on the Radio spot, he still might not need to buy more of these advertisements.