¡Descarga Brain Cell Internet Advertising y más Ejercicios en PDF de Econometría solo en Docsity!
Copyright © 200 8 by DecisionPro, Inc. To order copies or request permission to reproduce materials, go to www.decisionpro.biz. No part of this publication may be reproduced, stored in a retrieval system, used in a spreadsheet, or transmitted in any form or by any means – electronic, mechanical, photocopying, recording or otherwise – without the permission of DecisionPro, Inc. MARKETING ENGINEERING FOR EXCEL EXERCISE VERSION v1 60208
Exercise
BrainCell Internet Advertising • Solver
By Arnaud De Bruyn
The BrainCell Internet Advertising exercise is a standalone spreadsheet with preset formulas and result displays. To use it, simply open the file “ BrainCell Internet Advertising Data (Solver).xls”. By default, the file installs in “ My Documents/My Marketing Engineering/ ”.
Overview
This exercise and the associated spreadsheet are intended to familiarize you with building formulas in Excel, teach you to use Excel’s Solver function, and introduce the concept of response functions.
Introduction
BrainCell is positioned to sell cell phones, call plans, and mobile services to end-user customers using the Internet exclusively as its unique sales channel (though it also will employ call centers to some extent, mostly for technical support and billing questions). Partnering with one of the largest available communication networks in Europe, BrainCell follows the same business strategy for its cell phones that Internet banks use with their banking operations: reducing its operational costs through the absence of physical branches and local infrastructure and then using these saved costs to reduce prices and increase competitiveness. As a relatively new offering, one of the challenges facing the company is identifying and targeting prospective customers. Market research shows that the customers who are most likely to go to the Internet to fulfill their mobile communication needs tend to be well educated, wealthier than the average population, heavy Internet users, and already technically familiar and equipped with cell phones. Given this target population, it seems natural (and cost effective) to use the Internet as a key communication channel to advertise the offerings and launch a promotional campaign. Although BrainCell intends to enter all 25 countries of the European Union eventually, its offering will launch first in the 6 largest countries: Germany, France, the United Kingdom, Italy, Spain, and Poland, which contain approximately 350 million inhabitants. According to Internet usage, cell phone penetration, and national income market research, some of these countries appear much more attractive than others for BrainCell’s offering. Initial tests and extensive market research indicate several interesting findings. Although English remains the primary international language, it is
much more effective to advertise in the specific language of each target country. Advertising online in English is much more expensive than advertising in Polish or German, because online competition is fiercer in English (and, to some extent, Spanish), so Internet advertising costs rise exponentially (especially for services such as GoogleAds, for which advertisers must bid to get good ad placements). Proportional to its costs, Internet advertising is more effective in French or German than in English, and BrainCell must take this effect into account to measure the cost effectiveness of its Internet campaigns. In addition, the transformation rate for clicks is lower in English; for example, U.S. customers might see an online ad, follow the link (generating costs for BrainCell, which often pays by the click), then realize the offering is not intended for U.S. customers, and leave the site without purchasing. BrainCell recently began to develop promotional response model tools to help it decide the appropriate level and allocation of Internet advertising across countries. It started with a prototype spreadsheet to encourage discussion about the appropriate level of Internet advertising for the six targeted European countries and to familiarize marketing managers with Marketing Engineering and the related software tools and ideas. In this case, the spreadsheet was developed to both socialize the ideas and help managers understand response modeling and optimization with Excel’s Solver tool. To get feedback about the value of the prototype, the developers purposely left the software incomplete. As a marketing manager, you must complete the missing cells in the spreadsheet (gross margins, acquisition costs, ROI, etc.) using Excel formulas. In addition, the BrainCell marketing team, along with the advertising agency and Internet consultants, has conducted a judgmental calibration exercise to provide background for the promotional response modeling. In essence, the group considered the following questions: How many customers would BrainCell acquire within its first six months of operations if the company spent: Nothing? 50% less than current amounts? Current planned amounts? 50% more than current amounts? An unlimited amount? Using the brand managers’ answers to these questions, the software constructs a response model, which relates the amount of Internet advertising to the number of new customers acquired as a result of that spending. The training session is designed to accomplish several goals: Familiarize you with building formulas in Excel. Introduce you to the functionality of Excel’s Solver tool. Introduce the concepts of response functions and judgmental calibration. Produce a preliminary advertising budget (and some sensitivity analyses) for these six European countries. Provide design feedback for a more complete, operational decision support tool. Note: The only cells you should change when running Solver are Cells C 18 – H18.
Getting Started Open the file “ BrainCell Internet Advertising Data (Solver).xls ” in “ My Documents/My Marketing Engineering/. ” Step 1 Understanding the model In Excel, click on the BrainCell sheet. This sheet contains a simple business model spreadsheet that leads to estimates of net profits for six different European countries.
Base scenario (rows 5 and 6) contains information about the initially planned budget and expected results. For instance, the company plans to spend 187,500€ in the United Kingdom and acquire about 12,3 00 new customers within the first six months. These cells do not need to be changed. Use them as a reference point only. Market research data (rows 7–10) contain key figures about the target markets. This information comes from various sources and has no direct impact on the model. Note that mobile phone penetration can go higher than 100%, because some customers have more than one cell phone. This phenomenon is not taken into account. Estimated target population (row 11 ) represents how many potential BrainCell customers there are in each country. These figures are based on market research data and should be used later to estimate the true size of the market (e.g., compute market shares). Estimated gross margins (row 12 ) per acquired customer represent average revenues minus the costs of serving a new customer during the first six months of its relationship with the firm. This figure does not take into account acquisition costs (i.e., advertising). Recommended scenario (rows 1 8 and 19) contains the recommended planned advertising budget and number of customers acquired as a result of that campaign. Cells C18–H18 should become the target cells of Solver. Cells C19–H19 contain the response functions. Gross margins , Net margins , Acquisition cost per customer , Return on investment , and Market share (rows 20 – 24) must be filled in manually using Excel formulas. Note: Return on investment is defined as (gross margins / costs) – 1, or (net margins / costs).
Step 3 Using solver You must determine the “optimal” spending level that maximizes Total Net Margins (cell C 28 ). (Your spending level for each of the six countries must be greater than or equal to zero.) Use Solver to perform this task. In the Excel menu, select TOOLS SOLVER and select the cells as follows (if the SOLVER option does not appear under the TOOLS menu, read the last section of this document to install it): Solver should have these cells selected: Target Cell is the cell that you want to maximize, in this case Total Net Margins, in cell C2 8. Changing Cells are those cells that contain decision variables; in this exercise, the decision variables are in cells C 18 – H18 and represent the levels of Internet advertising in each country. Constraints must be added to the decision variables because you cannot spend less than zero. Click Solve. In some cases, the Solver run in Excel will not converge or will converge to a solution that is not optimal. You then may have to provide Solver with new starting values. It is usually a good idea to restart any new optimization from the base scenario. Note: If you set up the Return on Investment formulas correctly, setting a budget to zero will create a Divide by Zero error in the spreadsheet. To avoid this problem, create a constraint in Solver that the advertising budget in each country must be greater than 0.01 or another very small number.
Step 4 Recalibrating the response functions Recalibrate the response curve for various countries and see how it affects the recommendations. Changing the values in the Calibration spreadsheet is not sufficient. Instead, you must also run a calibration analysis of the newly entered data using the Marketing Engineering for Excel resource allocation software. To recalibrate a response function, follow these steps: Open the Calibration spreadsheet. Update the data.
After you enter consensus estimates for different “what-if” scenarios for
each country, click on MEXL RESOURCE ALLOCATION CALIBRATE RESPONSE CURVES, and follow the instructions. Refer to the resource allocation tutorial for details. Because Internet advertising is individually targeted at customers, hence bears direct influence on those reached, we suggest to use an exponential function when calibrating. Note: The software will create a worksheet called "Book1.xls" that includes graphs of the response functions, such as: You must return to the sheet called "BrainCell Internet Advertising Data (Solver).xls" to complete the exercise.
