This must be done in excel. Please show step by step. Please answer all parts of the questions. Thank you!
Page 2 of 3 - ZOOM Sorrentino Company Sorrentino Company, which has been in business for one year, manufactures specialty Italian pastas. The pasta products start in the mixing department, where durum flour, eggs, and water are mixed to form dough. The dough is kneaded, rolled flat, and cut into fettucine or lasagna noodles, then dried and packaged. Paul Gilchrist, controller for Sorrentino Company, is concerned because the company has yet to make a profit. Sales were slow in the first quarter but really picked up by the end of the year. Over the course of the year, 718,200 boxes were sold. Paul is interested in determining how many boxes must be sold to break even. He has begun to determine relevant fixed and variable costs and has accumulated the following per unit data: Price Direct materials Direct labor $0.90 0.35 0.25 6 He has had more difficulty separating overhead into fixed and variable components. In examining overhead-related activities, Paul has noticed that machine hour appear to be closely correlated with units in that 100 boxes of pasta can be produced per machine hour. Setups are important batch-level activity. Paul has accumulated the following information on overhead costs, number of setups, and machine hours for the past 12 months. Month Overhead Number of Setups Machine Hours January $5.800 18 592 February 4.500 6 560 March 4,790 12 April 5,500 15 May 6.250 20 June 5.100 10 552 July 5,532 16 630 August 12 602 September 5.300 October 4.900 12 550 November 5.450 582 December 5,480 14 618 575 613 660 5,409 11 635 14 Selling and administrative expenses, all fixed, amounted to $185,000 last year. - ZOOM October November December 4.900 5,450 5,480 12 14 14 550 582 618 Selling and administrative expenses, all fixed, amounted to $185,000 last year. In the second year of operations, Sorrentino Company has decided to expand into the production of sauces to top its pastas. Sauces are also started in the mixing department, using the same equipment. The sauces are mixed, cooked, and packaged into plastic containers. One jar of sauce is priced at $2 and required $0.75 of direct materials and $0.50 of direct labor. 50 jars of sauce can be produced per machine hour. The production manager believes that with careful scheduling, he can keep the total number of setups (for both pasta and sauce) to the same number as used last year. The marketing director expects to increase selling expense by S45,000 per year to promote the new product and believes Sorrentino Company can two boxes of pasta for every one jar of sauce. Required: 1. Separate overhead into fixed and variable components using regression analysis. Run three regressions, using the following independent variables: (a) number of setups, (b) number of machine hours, and ) a multiple regression using both number of setups and machines. Which regression equation is best? Why? (4 pts) - ZOOM expects to increase selling expense by S45,000 per year to promote the new product and believes Sorrentino Company can sell two boxes of pasta for every one jar of sauce. Required: 1. Separate overhead into fixed and variable components using regression analysis. Run three regressions, using the following independent variables: (a) number of setups, (b) mumber of machine hours, and (c) a multiple regression using both mumber of setups and machines. Which regression equation is best? Why? (4 pts) 2. Using the results from (1) above, calculate the number of boxes of pasta which must be sold to break even for the year before the expansion into the production of sauces. (7 pts) Tips: (a) Carefully interpret the regression coefficients (i.e., what do the intercept and slope represent?) (b) The setup cost is a batch-level cost (i.e., a step cost). How should it be treated when computing the break-even point? (c) Check figure: 847,761 boxes to break even. 3. Now consider the production of sauces. Using the results of multiple regression equation, calculate the break-even number of boxes of pasta and jars of sauce, respectively. (7 pts) 4. Suppose that the production manager is wrong and that the number of setups doubles. Calculate the new break-even number of boxes of pasta and jars of sauce, respectively. (3 pts) 5. Refer to the original data. Suppose that only 30 jars of sauce can be produced per machine hour and that Sorrentino Company will sell 3 boxes of pasta for every one jar of sauce. Calculate the new break-even number of boxes of pasta and jars of sauce, respectively. (5 pts) 6. Based on the results in Requirements (4) and (5), explain how changes in setup costs and sales mix affect the break-even points for Sorrentino Company. (4 pts) Tips: Explain how doubling up the number of setups and changing the sales mix impact contribution margin and fixed costs, and hence the break-even points. Your answer should be generalizable (not just for changes specified in this project), and indicate how the break-even points would increase/decrease as a result of those changes. Page 2 of 3 - ZOOM Sorrentino Company Sorrentino Company, which has been in business for one year, manufactures specialty Italian pastas. The pasta products start in the mixing department, where durum flour, eggs, and water are mixed to form dough. The dough is kneaded, rolled flat, and cut into fettucine or lasagna noodles, then dried and packaged. Paul Gilchrist, controller for Sorrentino Company, is concerned because the company has yet to make a profit. Sales were slow in the first quarter but really picked up by the end of the year. Over the course of the year, 718,200 boxes were sold. Paul is interested in determining how many boxes must be sold to break even. He has begun to determine relevant fixed and variable costs and has accumulated the following per unit data: Price Direct materials Direct labor $0.90 0.35 0.25 6 He has had more difficulty separating overhead into fixed and variable components. In examining overhead-related activities, Paul has noticed that machine hour appear to be closely correlated with units in that 100 boxes of pasta can be produced per machine hour. Setups are important batch-level activity. Paul has accumulated the following information on overhead costs, number of setups, and machine hours for the past 12 months. Month Overhead Number of Setups Machine Hours January $5.800 18 592 February 4.500 6 560 March 4,790 12 April 5,500 15 May 6.250 20 June 5.100 10 552 July 5,532 16 630 August 12 602 September 5.300 October 4.900 12 550 November 5.450 582 December 5,480 14 618 575 613 660 5,409 11 635 14 Selling and administrative expenses, all fixed, amounted to $185,000 last year. - ZOOM October November December 4.900 5,450 5,480 12 14 14 550 582 618 Selling and administrative expenses, all fixed, amounted to $185,000 last year. In the second year of operations, Sorrentino Company has decided to expand into the production of sauces to top its pastas. Sauces are also started in the mixing department, using the same equipment. The sauces are mixed, cooked, and packaged into plastic containers. One jar of sauce is priced at $2 and required $0.75 of direct materials and $0.50 of direct labor. 50 jars of sauce can be produced per machine hour. The production manager believes that with careful scheduling, he can keep the total number of setups (for both pasta and sauce) to the same number as used last year. The marketing director expects to increase selling expense by S45,000 per year to promote the new product and believes Sorrentino Company can two boxes of pasta for every one jar of sauce. Required: 1. Separate overhead into fixed and variable components using regression analysis. Run three regressions, using the following independent variables: (a) number of setups, (b) number of machine hours, and ) a multiple regression using both number of setups and machines. Which regression equation is best? Why? (4 pts) - ZOOM expects to increase selling expense by S45,000 per year to promote the new product and believes Sorrentino Company can sell two boxes of pasta for every one jar of sauce. Required: 1. Separate overhead into fixed and variable components using regression analysis. Run three regressions, using the following independent variables: (a) number of setups, (b) mumber of machine hours, and (c) a multiple regression using both mumber of setups and machines. Which regression equation is best? Why? (4 pts) 2. Using the results from (1) above, calculate the number of boxes of pasta which must be sold to break even for the year before the expansion into the production of sauces. (7 pts) Tips: (a) Carefully interpret the regression coefficients (i.e., what do the intercept and slope represent?) (b) The setup cost is a batch-level cost (i.e., a step cost). How should it be treated when computing the break-even point? (c) Check figure: 847,761 boxes to break even. 3. Now consider the production of sauces. Using the results of multiple regression equation, calculate the break-even number of boxes of pasta and jars of sauce, respectively. (7 pts) 4. Suppose that the production manager is wrong and that the number of setups doubles. Calculate the new break-even number of boxes of pasta and jars of sauce, respectively. (3 pts) 5. Refer to the original data. Suppose that only 30 jars of sauce can be produced per machine hour and that Sorrentino Company will sell 3 boxes of pasta for every one jar of sauce. Calculate the new break-even number of boxes of pasta and jars of sauce, respectively. (5 pts) 6. Based on the results in Requirements (4) and (5), explain how changes in setup costs and sales mix affect the break-even points for Sorrentino Company. (4 pts) Tips: Explain how doubling up the number of setups and changing the sales mix impact contribution margin and fixed costs, and hence the break-even points. Your answer should be generalizable (not just for changes specified in this project), and indicate how the break-even points would increase/decrease as a result of those changes
