Construct a model use solver when applicable to construct the model

 Construct a model
use Solver when applicable to construct the model

Case information:

Last month the CEO of the Australian Banking Corporation (ABC), Pierre De Posit, had sat at his desk shuffling through endless reports and the graphs showing ABC’s declining share of the business banking sector. Pierre thought this ‘trend’ may, in some way, have been connected to a lack of effective online advertising by ABC. With this in mind, he had moved quickly to commission a study into the effectiveness of advertising on internet sites – in particular he wanted to know about the internet sites that specialised in reporting news that was of interest to business men and women.

One of the first steps in the study commissioned by Pierre was to bring together a focus group of business banking customers. This group of 30 people was carefully selected to represent the market as a whole without any significant bias. The focus group was asked to indicate which internet news sites (out of the ten most popular sites) they visited regularly and on which they could recall at least one advertising message. The suggested correlation was that these sites would be the most effective sites on which ABC could advertise to reach and capture the attention of the decision-makers in the business banking sector.

The Excel file entitled “QBM Individual Assignment – Australian Banking Corporation) – Raw Data” was created as part of the initial stages of the study. This file contains:

• • Data showing which of the ten internet news sites (annotated as site “A” through to site “J” in cells D6:M6) where visited regularly by each of the focus group members (annotated as member 1 through to member 30 in cells C7:C36). The number “1” was used to indicate a site that was visited, and the number “0” otherwise (see cells D7:M36). You should note that several focus group members visited more than one site regularly.
• • The current cost per week of advertising on each of the ten potential internet news sites (see cells D5:M5).
• • 37 months of historical data regarding the month-close value of an ‘online advertising cost index’. This index is a unitless indicator of the change in the prices (i.e. the costs to customers) in the internet advertising market month by month. For example, if the index falls, say by 1.5%, then that indicates online advertising prices in the relevant month (after necessary normalisation) were 1.5% lower than those in the preceding month.

Pierre is unsure of how much money to commit to advertising on internet news sites in order to quell ABC’s market share slide in the business banking sector, but he considers that a budget of $12,500 per week might be adequate.

Armed with the current data, Pierre would now like to know: (i) which internet news sites should be selected for ABC advertisements; and (ii) what percentage of potential customers will be reached via such an advertising campaign. However, to complicate matters he also does not want to count multiple exposures by the same persons to ABC advertisements. This means that if a customer were to visit multiple internet news sites on which ABC has placed advertisements, then that customer should only be counted as having been reached on the first instance he/she visits an internet news sites on which ABC has placed advertisement – he/she cannot be counted again.

Answer instruction:

YOU ARE REQUIRED TO:

1. (a) construct a model (i.e. an Excel spreadsheet without any VBA Macros) to adequately analyse the ABC situation, then determine the internet news sites on which ACB should place its advertisements and the percentage of potential customers that will be reached via such an advertising campaign.
2. (b) write a report (i.e. an MS Word document) that is no longer than two A4 pages to support your model and explain your findings. It should be noted that this document is equally as important as the spreadsheet and will be graded. This report is not to be a model map, but rather it is to be a supporting document that clearly summarises:
3. (i) how your model operates (i.e. a clear explanation and justification of any core inputs, assumptions, forecasts, calculations, etc);
4. (ii) the results you have determined (including discussion around any scenario and/or sensitivity analysis that you have chosen to conduct);
5. (iii) your advice to the CEO of ABC in regard to the advertising decision.
6. Please use only the information provided on the case information and the spreadsheet attached
7. All the answers in the case information and answer instructions must be clearly ansered

Attachments area