Sharp makes suits for special occasions. The design has a two-year life cycle. Expected sales volume is 8,000 units; selling price $450 and the profit margin is 45%. You are also given the following information. 1. Direct superior materials cost $100. These can be substituted for an inferior grade which costs 60% less. However 25% of the superior materials cannot be substituted. Sharp's buying manager has negotiated a 10% discount of the superior material. 2. The suits require 40 minutes of direct labor at a cost of $30 per hour. However if the lower grade material is used, the first 1,000 suits will take 40 minutes and the next 1,00030 minutes. 3. Each suit needs two hours of machining at $20 per hour. 4. 5% of the output will be checked for quality. Each quality check will cost $40 5. 15% of the suits will have to be reworked if the inferior material is used. Each rework will cost $40 6. The initial design costs were $400,000 7. Sales and marketing will cost $1m over 2 years. You are required to prepare a MS Excel spreadsheet which: a) Shows profit per unit and target cost per unit b) Calculates the lifetime cost of each unit c) Advises Sharp on the options they have using what if scenarios Berries are planning to introduce a new product. The net profit margin is 20%. The expected sales are 30,000 units at $25 per unit. Research and development is $150k; Market research $35k; Variable manufacturing costs are $300k; fixed manufacturing costs are $100k; closure costs are $75k. A profit margin of 20% is required. You are required to prepare an MS Excel spreadsheet which a) Determines the target cost per unit b) Calculates the lifecycle cost per unit c) The market research company report that more new products would be purchased if the price was reduced by 50 c per unit. How many units need to be sold at the lower price to maintain a 20% profit margin