Please solve dss case for management information systems class, use excel spreadsheet shareable for download please and thank you

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 Internet 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: Paper -- 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 flyer 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 film. 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-if" with the decision variables. The four mixes are quantified 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 Newspaper: 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 fine-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 over-done. 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 sign-up, $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). Constants You should use these constants: Calculations We recommend that you calculate and use these intermediate values: 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. Warning: 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 signups and the related promotional expenses. A format 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 cells. 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 pre-tax revenue. 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 C : =C80 Then, whatever shows in C80 will now show in C. 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): 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 refine 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 refinements. One approach is to find 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 refine 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 file name that has at most 8 characters (Promo.xls?). Make a note of the file name. Warning: When leaving Excel, always save your work first (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: 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) 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 Internet 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: Paper -- 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 flyer 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 film. 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-if" with the decision variables. The four mixes are quantified 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 Newspaper: 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 fine-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 over-done. 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 sign-up, $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). Constants You should use these constants: Calculations We recommend that you calculate and use these intermediate values: 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. Warning: 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 signups and the related promotional expenses. A format 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 cells. 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 pre-tax revenue. 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 C : =C80 Then, whatever shows in C80 will now show in C. 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): 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 refine 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 refinements. One approach is to find 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 refine 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 file name that has at most 8 characters (Promo.xls?). Make a note of the file name. Warning: When leaving Excel, always save your work first (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: 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)