Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Hello! i have posted the assigment required if you could please answer both word document and excel portion that would be great(: with formulas entered
Hello! i have posted the assigment required if you could please answer both word document and excel portion that would be great(:
with formulas entered in excel please
Late submissions will not be graded. Projects should be submitted in Blackboard (Linear Programming Group Assignment link). You will submit two files per group: 1. One Word Document with a report outlining your recommendations for the issues listed in the case. The document should also include an appendix for each of the issues showing your algebraic equations and decisions, and a readable screen clip of your Excel output, including the Solver dialogue box (see parts a & b below). This is especially important to show how you used solver. 2. One Excel Spreadsheet with clearly labelled worksheets (one worksheet for each of the issues in the assignment). The work in the worksheets needs to be well organized and clearly labelled, with formulas used appropriately. Examples of potential formats can be found in chapter 16 in the text. The general approach for solving each of the issues should be: a. Formulate the linear programming model for the issue algebraically. Group this in three sections: 1) Definition of Decision Variables, 2) Objective Function Equation, 3) Constraints Equations. This should go in the appendix to your memo. (6 marks each issue) b. Set up and solve this problem using Excel. Include screenshots of this in the appendix to the memo. (6 marks each issue) c. Provide your answers and recommendations to the Manager of the company (include your recommendations for all issues in single report) (2 marks each issue). See basic structure of the report on page 5. d. General organization and formatting - 4 marks. MacBook Coase Specialty Paints Coase Specialty Paints is a company run by Ronald Coase. He is an artist and earned a BA degree with a major in Art and Design and a minor in philosophy. The company was founded by Ronald's father, and he recently asked him to take over managing it so that he could retire. Ronald agreed and left his interior design job with an architecture firm to assume the role. He is keen to develop the business further. The company makes a wide variety of paints, mostly targeted at artists. However, since Ronald has had experience in interior design, he has determined that the company could apply its skills to develop new products for that industry and others. He knows that he hasn't yet developed his quantitative analysis skills to the point they need to be to run a company, so he is seeking assistance with four decisions that he needs to make. He would like to have a report from you providing recommendations for each decision, along with the background calculations that support your decisions. Decision 1: Marketing - Advertising Placement Ronald has a yearly advertising and promotions budget of $40,000 His goal is to maximize the reach (the number of customers that see his company's ads or other marketing efforts). He is considering a mix of placing ads on art websites and in printed art magazines, as well as using targeted social media ad placements and appearances at art tradeshows. He wants to make sure that the company has a diverse presence, so would like to see at most 150 ads placed in any combination of websites and social tredia per year. However, he has data that suggests that if he advertises on the websites more than 60 times per year he will be wasting money. There are seven art tradeshows that the company could attend, but Ronald feels it is mandatory to attend at least the two most popular tradeshows. There are three major art magazines, each published monthly, that Ronald would like to advertise in. He could advertise in each of these once per month but would be comfortable reducing the frequency to a minimum of once every three months in each magazine and rotating the placements between the magazines. The reach and cost of each type of ad placement or event is shown below: Art Websites Social Art Media Tradeshows Print Magazine Ads Reach (number of people) 20,000 10,000 5,000 15,000 Cost (dollars) 200 250 1.000 400 Recommend the appropriate mix of advertising and promotions for the company. Also comment on what would change if Ronald doubled his advertising and promotions budget. Estimate the increased reach. After doubling his advertising budget, did Ronald spend all his budget? Why or why not? Late submissions will not be graded. Projects should be submitted in Blackboard (Linear Programming Group Assignment link). You will submit two files per group: 1. One Word Document with a report outlining your recommendations for the issues listed in the case. The document should also include an appendix for each of the issues showing your algebraic equations and decisions, and a readable screen clip of your Excel output, including the Solver dialogue box (see parts a & b below). This is especially important to show how you used solver. 2. One Excel Spreadsheet with clearly labelled worksheets (one worksheet for each of the issues in the assignment). The work in the worksheets needs to be well organized and clearly labelled, with formulas used appropriately. Examples of potential formats can be found in chapter 16 in the text. The general approach for solving each of the issues should be: a. Formulate the linear programming model for the issue algebraically. Group this in three sections: 1) Definition of Decision Variables, 2) Objective Function Equation, 3) Constraints Equations. This should go in the appendix to your memo. (6 marks each issue) b. Set up and solve this problem using Excel. Include screenshots of this in the appendix to the memo. (6 marks each issue) c. Provide your answers and recommendations to the Manager of the company (include your recommendations for all issues in single report) (2 marks each issue). See basic structure of the report on page 5. d. General organization and formatting - 4 marks. MacBook Coase Specialty Paints Coase Specialty Paints is a company run by Ronald Coase. He is an artist and earned a BA degree with a major in Art and Design and a minor in philosophy. The company was founded by Ronald's father, and he recently asked him to take over managing it so that he could retire. Ronald agreed and left his interior design job with an architecture firm to assume the role. He is keen to develop the business further. The company makes a wide variety of paints, mostly targeted at artists. However, since Ronald has had experience in interior design, he has determined that the company could apply its skills to develop new products for that industry and others. He knows that he hasn't yet developed his quantitative analysis skills to the point they need to be to run a company, so he is seeking assistance with four decisions that he needs to make. He would like to have a report from you providing recommendations for each decision, along with the background calculations that support your decisions. Decision 1: Marketing - Advertising Placement Ronald has a yearly advertising and promotions budget of $40,000 His goal is to maximize the reach (the number of customers that see his company's ads or other marketing efforts). He is considering a mix of placing ads on art websites and in printed art magazines, as well as using targeted social media ad placements and appearances at art tradeshows. He wants to make sure that the company has a diverse presence, so would like to see at most 150 ads placed in any combination of websites and social tredia per year. However, he has data that suggests that if he advertises on the websites more than 60 times per year he will be wasting money. There are seven art tradeshows that the company could attend, but Ronald feels it is mandatory to attend at least the two most popular tradeshows. There are three major art magazines, each published monthly, that Ronald would like to advertise in. He could advertise in each of these once per month but would be comfortable reducing the frequency to a minimum of once every three months in each magazine and rotating the placements between the magazines. The reach and cost of each type of ad placement or event is shown below: Art Websites Social Art Media Tradeshows Print Magazine Ads Reach (number of people) 20,000 10,000 5,000 15,000 Cost (dollars) 200 250 1.000 400 Recommend the appropriate mix of advertising and promotions for the company. Also comment on what would change if Ronald doubled his advertising and promotions budget. Estimate the increased reach. After doubling his advertising budget, did Ronald spend all his budget? Why or why not Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started