$10 Ralph's Artistic Artwork manufactures two different products lines, Impressionistic and Caricature. The following per unit data apply: Impressionism Caricature Selling price $160 $200 Cost of frame @ $5 per square foot $15 Other direct materials $50 $20 Direct labor @ $20 per hour $30 $30 Variable overhead @ $10 per picture $10 Allocated Rent $40 $40 Market Demand (units) 200 $20 100 Note: Each of the following scenarios is independent. 1. The company has only 400 square feet of wood available for the frames. a. How many units of each model should be made? b. What is the total contribution margin under the optimal product mix? 2. The Company has 500 labor hours available. I a. How many units of each model should be made? b. What is the total contribution margin under the optimal product mix? 3. The Company has unlimited resources. a. What product should the company's marketing department be emphasizing? 4. Assume that Ralph has enough resources to manufacture the 2 products above, but nothing else. He can contract with Disney World to produce the Caricature product line for a purchase price of $175 per unit (Ralph would pay Disney for the paintings but then Ralph would sell to the final customer). Ralph would produce another line of art with the resources - Beach Scenes. Ralph is hoping the Beach Scene line would sell well on the West Coast of Florida. He expects to sell 250 paintings of the beach scene for a contribution margin of $75 per picture. Ralph would allocate $40 in rent to the new product line. Should Ralph outsource the Caricature product line and produce the Beach Scenes? 5. What are some non-quantitative factors that should be considered by the company to develop a product mix strategy? STRUCTURE 1 Set up your Excel spreadsheet with 6 tabs . Input Scenario 1 Scenario 2 Scenario 3 Scenario 4 - Outsource Summary and conclusion The format for the summary and conclusion is: Number of units produced Impressionism Caricature Scenario 1 Scenario 2 Contribution margin Scenario 1 Scenario 2 Order of Marketing Effort Scenario 3 (order) Scenario 4 (outsource) Which product should Ralph produce and what is net benefit/cost? Other Factors that should be considered: Constrained Resource: Outsource Ralph's Artistic Artwork manufactures two different products lines, Impressionistic and Caricature. The following per unit data apply: Impressionism Caricature Selling price $160 $200 Cost of frame @ $5 per square foot $10 $15 Other direct materials $50 $20 Direct labor @ $20 per hour $30 $30 Variable overhead @ $10 per picture $10 $20 Allocated Rent $40 $40 Market Demand (units) 100 200 Note: Each of the following scenarios is independent. 1. The Company has only 400 square feet of wood available for the frames. a. How many units of each model should be made? b. What is the total contribution margin under the optimal product mix? 2. The Company has 500 labor hours available. a. How many units of each model should be made? b. What is the total contribution margin under the optimal product mix? 3. The Company has unlimited resources. a. What product should the company's marketing department be emphasizing? Assume that Ralph has enough resources to manufacture the 2 products above, but nothing else. He can contract with Disney World to produce the Caricature product line for a purchase price of $175 per unit (Ralph would pay Disney for the paintings but then Ralph would sell to the final customer). Ralph would produce another line of art with the resources - Beach Scenes. Ralph is hoping the Beach Scene line would sell well on the West Coast of Florida. He expects to sell 250 paintings of the beach scene for a contribution margin of $75 per picture. Ralph would allocate $40 in rent to the new product line. 4. Should Ralph outsource the Caricature product line and produce the Beach Scenes? 5. What are some non-quantitative factors that should be considered by the company to develop a product mix strategy? Ralph's Artistic Artwork manufactures two different products lines, Impressionistic and Caricature. The following per unit data apply: Impressionism Caricature Selling price $160 $200 Cost of frame @ $5 per square foot $10 $15 Other direct materials $50 $20 Direct labor @ $20 per hour $30 $30 Variable overhead @ $10 per picture $10 $20 $40 $40 Allocated Rent 100 200 Market Demand (units) a. Note: Each of the following scenarios is independent. 1. The Company has only 400 square feet of wood available for the frames. How many units of each model should be made? b. What is the total contribution margin under the optimal product mix? 2. The Company has 500 labor hours available, How many units of each model should be made? b. What is the total contribution margin under the optimal product mix? 3. The Company has unlimited resources. a. the optimal product mix 3. The Company has unlimited resources, a. What product should the company's marketing department be emphasizing? 4. Assume that Ralph has enough resources to manufacture the 2 products above, but nothing else. He can contract with Disney World to produce the Caricature product line for a purchase price of $175 per unit (Ralph would pay Disney for the paintings but then Ralph would sell to the final customer). Ralph would produce another line of art with the resources - Beach Scenes. Ralph is hoping the Beach Scene line would sell well on the West Coast of Florida. He expects to sell 250 paintings of the beach scene for a contribution margin of $75 per picture. Ralph would allocate $40 in rent to the new product line. Should Ralph outsource the Caricature product line and produce the Beach Scenes? 5. What are some non-quantitative factors that should be considered by the company to develop a product mix strategy? . STRUCTURE 1. Set up your Excel spreadsheet with 6 tabs Input Scenario 1 Scenario 2 Scenario 3 Scenario 4 - Outsource Summary and conclusion . . The format for the summary and conclusion is: Number of units produced Impressionism Caricature Scenario 1 Scenario 2 Contribution margin Scenario 1 Scenario 2 Order of Marketing Effort Scenario 3 (order) Scenario 4 (outsource) Scenario 2 Order of Marketing Effort Scenario 3 (order) Scenario 4 (outsource) Which product should Ralph produce and what is net benefit/cost? Other Factors that should be considered: Constrained Resource: Outsource: You must link your inputs (from input tab) to your other spreadsheets. You must link your outputs from the calculation worksheets to your summary worksheet. SHOW ALL YOUR TENTO menin savnalohaat larinnll