A healthcare company has developed a new self-testing kit for an infectious disease. It is considering how to maximize the expected sales revenue through a number of online advertising channels, subject to its given budget of $1.5 million. The selling price has been decided at $100 per pack. Three online advertising channels have been considered for placing advertisement (ad.) and the unit of advertising is different in each channel. A unit of ad. placed on an online newspaper is one day while in Facebook is one post. To maximize exposure, a minimum number of units (of ad.) will be placed with each channel. They can also provide the average number of viewers for each unit placed. Among those who have viewed or interacted with the ad., the percentage who has actually made a purchase can be calculated and named as the conversion (or purchase) rate. The relevant data for each advertising channel is given below: From the table, if the company purchased 1 day of advertisement with the online newspaper, the cost is $40,000. This is expected to reach 400,000 viewers, out of which 12,000(=400,0003%) will become actual customers. For simplicity, it can be assumed that each customer will buy 1 pack of self-testing kit at the price of $100. Hence, the expected sales revenue would be $1,200,000(=12,000$100). (a) Formulate the problem as a linear programming model to determine the advertising plan that would maximize the expected sales revenue. [8 marks] (b) Set up a spreadsheet model for (a) and solve for an optimal solution using Excel Solver.[11 marks] (c) Identify the binding constraints in (b). What is the ratio of expected sales revenue to total advertising cost? [5 marks ] (d) Suppose the company would like the ratio in (c) to reach at least R(>0), how much should the current budget be increased/decreased? (i) Modify the model in (a) such that it still preserves the linearity property. [5 marks] (ii) Based on d(i), modify the spreadsheet model in (b) to find the increase/decrease in the current budget and the resulting optimal solution for R=34,38 and 40 , respectively. [9 marks ] A healthcare company has developed a new self-testing kit for an infectious disease. It is considering how to maximize the expected sales revenue through a number of online advertising channels, subject to its given budget of $1.5 million. The selling price has been decided at $100 per pack. Three online advertising channels have been considered for placing advertisement (ad.) and the unit of advertising is different in each channel. A unit of ad. placed on an online newspaper is one day while in Facebook is one post. To maximize exposure, a minimum number of units (of ad.) will be placed with each channel. They can also provide the average number of viewers for each unit placed. Among those who have viewed or interacted with the ad., the percentage who has actually made a purchase can be calculated and named as the conversion (or purchase) rate. The relevant data for each advertising channel is given below: From the table, if the company purchased 1 day of advertisement with the online newspaper, the cost is $40,000. This is expected to reach 400,000 viewers, out of which 12,000(=400,0003%) will become actual customers. For simplicity, it can be assumed that each customer will buy 1 pack of self-testing kit at the price of $100. Hence, the expected sales revenue would be $1,200,000(=12,000$100). (a) Formulate the problem as a linear programming model to determine the advertising plan that would maximize the expected sales revenue. [8 marks] (b) Set up a spreadsheet model for (a) and solve for an optimal solution using Excel Solver.[11 marks] (c) Identify the binding constraints in (b). What is the ratio of expected sales revenue to total advertising cost? [5 marks ] (d) Suppose the company would like the ratio in (c) to reach at least R(>0), how much should the current budget be increased/decreased? (i) Modify the model in (a) such that it still preserves the linearity property. [5 marks] (ii) Based on d(i), modify the spreadsheet model in (b) to find the increase/decrease in the current budget and the resulting optimal solution for R=34,38 and 40 , respectively. [9 marks ]
