Need help on completing this case via excel all info I have is screen-shotted below

' Fitness Centers Ca 5.; 6: Decision Support Using Excel Selecting the Best Mix of Advertising Media PREVIEW In this case, you will use Excel's "what if " capabilities to help Lifetime Fitness's marketing management decide how to advertise tness center memberships. The question is: What mix of media should be used to maximize ad campaign net income? BACKGROUND Lifetime Fitness is headquartered in Syracuse, N. Y. The company owns and operates a number of fitness centers in metropolitan Syracuse. In those fitness centers, many lockers currently go unused. Many added members could be easily accommodated. Ellen Goldstein, vice-president of marketing at LFC, wants to increase membership in the metro Syracuse centers by running a one-year advertising campaign. She wants to know what sort of advertising media to use, and how many advertisements in each media to place, to maximize net income. More information about LFC is given in Company Background. Advertising Options for Lifetime Fitness Here are the different kinds of advertising media that Ellen wants her staff to consider. Radio A local FM station has a popular Sunday afternoon show on physical fitness. Key people from the local sports, sports medicine, and tness scenes are interviewed on the show. Lifetime Fitness could take out one (SO-second advertising spot in each weekly show. TVInfomerciai The local cable service is required by law to set aside a public access channel. Any local organization can buy time on this channel. Time slots are allocated in half-hour chunks. Some companies (local and national) have been airing half hour infomercials on this channel. An infomercial looks like a TV show when you first tune it in. It is scripted, and often a well-known person appears praising the product and its virtues. These shows often seem corny, but this kind of promotion has improved sales for some products and some companies. A Lifetime Fitness infomercial could be developed. to show the benefits of membership. Sunday Newspaper LFC could place an advertisement in the local Sunday paper -- specifically a one page insert included with the many supermarket. department store, and other such inserts. Such ads often get thrown out, unread. But Ellen thinks such an insert would be better than an ad in the Sports section or the Style section, because these sections do not cater equally to both genders. inrernet Portal The company's logo could be prominently displayed in a banner ad on an Internet "portal". A portal is an Internet site that acts as a gateway to network commerce. An advertiser pays the portal company to prominently display the advertiser's logo on the portal's home page (or, less desirably, on some other page that might be accessed). Today it is increasingly common for shoppers to start their Internet shopping at a portal site, where they can click the banner ad of interest, and be transported to the seller's web site. Acting as a portal is one way AOL, Excite, Yahoo!, and other big web sites are now making money. Ellen thinks that LFC could contract with Bombarrlcom, a portal that serves many businesses in central New York. People in the area increasingly are using Bombasr! to nd out what is going on at the local malls, movie theaters, car dealerships, and so on, LFC would pay a certain amount per day to have their banner ad shown on Bombasr! People would see the ad, and might click the LFC banner. They would then be linked to LFC's home page, where they would read about the tness services offered and sign up. General Advertising Concepts Here are some advertising denitions and concepts that will help you understand Lifetime Fitness's problem better. Any marketing promotion has a theoretical maximum target audience. For example. the theoretical target audience for a Sunday paper might be the number of people over a certain age, in the distribution area; i.e., those who could pick up and read a paper on Sunday. This maximum might be quite large. But, the theoretical maximum is just that -- theoretical. Only a cenain percentage of people are actually likely to see the ad. Not everyone (to continue the example) reads the Sunday paper every week. A city's Sunday paper could possibly be bought by a million people each week. but experience might show that an average of only a half million Sunday papers are read. An elfective exposure means that the promotion, if seen, is actually attended ta. To return to the Sunday paper example, many newspaper readers simply throw out ad supplements that come jammed into the paper. Or they scan ads too huniedly to actually pay attention. So, a Sunday paper could be bought by a half million people each week, but there might only be 10,000 "effective exposures" to a particular ad -- in other words, only that number of people would actually pay attention to it. And even with an effective exposure, there is no guarantee that the person will actually buy what is advertised. To estimate revenue generated by the ad, advertising executives must estimate the probability of acting on an effective exposure. To continue the example, if there were 10,000 effective exposures to an ad, and the chance of acting on it was 10%, then 1,000 new sales would be expected. Different sorts of media target different kinds of consumers. A person who sees an advertisement for golf clubs in the Sunday paper in America's heartland might be willing to spend $400 on a new set. But the kind of person seeing an ad for golf clubs in a more upscale magazine might be willing to spend $1,000 a new set of (presumably better) clubs. Ellen thinks the kind of people who are likely to buy a membership in a tness center are people with some disposable income (though not necessarily wealthy people), those with some free time, and people intelligent enough to appreciate the benets of tness. Such people are likely to read the paper, use the lntemet, listen to FM radio, andlor watch cable TV. Established Benchmarks for Promotional Media in Central New York State Syracuse is not a huge city, and not even half the population is over 18. The maximum audience that could see the Syracuse Sunday paper is 100,000 people. The FM radio station drinks that the maximum possible audience for their weekend shows is 10,000 people. The maximum infomercial audience for the local access station is 100,000. Bambast! executives say that 25,000 people might see their web site on any given day. About half the possible readers actually read a Sunday paper. The radio show has a fairly loyal following but not everyone tunes in each time it is aired. Infomercials are sometimes aired at odd times, so potential audiences may not tune in; actual viewership is low. Not everyone who could use the Intemet logs in every day, and those that do are often in a hurry. The percentages of people who might actually see the ads or spots are as follows. Paper -- 50% Radio -- 30% Infomercial -- 1% Portal - 10% Radio and Infomercial audiences are self primed. in a sense -- the people would not be tuning in if they were not in the "fitness mood." And a web surfer who has landed at the Bombast! portal must be in the mood to shop for something; maybe he or she will click on Lifetime. Percentages of effective exposures are as follows: Pape -- 3% Radio -- 20% Infomercial -- 20% Portal - 10%. Expected percentages for actually acting on an effective exposure are: Paper -- 1% Radio -- 5% Infomercial -- 5% Portal - 10%. Costs Media costs vary widely. A newspaper yer is cheap to design -- $200. A radio spot is not expensive -- a local advertising agency is willing to prepare one for $500. Infomercials are more expensive to stage and lm. The local ad agency can do one for $5000. The cost of the portal spot is also high, because the web page would have to be developed and programmed. A local software company would do this for $2000. These development costs are one-time costs, of course. For example, once the radio spot is developed, it can be used over and over again. The costs of placing each ad or spot also vary. The paper would charge $1000 each Sunday that Lifetime flyers were printed by the paper and inserted. The cost of placing a 60-second radio spot is $2000. An infomercial can be run for $3000 per showing on the local cable access channel. The Internet portal will charge $7500 for each day that Lifetime's banner is shown on the Bombast! front page. This is high, but the front web page on a portal is prime network real estate, and the Ellen does not think space on secondary web pages is worth renting. CASE ASSIGNMENT -- CREATING A DSS MODEL FOR ADVERTISING MIX Ellen has asked you to help her decide how many Sunday paper ads, weekend radio ads, infomercial spots, and portal spots to place. She poses the problem to you this way: LFC must determine how many of each kind of ad or spot should be bought, to maximize the difference between new membership revenues and promotion costs. Ellen is aware that you can model the problem in Excel. to provide her with effective decision support. She asks you to develop a model in Excel to predict costs and net incomes. given different strategies. After much discussion, Ellen and her staff have settled on four promotional mix strategies. Your model must handle each. Doing so is a way of playing "what-ii\" with the decision variables. The four mixes are quantied below: Emphasize TV: Buy 20 of the expensive Infomercial spots. Include only 10 Portal ads, and 25 each of the Newspaper ads and Radio spots. Emphasize Internet: Buy 25 of the Portal ads. Buy only 5 Infomercial spots. Include 25 each of the Newspaper ads and Radio spots. Emphasize Radio: Buy 52 radio spots (one a week -- the maximum). Include 10 each of the other three kinds of promotion. Emphasize Newspapgr: Buy 52 Sunday newspaper ads (one a week -- the maximum). Include 10 each of the other three kinds of promotion. You build your spreadsheet and enter each mix as inputs. Net income and costs will be different with each of the four strategies. Thus, you are using the spreadsheet in a "what if" way as you do -- i.e., " what if" LFC emphasized the Internet, "what if " LFC emphasized the Newspaper, and so on. Then, with the mix that produces the best net income, you should continue to play "what-if" with the four kinds of advertising. You refine the inputs until net income increases no more for that mix. In that way you identify the best strategy, and within that, how the strategy should be ne-tuned. To document your work, you will write a memorandum to Ellen discussing the results and recommending a strategy. Ellen thinks that advertising breadth is necessary -- so all of LFC's eggs are not in one basket. So, she wants at least 5 of each kind of ad or spot to be used in the next year. (I.e.. Paper -- at least 5 issues; Radio -- at least 5 spots; Infomercial -- at least 5 spots; Portal -- at least 5 days). She does not want to over-use any one medium either, so she has set some seemingly sensible maximum numbers, too. Paper -- 52 issues; Radio -- 52 spots; Infomercial -- 25 spots; Portal -- 30 days. Ellen thinks that advertising, in total, can be overdone. So she has set one added constraint -- the total number of ads and spots placed in the year should not exceed 90. Ellen sees one other cost. She thinks that many likely prospects are going to see more than one kind of ad or spot. But that person can only get one membership. The effect of duplicate hits must be accounted for in some way. Ellen proposes to do that by estimating an "overlap factor" cost. She thinks the cost can be Estimated in this way: 1. Compute the total number of all ads and spots in the year. 2. Divide that number by 5 to get the "overlap factor". 3. Then multiply the overlap factor by the "overlap constant". which is $10,000. 4. The result is the cost of advertising in a redundant way. Ellen says there is no need for a budget cap. The minimums and maximums on ads should keep costs in line, she reasons. A one year Lifetime Fitness membership costs a different amount for singles. couples. and families. Ellen says to assume that, on average, for each new signup, $500 in new revenue will come in. Suggestions for setting up your spreadsheet are set forth below. Inputs You need an Inputs area. where the user can enter the number of ads and spots. and play "what if \" with the inputs. Here is an acceptable format (with dummy 1's shown here these amounts would be out of bounds when you enter legitimate media mix data, of course). insults; gumher of adslpql plaged'. Sunday Paper Radio Spots Infomercial Internet Penal Constants You should use these constants: Constants Tax Rate 0.3 Maximum Audiences: Sunday Paper 100000 Radio Spots 10000 Infomercial 100000 Internet Portal 25000 %% Actually see the ad: Sunday Paper 0.5 Radio Spots 0.3 Infomercial 0.01 Internet Portal 0.1 %% Effective_Exposure: Sunday Paper 0.03 Radio Spots 0.2 Infomercial 0.2 Internet Portal 0.1 %% Actually ACT_on exposure: Sunday Paper 0.01 Radio Spots 0.05 Infomercial 0.05 Internet Portal 0.1 Cost of placing one ad/spot: Sunday Paper 1000 Radio Spots 2000 Infomercial 3000 Internet Portal 7500 Cost of developing ads/spots: Sunday Pape 200 Radio Spots 500 Infomercial 5000 Internet Portal 2000 Minimum Number of ad/spots: Sunday Paper Radio Spots Infomercial Internet Portal Maximum Number of ad/spots: Sunday Paper 52 Radio Spots Infomercial Internet Portal Overlap Constant 10000 Value of a one year sign-up 500 Max number of all ads 90 Calculations We recommend that you calculate and use these intermediate values:Calculations: Signups Via. Sunday paper Radio spots Infomercial Internet portal Overlap factor Are Inputs Valid? Signups and the overlap factor are explained above. if the inputs are valid (within the VP's limits) the word "VALID" should appear. If not within limits - for example, if less than 5 radio spots are entered - the word "ERROR" should appear, Thus, this calculation should check for all the bounds that Ellen has placed on the inputs, Waming: Do not hard code constants in your formulas. If you have a number as a constant or a calculated amount, use the cell address of the number in the formula. For example, in checking for valid inputs, use the cell addresses of the mins and maxes, which are in your Constants area This rule applies to formulas throughout the spreadsheet Spreadsheet Body Your spreadsheet body should be an income statement showing revenues from new sign- ups and the related promotional expenses A formal like that shown below is recommended. The statement shows data that you would see before input mix data is entered -- that is, when there are default "1's" in the Input cellsi Income Statement: Revenue Sunday paper Radio spots Infomercial Internet portal Total Costs' Cost of placing ads/spots Sunday paper Radio spots Infomercial Internet portal Overlap cost f I In Total costs Pre-tax revenue Income tax expense Net income The rule on income tax expense is that no income taxes are paid if pre-tax revenue is zero or if there are losses; otherwise the tax expense is equal to the tax rate times the pretax revenuei Results To see the net income and cost results, the user could enter the media mix inputs, and then scroll down to the income statement. But, that is a laborious way to work with a spreadsheet, so you need a place to show key results. A convenient place would be near the inputs themselves. The results area merely echoes numbers from below. If, for example, you want to show net income in cell C9, and the income statement has net income in cell C80, then you merely put this formula in C9: =C80 Then, whatever shows in C80 will now show in C9. Echoing a value from one cell to another is that simple to do. The results you should show are: Total costs, Net Income. and whether or not the inputs are valid in light of the VP's rules on the number of ads and spots. (Validity is a Calculation, recall). Here is an acceptable format (the data are for the results before legitimate inputs are entered): In nuts: Results: Number of ads spots placed: Sunday Paper Raolo Spots lnfomemal lntemet Portal Inputs Valid? ERROR Total Costs 29200 Net Income 7550 Set up your spreadsheet. Enter the data for one of the four strategies. Note the results on a piece of paper, for later reference in your memorandum to Ellen. Then enter the data for the next strategy, and note those results. Do that for all four strategies. For the strategy that gives the highest net income, continue to rene the inputs, until you cannot not show a greater net income with that strategy. of course, values associated with ERROR-full inputs do not count. Try to find some systematic way to play what if, in your renements. One approach is to nd the media type that, increased by one unit, with all others held constant, yields the largest increase in net income. Then maximize that number and go on to the other inputs and rene them in the same way. When you have the highest net income showing in Results, print the spreadsheet. Then be sure to save the program, Use File - Save As. We recommend that you use a le name that has at most 8 characters (Promo.xls'.'). Make a note of the le name. Warning: When leaving Excel, always save your work rst (File Save As). Then Exit (File - Exit). Do not merely take your disk out of the drive and leave. with your work showing on the screen. That is a good way to lose your work. To Complete the Case Write a two page (maximum) double spaced memorandum to Ellen, telling her how many of each kind of ads spot to get in the year. Use a proper Date/To/From/Subject header. You can assume she understands the problem and expects your memorandum, so you need not include any background. State your recommendation in the first paragraph. You should recommend the alternative that produces the best net income. Your first paragraph should be followed by a table (using the Table option in your word processor). It should show the results under the four strategies, and the refined values for the best strategy: Strategy -- Emphasis On: Sunday Radio Infomercial Portal Best Strategy Newspaper Refined Number of Sunday paper ad Number of Radio spots Number of Infomercial spots Number of Portal ads Net Income after taxes Refer the reader to the table for the actual numbers, which you need not repeat in your memorandum. Presented in this way, your results should speak for themselves, and you can probably end the memo with the table. DELIVERABLES 1. Your memorandum file (Word) 2. Your spreadsheet file (Excel)DSS for Marketing Mix Strategies Enter number of ads in column D) Inputs: Results: Number of ads(spots) placed: Inputs Valid? Yes Sunday Paper Total Costs: 115,200.00 A tA Radio Spots Net Income 59,360.00 Infomercial Internet Portal Total 20 Constants Tax Rate 30% Maximum Audiences: Sunday Paper 100,000 Radio Spots 10,000 Infomercial 100,000 Internet Portal 25,000 %% Actually see the ad: Sunday Paper 50% Radio Spots 30% Infomercial 1% Internet Portal 10% %% Effective Exposure: Sunday Paper 3% Radio Spots 20% Infomercial 20% Internet Portal 10% %% Actually ACT on exposure: Sunday Paper 1% Radio Spots 5% Infomercial 5% Internet Portal 10% Cost of placing one ad/spot: Sunday Paper $1,000 Radio Spot $2,000 Infomercial $3,000 Internet Portal $7,50 Cost of developing ads/spots: Sunday Paper $200 Radio Spots $500 Infomercial $5,000 Internet Portal $2,000 Minimum Number of ad/spots: Sunday Paper Radio Spots 5 Infomercial 5 Internet Portal 5 Maximum Number of ad/spots: Sunday Paper 52 Radio Spots 52 Infomercial 25 Internet Porta 30 Overlap Constant 0000 Value of a one year sign-up $500 Max number of all ads 90Number 01 Signups Sunday paper 75 Radio spots 150 Infomercial 50 Internet portal 125 Overlap factor 4 Are Inputs Valid? Yes Income Statement Revenue: Sunday paper 5 37,500.00 Radio spots $ 75,000.00 Infomercial 5 25,000.00 Internet portal 5 52,500.00 Tale 5 200,000.00 Costs: Cost of placing adslspots: Sunday paper 5 5,000.00 Radio spots 5 10,000.00 Infomercial S 15,000.00 Internet portal 5 31500.00 Overlap cost 5 40,000.00 Cost of developing ads/spot: 3 7,700.00 Total costs 5 115,200.00 Pretax revenue 5 54,800.00 Income tax expense 5 25,440.00 Net income 5 59,360.00 DSS for Marketing Mix In this assignment, you will use the Excel Decision Support System to help Lifetime Fitness's marketing management decide how to conduct an effective marketing campaign. The question is: What mix of media should be used to maximize the net income? The decision variables are the numbers of ads spots for each type of media. You will perform three exercises in this assignment: 1. What if analysis. Change the number of ads manually and observe the changes in total cost and net income. Note that there are constraints on the variables. Your inputs must be valid. Find out the net income resulting from each strategy suggested by Lifetime Fitness Center. Can you nd a strategy that results in prot more than any of those strategies suggested by LFC? 2. Optimization: Use Excel Solver (add-in) to nd the optimal solution for this problem. 3. Goal seeking: Given that the company wants to achieve $400,000 net income, what should be the input values? (Goal seeking analysis is available via the Solver add-in) Create an answer worksheet in which you list the answers to the three analyses above. Document your analysis in a business memo addressed to Ellen Goldstain, VP of Marketing at LFC. Note that you need to submit both this Excel le with Excel Solver setup and the memo. (1313) INTRO T... > Assignments > U4 Assignment: DSS Case U4 Assignment: DSS Case Due Apr 28 by 11:59pm Points 30 Submitting a text entry box or a le upload In this assignment, you will use Excel's "what if" capabilities to help a company {Lifetime Fitness Center) to decide how to conduct an effective marketing campaign. The question is: What mix of advertising media should be used to maximize the net income? The case background and required analysis are given in this document: DSS Case i]. You can build your own Excel D55, or using this partially completed design LFCDSS i, Submit the DSS Case analysis, including both the DSS spreadsheet and the business memo (MS Word le} Grading criteria i 4 Previous Next *