Please help me with my

Hand in Group Assignment on Linear Programming

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 Furktion 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 or report. (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 and memo on pages 5 & 6. d. General organization and formatting - 4 marks. Richelieu Specialty Paints is a company run by Amanda Richelieu. She is an artist and earned a BA degree with a major in Art and Design. The company was founded by Amanda's father and he recently asked her to take over managing it so that he could retire. Amanda agreed and left her interior design job with an architecture firm to assume the role. She is keen to develop the business further. The company makes a wide variety of paints, mostly targeted at fine artists. However, since Amanda has had experience in interior design she has determined that the company could apply its skills to develop new products for that industry and others. She knows that she hasn't yet developed her quantitative analysis skills to the point they need to be to run a company, so she is seeking assistance with four decisions that she needs to make. She would like to have a report from you providing recommendations for each decision, along with the background calculations that support your decisions. I Decision 1: Marketing - Advertising Placement Amanda has a yearly advertising and promotions budget of $50,000. Her goal is to maximize the reach (the number of customers that see her company's ads or other marketing efforts). She 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. She wants to make sure that the company has an online presence, so would like to see at least 100 ads placed in any combination of websites and social media per year. However, she has data that suggests that if she advertises on the websites more than 60 times per year she will be wasting money. There are seven art tradeshows that the company could attend, but Amanda feels it is mandatory to attend at least the two most popular tradeshows. There are three major art magazines, each published monthly, that Amanda would like to advertise in. She 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: Print Art Social Art Media Tradeshows Magazine Websites Ads Reach (number of people) 14000 12,000 5,000 10,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 Amanda doubled her advertising and promotions budget. Estimate the increased reach. Decision 2: Financial - Assets Investment Richelieu Specialty Paints has been quite successful and has $1,500,000 cash to be invested. Amanda and her management team have developed four potential investments in addition to leaving the cash in a low interest deposit. The four new opportunities are: 1. Purchase some shares of a major supplier ( 25% risk of total loss, 13% potential return) 2. Invest in the research and development of a new product (50% risk of total loss, 60% potential return) 3. Buy a selection of corporate bonds (10% risk of total loss, 6% potential return) 4. Buy a selection of government bonds (1% risk of total loss, 3% potential return) Leaving the money in a low interest deposit is virtually riskless and has a 0.5% potential return. Richelieu's father had provided Amanda with some guidelines for investment, aimed at diversification and risk management. These are: 1. Invest a maximum of 40% of the total cash in any one of the four new opportunities to ensure some risk control. 2. Invest at least $100,000 in each of the four new opportunities to ensure some diversity. 3. Limit the total potential loss, on average, to $250,000, based on the risk factors indicated. 4. Ensure the highest return possible. Recommend the appropriate mix of the four investments and note how much money, if any, should be left in the low interest deposit. Also, how would this mix change, and what would happen to potential return, if Amanda decided to take a more conservative approach and limit the total potential loss to $175,000. Decision 3: Operations - Materials Blending Richelieu Specialty Paints has begun making paints for the interior design market. The company starts the process by blending four base paint mixes that they buy from major manufacturers. They then add other components to finish off the paint mix to customer specifications. Each of the four base mixes includes pigment, solvent and three additives. When Richelieu blends the four base mixes they want the final product to be within these specifications (these numbers are a percentage of total volume): . Pigment: 26% sx32% . Solvent: 59% x 66% . Additive 1: 3% x5% . Additive 2: 5% sx7% . Additive 3: 1% x 3% The specifications of each of the four base mixes before they are blended is: Base 1 Base 2 Base 3 Base 4 Pigment Volume 30.0% 25.0% 25.0% 30.0% Solvent Volume 48.0% 60.0% 67.5% 55.0% Additive 1 Volume 10.0% 5.0% 0.0% 5.0% Additive 2 Volume 16.0% 5.0% 2.0% 10.0% Additive 3 Volume 6.0% 5.0% 5.5% 0.0% Unit Cost $15 $20 $25 $50 Recommend what percentage of each base mix should be included in the blend to achieve specifications listed, keeping input costs as low as possible. Base mix 2 is purchased overseas and thus the price fluctuates due to currency exchange rates. Comment on how the percentage of each base included in the blend would change if the cost for mix 2 rises to $35 per unit and note what would happen to total cost in this situation. (instructor's note: you do not need units of volume, use the percentages) Decision 4: Operations and Marketing - Product Mix Richelieu Specialty Paints has received a special order for two types of paints: Marine Coat, used on boats and Blocker, used on items that receive a great deal of strong, hot sun. Both paints use a UV blocking additive and a mold inhibitor. Richelieu currently has 240 litres of the mold inhibitor on hand and 300 litres of the UV blocking additive. Marine Coat Mold Inhibitor Used (litres per can) Blocker 0.4 0.6 UV Blocking Additive Used (tres per can) CM per can 1 0.5 $3.00 $6.00 The special-order customer has requested at least 200 cans of Marine Coat but will take up to 350 cans if available. The customer will take as much Blocker paint as Richelieu can provide. Amanda would like to generate the highest total contribution margin possible for this order and needs to know how much of each product should be made. Report Format Title Page The next page should include a couple of paragraphs discussing what the client wants you to do. Briefly summarize, do not cut and paste the assignment. Over the next few paragraphs discuss your results and recommendations. Keep a logical order - discuss in the same order as the problems were given. Decision 1 first, Decision 2 second and so on. Give meaningful headers to each of your decisions (DO NOT CALL THEM DECISION 1, DECISION 2, ETC). At a minimum each decision should be discussed in it's own paragraph. It may take more than one paragraph per decision. That would be up to you. If you asked to do "what if" analysis, which is what we do to find the impact of a change in a constraint, that requires another solver calculation with the changed constraint and another set of results. Lastly, include a concluding paragraph that summarizes the problem in a couple sentences and the recommendations in another few sentences. Include an appendix where you will put your relevant tables, graphs, excel results, etc. 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 Furktion 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 or report. (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 and memo on pages 5 & 6. d. General organization and formatting - 4 marks. Richelieu Specialty Paints is a company run by Amanda Richelieu. She is an artist and earned a BA degree with a major in Art and Design. The company was founded by Amanda's father and he recently asked her to take over managing it so that he could retire. Amanda agreed and left her interior design job with an architecture firm to assume the role. She is keen to develop the business further. The company makes a wide variety of paints, mostly targeted at fine artists. However, since Amanda has had experience in interior design she has determined that the company could apply its skills to develop new products for that industry and others. She knows that she hasn't yet developed her quantitative analysis skills to the point they need to be to run a company, so she is seeking assistance with four decisions that she needs to make. She would like to have a report from you providing recommendations for each decision, along with the background calculations that support your decisions. I Decision 1: Marketing - Advertising Placement Amanda has a yearly advertising and promotions budget of $50,000. Her goal is to maximize the reach (the number of customers that see her company's ads or other marketing efforts). She 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. She wants to make sure that the company has an online presence, so would like to see at least 100 ads placed in any combination of websites and social media per year. However, she has data that suggests that if she advertises on the websites more than 60 times per year she will be wasting money. There are seven art tradeshows that the company could attend, but Amanda feels it is mandatory to attend at least the two most popular tradeshows. There are three major art magazines, each published monthly, that Amanda would like to advertise in. She 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: Print Art Social Art Media Tradeshows Magazine Websites Ads Reach (number of people) 14000 12,000 5,000 10,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 Amanda doubled her advertising and promotions budget. Estimate the increased reach. Decision 2: Financial - Assets Investment Richelieu Specialty Paints has been quite successful and has $1,500,000 cash to be invested. Amanda and her management team have developed four potential investments in addition to leaving the cash in a low interest deposit. The four new opportunities are: 1. Purchase some shares of a major supplier ( 25% risk of total loss, 13% potential return) 2. Invest in the research and development of a new product (50% risk of total loss, 60% potential return) 3. Buy a selection of corporate bonds (10% risk of total loss, 6% potential return) 4. Buy a selection of government bonds (1% risk of total loss, 3% potential return) Leaving the money in a low interest deposit is virtually riskless and has a 0.5% potential return. Richelieu's father had provided Amanda with some guidelines for investment, aimed at diversification and risk management. These are: 1. Invest a maximum of 40% of the total cash in any one of the four new opportunities to ensure some risk control. 2. Invest at least $100,000 in each of the four new opportunities to ensure some diversity. 3. Limit the total potential loss, on average, to $250,000, based on the risk factors indicated. 4. Ensure the highest return possible. Recommend the appropriate mix of the four investments and note how much money, if any, should be left in the low interest deposit. Also, how would this mix change, and what would happen to potential return, if Amanda decided to take a more conservative approach and limit the total potential loss to $175,000. Decision 3: Operations - Materials Blending Richelieu Specialty Paints has begun making paints for the interior design market. The company starts the process by blending four base paint mixes that they buy from major manufacturers. They then add other components to finish off the paint mix to customer specifications. Each of the four base mixes includes pigment, solvent and three additives. When Richelieu blends the four base mixes they want the final product to be within these specifications (these numbers are a percentage of total volume): . Pigment: 26% sx32% . Solvent: 59% x 66% . Additive 1: 3% x5% . Additive 2: 5% sx7% . Additive 3: 1% x 3% The specifications of each of the four base mixes before they are blended is: Base 1 Base 2 Base 3 Base 4 Pigment Volume 30.0% 25.0% 25.0% 30.0% Solvent Volume 48.0% 60.0% 67.5% 55.0% Additive 1 Volume 10.0% 5.0% 0.0% 5.0% Additive 2 Volume 16.0% 5.0% 2.0% 10.0% Additive 3 Volume 6.0% 5.0% 5.5% 0.0% Unit Cost $15 $20 $25 $50 Recommend what percentage of each base mix should be included in the blend to achieve specifications listed, keeping input costs as low as possible. Base mix 2 is purchased overseas and thus the price fluctuates due to currency exchange rates. Comment on how the percentage of each base included in the blend would change if the cost for mix 2 rises to $35 per unit and note what would happen to total cost in this situation. (instructor's note: you do not need units of volume, use the percentages) Decision 4: Operations and Marketing - Product Mix Richelieu Specialty Paints has received a special order for two types of paints: Marine Coat, used on boats and Blocker, used on items that receive a great deal of strong, hot sun. Both paints use a UV blocking additive and a mold inhibitor. Richelieu currently has 240 litres of the mold inhibitor on hand and 300 litres of the UV blocking additive. Marine Coat Mold Inhibitor Used (litres per can) Blocker 0.4 0.6 UV Blocking Additive Used (tres per can) CM per can 1 0.5 $3.00 $6.00 The special-order customer has requested at least 200 cans of Marine Coat but will take up to 350 cans if available. The customer will take as much Blocker paint as Richelieu can provide. Amanda would like to generate the highest total contribution margin possible for this order and needs to know how much of each product should be made. Report Format Title Page The next page should include a couple of paragraphs discussing what the client wants you to do. Briefly summarize, do not cut and paste the assignment. Over the next few paragraphs discuss your results and recommendations. Keep a logical order - discuss in the same order as the problems were given. Decision 1 first, Decision 2 second and so on. Give meaningful headers to each of your decisions (DO NOT CALL THEM DECISION 1, DECISION 2, ETC). At a minimum each decision should be discussed in it's own paragraph. It may take more than one paragraph per decision. That would be up to you. If you asked to do "what if" analysis, which is what we do to find the impact of a change in a constraint, that requires another solver calculation with the changed constraint and another set of results. Lastly, include a concluding paragraph that summarizes the problem in a couple sentences and the recommendations in another few sentences. Include an appendix where you will put your relevant tables, graphs, excel results, etc