Week 6 excel problems | Management homework help

Complete the following problem in your textbook:

From Chapter 13:  Problems: 2, 6, 10, and 17

Don't use plagiarized sources. Get Your Custom Essay on
Week 6 excel problems | Management homework help
Just from $5/Page
Order Essay

From Chapter 14: Problems: 3, 9 and 23

From Chapter 15: Problems:  6, 10 and 25

All work should be submitted in Excel with one (1) problem per tab in a single workbook. Formulas should be used as opposed to outside or manual calculations. Use of Excel add-ins is encouraged.

Chapter 13: 

Q2. A brand manager for ColPal Products must determine how much time to allocate between radio and television advertising during the next month. Market research has provided estimates of the audience exposure for each minute of advertising in each medium, which it would like to maximize. Costs per minute of advertising are also known, and the manager has a limited budget of $25,000. The manager has decided that because television ads have been found to be much more effective than radio ads, at least 70% of the time should be allocated to television. Suppose that we have the following data: (See attach photo)

a. Identify the decision variables, objective function, and constraints in simple verbal expressions. 

b. Mathematically formulate a linear optimization model.

Q6. Implement the linear optimization model that you developed for ColPal Products in Problem 2 in Excel and use Solver to find an optimal solution. Interpret the Solver Answer report and identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constraints.

Q10. Implement the linear optimization model that you developed for ColPal Products in Problem 2 in Excel and use Solver to find an optimal solution. Interpret the Solver Answer report and identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constraints.

Q17. Figure 13.33 shows the Solver sensitivity report for the ColPal Products scenario in Problem 2. Using only the information in the sensitivity report, answer the following questions. 

a. Suppose that the exposure for TV advertising was incorrectly estimated and should have been 875. How would the optimal solution have been affected?

b. Radio listening has gone down, and new marketing studies have found that the exposure has dropped to 150. How will this affect the optimal solution?

c. The marketing manager has increased the budget by $2,000. How will this affect the solution and total exposure?

d. The shadow price for the mix constraint (that at least 70% of the time should be allocated to TV) is –250. The marketing manager was told that this means that if the percentage of TV advertising is increased to 71%, exposure will fall by 250. Explain why this statement is incorrect.”

Chapter 14:

Q3. Korey is a business student at State U. She has just completed a course in decision models, which had a midterm exam, a final exam, individual assignments, and class participation. She earned an 86% on the midterm, 94% on the final, 93% on the individual assignments, and 85% on participation. The benevolent instructor is allowing his students to determine their own weights for each of the four grade components—of course, with some restrictions: 

1. The participation weight can be no more than 15%.

2. The midterm weight must be at least twice as much as the individual assignment weight.

3. The final exam weight must be at least three times as much as the individual assignment weight.

4. The weights of the four components must be at least 10%.

5. The weights must sum to 1.0 and be nonnegative.

a. Develop a model that will yield a valid set of weights to maximize Korey’s score for the course.

b. Implement your model on a spreadsheet and find a good solution using only your intuition.

c. Find an optimal solution using Solver.

Q9. Jaycee’s department store chain is planning to open a new store. It needs to decide how to allocate the 100,000 square feet of available floor space among seven departments. Data on expected performance of each department per month, in terms of square feet (sf), are shown next.

The company has gathered $20 million to invest in floor stock. The risk column is a measure of risk associated with investment in floor stock based on past data from other stores and accounts for outdated inventory, pilferage, breakage, and so on. For instance, electronics loses 24% of its total investment, furniture loses 12% of its total investment, and so on. The amount of risk should be no more than 10% of the total investment. 

a. Develop a linear optimization model to maximize profit.

b. If the chain obtains another $1 million of investment capital for stock, what would the new solution be?

Q23. Jason Wright is a part-time business student who would like to optimize his financial decisions. Currently, he has $16,000 in his savings account. Based on an analysis of his take-home pay, expected bonuses, and anticipated tax refund, he has estimated his income for each month over the next year. In addition, he has estimated his monthly expenses, which vary because of scheduled payments for insurance, utilities, tuition and books, and so on. The following table summarizes his estimates: (See Attach Photo)

Jason has identified several short-term investment opportunities: 

1. a 3-month CD yielding 0.60% at maturity

2. a 6-month CD yielding 1.42% at maturity

3. an 11-month CD yielding 3.08% at maturity

4. a savings account yielding 0.0375% per month

To ensure enough cash for emergencies, he would like to maintain at least $2,000 in the savings account. Jason’s objective is to maximize his cash balance at the end of the year. Develop a linear optimization model to find the best investment strategy.”

Chapter 15:

Q6. The Gardner Theater, a community playhouse, needs to determine the lowest-cost production budget for an upcoming show. Specifically, they have to determine which set pieces to construct and which, if any, set pieces to rent from another local theater at a predetermined fee. However, the organization has only two weeks to fully construct the set before the play goes into technical rehearsals. The theater has two part-time carpenters who work up to 12 hours a week each at $10 an hour. Additionally, the theater has a part-time scenic artist who can work 15 hours per week to paint the set and props as needed at a rate of $15 per hour. The set design requires 20 flats (walls), 2 hanging drops with painted scenery, and 3 large wooden tables (props). The number of hours required for each piece for carpentry and painting is shown below: (See Attached Photo)

Flats, hanging drops, and props can also be rented at a cost of $75, $500, and $350 each, respectively. How many of each units should be built by the theater and how many should be rented to minimize total costs?

Q10. The personnel director of Hatch Financial. which recently absorbed another firm, is now downsizing and must relocate five information systems analysts from recently closed locations. Unfortunately, there are only three positions available for five people. Salaries are fairly uniform among this group (those with higher pay were already given the opportunity to begin anew). Moving expenses will be used as the means of determining who will be sent where. Estimated moving expenses are as follows: (See Attached Photo)

Model this as an integer optimization model to minimize cost and determine which analysts to relocate to the three locations.

Q25. Tunningley Services is establishing a new business to serve customers in the Ohio, Kentucky, and Indiana region around the Cincinnati Ohio area. The company has identified 15 key market areas and wants to establish regional offices to meet the goal of being able to travel to all key markets within 60 minutes. The data file Tunningley.xlsx provides travel times in minutes between each pair of cities. a. Develop and solve an optimization model to find the minimum number of locations required to meet their goal.

b. Suppose they change the goal to 90 minutes. What would be the best solution?

Order a unique copy of this paper
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

We are committed to making our customer experience enjoyable and that we are keen on creating conditions where our customers feel secured and respected in their interactions with us.
With our qualified expert team who are available 24/7, we ensure that all our customer needs and concerns are met..

Money Payback-back guarantee

Our refund policy allows you to get your money back when you are eligible for a refund. In such a case, we guarantee that you will be paid back to your credit card. Another alternative we offer you is saving this money with us as a credit. Instead of processing the money back, keeping it with us would be an easier way to pay for next the orders you place

Read more

Zero-plagiarism guarantee

All orders you place on our website are written from scratch. Our expert team ensures that they exercise professionalism, the laid down guidelines and ethical considerations which only allows crediting or acknowledging any information borrowed from scholarly sources by citing. In cases where plagiarism is confirmed, then the costumier to a full refund or a free paper revision depending on the customer’s request..

Read more

Free-revision policy

Quality is all our company is about and we make sure we hire the most qualified writers with outstanding academic qualifications in every field. To receive free revision the Company requires that the Customer provide the request within fourteen (14) days from the first completion date and within a period of thirty (30) days for dissertations.

Read more

Privacy policy

We understand that students are not allowed to seek help on their projects, papers and assignments from online writing services. We therefore strive to uphold the confidentiality that every student is entitled to. We will not share your personal information elsewhere. You are further guaranteed the full rights of originality and ownership for your paper once its finished.

Read more

Fair-cooperation guarantee

By placing an order with us, you agree to the service we provide. We will endear to do all that it takes to deliver a comprehensive paper as per your requirements. We also count on your cooperation to ensure that we deliver on this mandate.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency