Please complete excel sheet and do not hesitate if you have any questions

Project 1 Name: Type your Name here Complete using excel and submit by uploading to this assignment area by June 14 th 6:00 p.m. Central Time. Your grade will be based on the accuracy of your answer and the proper usage of excel. Make sure you use cell references, absolute value, use the IF statement to designate the variances as favorable or unfavorable and IFSUM for the subvariances. I have some reference material in the chapter 12 folder. Your answers should be on the analysis worksheet. I have posted the solution to Ex 12-22, 23,24, and 25 which should be reviewed and used as a model when developing your answers. There is step by step example of this type of Strategic Analysis of Operating income starting on page 487, which you will find very helpful. You should be using absolute value in your variance analysis, since we do not look at this analysis using positive and negatives. Also, there is an excellent Problem for Self-Study on page 498 that can also be used as a model. Make sure you save your file using your first initial and last name. Following a strategy of product differentiation. Computer Corporation makes a high-end Computer , High-end model. Computer Corporation presents the following data for the years 2015 and 2016. DATA Field: Units of High-end model produced and sold Selling Price Direct materials (pounds) Direct materials costs per pounds Manufacturing capacity for units Total manufacturing conversion costs Manufacturing conversion costs per unit Selling and customer-service capacity Total selling and customer-service costs Cost per customer of selling and customer -service capacity 2015 2016 20,700 20,850 $212 $215 60,000 61,000 $20 $21 25,000 25,000 $1,050,000 $1,075,000 $42 $43 60 59 Per customer $360,000 $362,500 $6,000 $6,144 Grading Rubric: Part A #1 #2 #3 #4 Growth Price-Recovery Productivity Part B #1 #2 #3 0.3 0.3 0.1 3 3 2 0.3 0.5 0.5 10 Computer Corporation produces no defective units but it wants to reduce direct materials usage per unit in 2016. Manufacturing conversion costs in each year depend on production capacity defined in terms of units that can be produced. Selling and customer-service costs for each year depend on the number of customers that the selling and customer-service functions are designed to support each year. They had 50 customers in 2015 and 52 in 2016. Required: Your solution should be on the analysis worksheet. Rounded to the nearest Dollar. You must use the Data Field above by linking and using formulas on the Analysis worksheet. Part A: Please reference the analysis worksheet, which should contain your solution and I have tried to get you started. You need to use cell references and use the "If" function to designate whether the component is Favorable or Unfavorable. On the designation of the total of the subvariances you must use a SUMIF statement. All of your computational answers must include cell references. If a variance is zero you do not have to designate the variance as U or F , since that will save you a little bit of time. I will take off 1/2 pts. For each designation that does not use the IF function and SUM IF function for the totaling of each of the components-growth, price recovery, and productivity. Variances should not be shown as a negative number, so please use the Absolute function, so none of your variances will show as a negative. Calculate the following using only cell references on the sheet called analysis: 1. What is the operating income for 2015? 2. What is the operating income for 2016? 3. What is the change in operating income from 2015 to 2016? 4. Calculate the growth, price-recovery, and productivity components that explain the change in operating income from 2015 to 2016. Make sure you break the growth and price-recovery into revenue and cost effect subcomponents. Part B: Answer the following questions based on your reading of chapter 13, application of this companies analysis, and our discussion of this topic in the discussion forum. 1. Which strategy is the company pursuing? Why? 2. Was the company's strategy successful in 2016? Why? 3. What do each of the components mean in respect to changes in operating income? You need to discuss Growth, price-recovery, and productivity. Type in name here Part A: Computer Corporation Income Statement For the Year ended Sales Costs Direct material costs Conversion costs Selling and Customer-service costs Total costs Operating income 2015 $4,388,400 2016 Guidance: Please click on the cell so that you can see that I did not type in the number but used cell references. Guidance: A good check figure. The change in Operating income should equal the combination of your 3 components. Guidance: Again, please note the cell referencing by clicking on the cell. Do not type in any numbers. #3 Change in operating income Growth Component: Revenue Cost: (20850-20700)*212 $31,800 F Materials CC Selling Total Growth Component Guidance: Please note that I have used the IF statement to determine if the variance is favorable or unfavorable. Guidance: Remember to use the SUMIF statement for the Growth, Price, and Productivity components Price Recovery: Revenue Cost: Material Conversion Cost Selling Costs Total Price Recovery Productivity: Material Conversion Cost Selling Costs Total Productivity Verification that total components equals change in income Part B: Answer the following questions based on your reading of chapter 13, application of this companies analysis, and our discussion of this topic in the discussion forum. 1. Which strategy is the company pursuing? Why? Worth .3 pt. 2. Was the company's strategy successful in 2016? Why? Worth 1/2 pt. 3. What do each of the components mean in respect to changes in operating income? You need to discuss Growth, price-recovery, and productivity. Worth 1/2 pt. for each component discussed. 12-22 (15 min.) Strategy, balanced scorecard. Stanmore Corporation makes a special-purpose machine, D4H, used in the textile industry. Stanmore has designed the D4H machine for 2013 to be distinct from its competitors. It has been generally regarded as a superior machine. Stanmore presents the following data for 2012 and 2013. Stanmore produces no defective machines, but it wants to reduce direct materials usage per D4H machine in 2013. Conversion costs in each year depend on production capacity defined in terms of D4H units that can be produced, not the actual units produced. Selling and customer-service costs depend on the number of customers that Stanmore can support, not the actual number of customers it serves. Stanmore has 75 customers in 2012 and 80 customers in 2013. Required: 1. Is Stanmore's strategy one of product differentiation or cost leadership? Explain briefly. 2. Describe briefly key measures that you would include in Stanmore's balanced scorecard and the reasons for doing so. SOLUTION 1. Stanmore Corporation follows a product differentiation strategy in 2011. Stanmore's D4H machine is distinct from its competitors and generally regarded as superior to competitors' products. To succeed, Stanmore must continue to differentiate its product and charge a premium price. 2. Balanced Scorecard measures for 2013 follow: Financial Perspective (1) Increase in operating income from charging higher margins, (2) price premium earned on products These measures indicate whether Stanmore has been able to charge premium prices and achieve operating income increases through product differentiation. Customer Perspective (1) Market share in high-end special-purpose textile machines, (2) customer satisfaction, (3) new customers Stanmore's strategy should result in improvements in these customer measures that help evaluate whether Stanmore's product differentiation strategy is succeeding with its customers. These measures are leading indicators of superior financial performance. Internal Business Process Perspective (1) Manufacturing quality and reduced wastage of direct materials, (2) new product features added, (3) order delivery time Improvements in these measures are expected to result in more distinctive products delivered to its customers and in turn superior financial performance. Learning and Growth Perspective (1) Development time for designing new machines, (2) improvements in manufacturing processes, (3) employee education and skill levels, (4) employee satisfaction Improvements in these measures are likely to improve Stanmore's capabilities to produce distinctive products that have a cause-and-effect relationship with improvements in internal business processes, which in turn lead to customer satisfaction and financial performance. 12-23 (30 min.) Strategic analysis of operating income (continuation of 12-22). Refer to Exercise 12-22. Required: 1. Calculate the operating income of Stanmore Corporation in 2012 and 2013. 2. Calculate the growth, price-recovery, and productivity components that explain the change in operating income from 2012 to 2013. 3. Comment on your answer in requirement 2. What do these components indicate? SOLUTION 1. Operating income for each year is as follows: Revenue ($40,000 200; $42,000 210) Costs Direct materials costs ($8 300,000; $8.50 310,000) Manufacturing conversion costs ($8,000 250; 8,100 250) Selling & customer service costs ($10,000 100; $9,900 95) Total costs Operating income Change in operating income 2012 $8,000,000 2013 $8,820,000 2,400,000 2,635,000 2,000,000 2,025,000 1,000,000 940,500 5,400,000 5,600,500 $2,600,000 $3,219,500 $619,500 F 2. The Growth Component Actual units of Selling Actual units Revenue effect = of output sold output sold price of growth in 2013 in 2012 in 2012 = (210 200) $40,000 = $400,000 F Actual units Units of input required of inputs Cost effect of used to growth for = to produce 2013 output produce variable costs in 2012 2012 ouput Cost effect of growth for = fixed costs Input inprice 2012 Actual Actual units of capacity in per unit 2012 because adequate units of Price of capacity capacity capacity exists to produce 2013 output in 2012 in 2012 in 2012 Kilograms of direct materials that would be required in 2013 to produce 210 units instead of the 200 units produced in 2012, assuming the 2012 input-output relationship continued into 2013, 300,000 equal 315,000 kilograms 210 . Manufacturing conversion costs and selling and 200 customer-service capacity will not change because adequate capacity exists in 2012 to support year 2013 output and customers. The cost effects of growth component are: Direct materials costs Manufacturing conversion costs Selling & customer-service costs Cost effect of growth (315,000 300,000) $8 = (250 250) $8,000 = (100 100) $25,000 = $120,000 U 0 0 $120,000 U In summary, the net increase in operating income as a result of the growth component equals: Revenue effect of growth $400,000 F Cost effect of growth 120,000 U Change in operating income due to growth $280,000 F The Price-Recovery Component Revenue effect of Selling price Selling price = price-recovery in 2013 in 2012 Actual units of output sold in 2013 = ($42,000 $40,000) 210 = $420,000 F Cost effect of price-recovery for = variable costs Units of input Input Input required to price in price in produce 2013 2013 2012 output in 2012 Cost effect of price-recovery for = fixed costs Actual units of capacity in Price per Price per 2012 because adequate unit of unit of capacity exists to produce capacity capacity in 2013 2013 output in 2012 in 2012 Direct materials costs Manufacturing conversion costs Selling & customer-service costs Cost effect of price-recovery ($8.50 $8) 315,000 = $157,500 U ($8,100 $8,000) 250 = 25,000 U ($9,900 $10,000) 100 = 10,000 F $172,500 U In summary, the net increase in operating income as a result of the price-recovery component equals: Revenue effect of price-recovery Cost effect of price-recovery Change in operating income due to price-recovery $420,000 F 172,500 U $247,500 F The Productivity Component Units of input Actual units of Cost effect of input used required to Input price productivity for = to produce produce 2013 2013 in 2013 variable costs output ouput in 2012 Actual units of capacity in Price per Actual Cost effect of units of 2012 because adequate productivity for = capacity capacity exists to produce unit of capacity in 2013 fixed costs 2013 output in 2012 in 2013 The productivity component of cost changes are Direct materials costs (310,000 315,000) Manufacturing conversion costs (250 250) Selling & customer-service costs (95 100) Change in operating income due to productivity $8.50 = $8,100 = $9,900 = $42,500 F 0 49,500 F $92,000 F The change in operating income between 2012 and 2013 can be analyzed as follows: Revenues Costs Operating income Income Statement Amounts in 2012 (1) $8,000,000 Revenue and Cost Effects of Growth Component in 2013 (2) $400,000 F Revenue and Cost Effect Cost Effects of of Price-Recovery Productivity Component Component in 2013 in 2013 (3) (4) $420,000 F Income Statement Amounts in 2013 (5) = (1) + (2) + (3) + (4) $8,820,000 5,400,000 120,000 U 172,500 U $92,000 F 5,600,500 $2,600,000 $280,000 F $247,500 F $92,000 F $3,219,500 $619,500 F Change in operating income 3. The analysis of operating income indicates that a significant amount of the increase in operating income resulted from Stanmore's product differentiation strategy. The company was able to continue to charge a premium price while growing sales. Stanmore was also able to earn additional operating income by improving its productivity. The productivity gains may be important from the standpoint of funding the product differentiation strategy and innovation (as has been the case with the pharmaceutical industry in recent years), but Stanmore's strategic focus has to be on differentiating its products. 12-24 (20 min.)Analysis of growth, price-recovery, and (continuation of 12-23). productivity components Suppose that during 2013, the market for Stanmore's special-purpose machines grew by 3%. All increases in market share (that is, sales increases greater than 3%) are the result of Stanmore's strategic actions. Calculate how much of the change in operating income from 2012 to 2013 is due to the industry-market-size factor, product differentiation, and cost leadership. How successful has Stanmore been in implementing its strategy? Explain. SOLUTION Effect of the industry-market-size factor on operating income Of the 10-unit increase in sales from 200 to 210 units, 3% or 6 (3% 200) units is due to growth in market size, and 4 (10 6) units is due to an increase in market share. The change in Stanmore's operating income from the industry-market size factor rather than from specific strategic actions is: 6 $280,000 (the growth component in Exercise 12-23) $168,000 F 10 Effect of product differentiation on operating income The change in operating income due to: Increase in the selling price of D4H (revenue effect of price recovery) $420,000 F Increase in price of inputs (cost effect of price recovery) 172,500 U Growth in market share due to product differentiation $280,000 (the growth component in Exercise 12-23) 4 10 112,000 F Change in operating income due to product differentiation $359,500 F Effect of cost leadership on operating income The change in operating income from cost leadership is: Productivity component $ 92,000 F The change in operating income between 2012 and 2013 can be summarized as follows: Change due to industry-market-size Change due to product differentiation Change due to cost leadership Change in operating income $168,000 F 359,500 F 92,000 F $619,500 F Stanmore has been successful in implementing its product differentiation strategy. More than 58% ($359,500 $619,500) of the increase in operating income during 2013 was due to product differentiation, i.e., the distinctiveness of its machines. It was able to raise the prices of its machines faster than the costs of its inputs and still grow market share. Stanmore's operating income increase in 2013 was also helped by a growth in the overall market and some productivity improvements. 12-25 (15 min.) Identifying and managing unused capacity (continuation of 12-22). Refer to Exercise 12-22. Required: 1. Calculate the amount and cost of (a) unused manufacturing capacity and (b) unused selling and customer-service capacity at the beginning of 2013 based on actual production and actual number of customers served in 2013. 2. Suppose Stanmore can add or reduce its manufacturing capacity in increments of 30 units. What is the maximum amount of costs that Stanmore could save in 2013 by downsizing manufacturing capacity? 3. Stanmore, in fact, does not eliminate any of its unused manufacturing capacity. Why might Stanmore not downsize? SOLUTION 1. The amount and cost of unused capacity at the beginning of year 2013 based on year 2013 production follows: Manufacturing, 250 210; (250 - 210) $8,100 Selling and customer service, 100 - 80; (100 - 80) $9,900 Amount of Unused Capacity 40 20 Cost of Unused Capacity $324,000 198,000 2. Stanmore can reduce manufacturing capacity from 250 units to 220 (250 30) units. Stanmore will save 30 $8,100 = $243,000. This is the maximum amount of costs Stanmore can save in 2013. It cannot reduce capacity further (by another 30 units to 190 units) because it would then not have enough capacity to manufacture 210 units in 2013 (units that contribute significantly to operating income). 3. Stanmore may choose not to downsize because it projects sales increases that would lead to a greater demand for and utilization of capacity. Stanmore may have also decided not to downsize because downsizing requires a significant reduction in capacity. For example, Stanmore may have chosen to downsize some more manufacturing capacity if it could do so in increments of say, 10, rather than 30 units. Also, Stanmore may be focused on product differentiation, which is key to its strategy, rather than on cost reduction. Not reducing significant capacity also helps to boost and maintain employee morale