An author just signed a lucrative contract with a publisher that offers to pay her the amount of $800 at the end of year 9 when the book is scheduled to be released. The author, being profligate, desires to receive a different package: an immediate payment of $100 that is followed by an annuity (an equal amount) to be paid at the end of each year for 9 consecutive years. What annuity will make his package equivalent to the publisher's advance. Use an interest rate is 6%.

Transcribed Image Text:

In this assignment, you will use Excel's "what if" capabilities 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. 2. Optimization: Use Excel Solver add-in to find 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? Create an answer worksheet in which you list the answers to the three problems 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 file with Excel Solver setup and the memo. DSS for Marketing Mix Strategies (Enter number of ads in column D) Inputs: Results: Number of ads(spots) placed: $ Sunday Paper Radio Spots Infomercial $ Internet Portal Total 55552 20 Inputs Valid? Total Costs: Net Income Yes 115,200.00 59,360.00 Maximum Audiences: Sunday Paper Radio Spots Infomercial Internet Portal %% Actually see the ad: Sunday Paper Radio Spots Infomercial Internet Portal %% Effective Exposure: Sunday Paper Radio Spots Infomercial Internet Portal %% Actually ACT on exposure: Sunday Paper Radio Spots Infomercial Internet Portal Cost of placing one ad/spot: Sunday Paper Radio Spots Infomercial Internet Portal Cost of developing ads/spots: Sunday Paper Radio Spots Infomercial Internet Portal Minimum Number of ad/spots: Sunday Paper Radio Spots Infomercial Internet Portal Maximum Number of ad/spots: Sunday Paper Radio Spots Infomercial Internet Portal Overlap Constant Value of a one year sign-up Max number of all ads 30% 100,000 10,000 100,000 25,000 50% 30% 1% 10% 3% 20% 20% 10% 1% 5% 5% 10% $1,000 $2,000 $3,000 $7,500 $200 $500 $5,000 $2,000 5555 52 52 25 30 10000 $500 90 Number of Signups Sunday paper Radio spots Infomercial Internet portal Overlap factor Are Inputs Valid? 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 Cost of developing ads/spots $ Total costs Pre-tax revenue Income tax expense Net income $ SSSSSS $ $ $ $ 5,000.00 10,000.00 15,000.00 37,500.00 40,000.00 7,700.00 $ $ $ $ $ LA LA LA LA LA 5555 $ $ $ $ 75 150 50 125 4 Yes 37,500.00 75,000.00 25,000.00 62,500.00 200,000.00 115,200.00 84,800.00 25,440.00 59,360.00 In this assignment, you will use Excel's "what if" capabilities 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. 2. Optimization: Use Excel Solver add-in to find 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? Create an answer worksheet in which you list the answers to the three problems 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 file with Excel Solver setup and the memo. DSS for Marketing Mix Strategies (Enter number of ads in column D) Inputs: Results: Number of ads(spots) placed: $ Sunday Paper Radio Spots Infomercial $ Internet Portal Total 55552 20 Inputs Valid? Total Costs: Net Income Yes 115,200.00 59,360.00 Maximum Audiences: Sunday Paper Radio Spots Infomercial Internet Portal %% Actually see the ad: Sunday Paper Radio Spots Infomercial Internet Portal %% Effective Exposure: Sunday Paper Radio Spots Infomercial Internet Portal %% Actually ACT on exposure: Sunday Paper Radio Spots Infomercial Internet Portal Cost of placing one ad/spot: Sunday Paper Radio Spots Infomercial Internet Portal Cost of developing ads/spots: Sunday Paper Radio Spots Infomercial Internet Portal Minimum Number of ad/spots: Sunday Paper Radio Spots Infomercial Internet Portal Maximum Number of ad/spots: Sunday Paper Radio Spots Infomercial Internet Portal Overlap Constant Value of a one year sign-up Max number of all ads 30% 100,000 10,000 100,000 25,000 50% 30% 1% 10% 3% 20% 20% 10% 1% 5% 5% 10% $1,000 $2,000 $3,000 $7,500 $200 $500 $5,000 $2,000 5555 52 52 25 30 10000 $500 90 Number of Signups Sunday paper Radio spots Infomercial Internet portal Overlap factor Are Inputs Valid? 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 Cost of developing ads/spots $ Total costs Pre-tax revenue Income tax expense Net income $ SSSSSS $ $ $ $ 5,000.00 10,000.00 15,000.00 37,500.00 40,000.00 7,700.00 $ $ $ $ $ LA LA LA LA LA 5555 $ $ $ $ 75 150 50 125 4 Yes 37,500.00 75,000.00 25,000.00 62,500.00 200,000.00 115,200.00 84,800.00 25,440.00 59,360.00

Answer rating: 100% (QA)

To calculate the value of annuity we first need to calculate the present value of 800 recei... View the full answer