The following are the job requirements:
5.5 Mail Advertising Sheet
In the lead up to the new season, the theatre plans to run a mail campaign that will encourage new people to sign up to an annual ticket to the theatre. To advertise this, they plan to target several Brisbane areas in the campaign. You are required to calculate the number of potential new customers in each month, and collectively. You also need to calculate the associated advertising costs so that you can determine the cost per customer to determine how long the campaign should run for to be the most cost effective.
For this you will create three two-variable data tables. The data tables are comparing the different suburbs in Brisbane, the number of properties in the suburb, with the month of the campaign, ranging from one to ten months. Use the following information to calculate costs and customer numbers.
Each flyer costs $0.183 to be delivered by Australia Post.
o The discount offered to new customers on the flyers is $35.
o Based on Brisbane City data, each property has on average 2.6 people living there. Each of these is considered a potential customer.
o In each month of the campaign, flyers will be sent to all households in the region. o The cost of printing flyers is defined on the Constant Sheet.
o It costs $1000 for each new flyer to be produced. A new flyer is needed each month for each suburb.
o The predicted effectiveness of the advertising campaign in a particular month is calculated using the formula:
o effectiveness = 0.01(1 + (0.1(1 − month)))
o The total predicted effectiveness of the advertising campaign by a month is calculated using the formula: o effectiveness = −0.0005month2 + 0.0105month
Calculate the total cost for each month, total predicted customer increase for each month and the cost per customer for each month. In the cost per customer section, use red-yellow-green colour scale conditional formatting to highlight the best option with green being the best.
5.6 Advertising Campaign Sheet
This sheet is currently empty. On this sheet you will need to set up scenario manager to run a number of scenarios that will predict the costs and the number of new customers for running a radio advertising campaign. Use the following information in calculating the costs and new customers:
o The Brisbane radio audience on the selected station is 56,250 people.
o Each month a new radio advert needs to be produced at a value of $5,000.
o The discount offered to new customers remains at $35.
o Radio advertising can be purchased in blocks of 20 or 50 per month. These each cost $500 and $400 for each play of the advert, respectively.
o The total predicted effectiveness of the radio advertising campaign based on the total number of adverts played is calculated using the formula:
o effectiveness = −0.0000007adverts2 + 0.0008adverts
Set up the sheet to run the following scenarios. The scenarios need to calculate the total costs of the advertising campaign, the total predicted increase and the cost of gaining each new customer. There are twelve scenarios to consider. Save the results of the scenario analysis on a new sheet. It should contain meaningful labels for each of the values altered (not cell references).
The twelve scenarios are all combinations for the following situations:
5.6.1 Number of Months (2)
The advertising campaign can run for 3 or 6 months.
5.6.2 Amount of Radio Advertising (2)
The amount of radio advertising can be low or high. Low is 20 adverts each month and high is 50 adverts each month.
5.6.3 Uptake Scale (3)
To account for errors in the predicted customer uptake, there are three scaling factors that will affect the effectiveness of the campaign. These scaling factors are low – 75%, expected – 100%, and high – 125%.
The theatre has eleven different periods for running performances during the Mainhouse season. To best match the performance with a period, the cast listed their preferences from 1 (the most desirable) to 11 (the least desirable). Your job is to allocate the periods so the sum of the preferences scores is minimised but also ensure that each performance receives no worse than their 5th choice. Two performances have special needs and have requested a particular session – Terry Pratchett’s Maskerade needs to occur in the “April to May” period and Picnic at Hanging Rock needs to occur in the “September to October” period. To complete this task, you should use Solver.
The sheet you have for this contains two tables. The first table indicates each performance’s preferences for different periods. The second table indicates the assignments of the periods to different performances. A value of 1 in the table indicates that the period has been assigned to the performance. A value of 0 indicates that the period has not been assigned. Only one period can be assigned to each performance and each performance can only reserve one period; each row and column of the table will have only one cell containing the value 1 and the rest of the cells will contain the value 0.
The values in the preference value column should be no greater than 5 indicating each performance will receive no worse than the 5th best choice. The preference total cell contains the sum value of these scores.
You are required to save the result of the Solver to an Answer Report and return the sheet to just before you ran Solver.
0 comments