A: Exhibit 1 – Media Selection
Model the media selection problem as a mathematical programming model to determine the optimal combination of media outlets and the number of units in each that would maximize the number of potential customers reached. Use Microsoft Excel’s QM to solve the problem.
B: Exhibit 2 – Investment Options
Model the investment options problem as a mathematical programming model to maximize the returns for the money invested by the company. Use Microsoft Excel’s QM to solve the problem.
Rich thinks it is a good idea to address each of five problems that Rudy talked about one at a time. He decides to look at the media selection issue first. To get data on this, Rich approaches Sarah, the marketing manager. The following are excerpts from Rich’s conversation with Sarah.
Rich: “Sarah, you have been the marketing manager of the company for the last five years. I need some information from you regarding how the company approached the media selection issue in the past for services in new markets. Can you help me with that?”
Sarah: “Sure, is this for the satellite dish venture?”
Rich: “You’ve got it. So, I understand from Rudy that your department has already identified some preliminary requirements related to how we are going to approach the media selection issue for this project?”
Sarah: “Yes, let me share that information with you.”
Media Selection Data:
The marketing department’s plan for the advertisement campaign has a budget of $100,000. Previous experience has shown that the exposure to potential customers as a result of the advertising effort will be, as follows:
? For every sign placed by the roadside, 10 additional customers will sign up for the service.
? For every newspaper insert, 30 additional customers will sign up for the service.
? For every hundred weekend flyers in supermarkets, 10 additional customers will sign up for the service.
? For every hundred personal mailings to potential customers, 40 additional customers will sign up for the service.
? For every TV advertisement placed daily in the last month before the service is launched, 490 additional customers will sign up for the service.
The costs for each of these advertising measures, along with the practical minimum and maximum number that should be planned for each, are shown in the following table:
Advertising device Cost Minimum Maximum
Roadside sign 25 100 500
Newspaper insert 60 50 300
Weekend flyers
(hundreds) 30 40 100
Personal mailings (hundreds) 82 500 800
Daily TV ads 1000 3 12
Notes:
? The data presented here are only for the month preceding the launch of the service.
? This is a critical time to register new customers; therefore, the initial analysis needs to focus only on this month.
? The cost data in the table are the $ cost per insert for each type.
? The practical maximum and minimum numbers in the table are the maximum and minimum number of inserts possible for each media type.
It is important to note that in real-life situations, it takes significant effort to collect data by examining various documents and interviewing different people, to develop assumptions for simplifying analysis, and to present the data in an understandable form.
Rich: “So, this data is from your experience and shows the number of potential customers that we can get from each media option, correct?”
Sarah: “Yes. In addition, we have estimated the costs and the maximum and minimum number of units that we can feasibly show on each media outlet.”
Rich: “OK, I think I can model this media selection data as a mathematical programming model. That will give us a combination of media outlets that will maximize the number of customers we reach.”
Sarah: “Great! That will be a good start.”
Rich’s Meeting with Ron Floyd:
After getting the data for the media selection decision, Rich goes to Ron, the company’s financial manager, to understand the details of the investment situation Rudy had talked about.
Rich: “Ron, I’m here about the satellite dish project, and I need some information regarding the finances for this project.”
Ron: “Sure, what do you need to know?”
Rich: “Well, to start with, Rudy indicated that there is a sum of money earmarked for this project?”
Ron: “Yeah, call it a pot of gold because it is almost $2 million dollars that we are talking about here.”
Rich: “Really? That’s why Rudy mentioned that we needed to look at ways to invest this money in alternative investments before we use it for the project.”
Ron: “Yes, that is sensible because you won’t need the entire amount right away.”
Rich: “So are there any rules that we traditionally follow when we decide on such investments?”
Ron: “Yes, there are. Let me tell you some of these guiding principles as we discuss the investment options data.”
Investment Options Data:
The company has traditionally hedged its investments across a variety of investment options. The options available to the company with their expected annual returns are shown in the following table. In addition, the table shows the levels of liquidity and risk for each of the six types of investments.
Investment type Expected annual rate of return (%) Liquidity level Risk level
Money market funds 12.25 High High
Stocks 11.50 High High
School bonds 4.00 Low Low
Certificates of deposit 3.00 Low Low
Tax-free municipal bonds 6.50 Low Low
Treasury bills 7.50 High Low
In addition, the company has the following corporate principles for investing:
1. The company will not invest more than 30 percent of the money in either of the first two investments because these are too risky.
2. For the same reason, not more than 50 percent of the total money will be put in the first two investments together.
3. The company will need the money whenever required for the satellite dish venture. Therefore, not more than 30 percent of the total money will be invested in investments with low liquidity, that is, school bonds, certificates of deposit, and tax-free municipal bonds.
4. The company considers treasury bills issued by the Federal Reserve as less risky and having high liquidity. Therefore, the company wants at least 15 percent of the total money to be invested in treasury bills.
To diversify across the investment types, there is a corporate policy limit on each of the six types of investment. These are listed in the following table:
Investment type Maximum % of total money
that can be invested
Money market funds 30
Stocks 30
School bonds 20
Certificates of deposit 25
Tax-free municipal bonds 40
Treasury bills 25
1. For the same reason of diversification, a minimum of 10 percent of the total money will be invested in each of the funds.
Notes:
1. In the company, investments with a high liquidity level are known as “liquid” investments and those with a low liquidity level are known as “non-liquid” investments.
2. Similarly, investments with a high risk level are known as “risky” investments and those with a low risk level are known as “less-risky” investments.
It is important to note that in real-life situations, it takes significant effort to collect data by examining various documents and interviewing different people, to develop assumptions for simplifying analysis, and to present the data in an understandable form.
PART A:
The first step in media selection is to formulate the media-selection problem as a linear-programming problem. The decision variables are the number of inserts in each of the media outlets, and the objective function is to maximize the number of customers reached. The constraints and objective functions are to be entered in Excel so that the problem can be solved using Excel QM
PART B:
In this project, investment portfolio analysis is modelled as a linear-programming problem in which the decision variables are associated with the amount of money invested in each investment option. The objective function is to maximize the return from all the investments combined.