Instructions:

1. The purpose of this project is to apply the regression method and perform CVP analysis using Excel. To complete this assignment, you have an option of working independently or working in a group of two.

2. You are required to run regressions and construct a worksheet to complete other requirements in Excel. You are expected to have basic Excel skills and use formulas to compute your answers, rather than using the calculator and typing numbers in Excel (it looks silly and makes using Excel pointless if you do that). 2 points will be deducted for a lack of basic Excel skills.

3. Upload your Excel file to Canvas by 11:59 pm, Wednesday, October 26th. If you are working in a group of two, only one file needs to be uploaded. Make sure both of your names are printed in the file. Late submission will not be granted for credit.

4. This project requires you to run a multiple regression, where multiple independent variables are employed. While the multiple regression is not covered in class, you should be able to search the website and find out how the multiple regression is performed in Excel.

---

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: .90

Direct Materials: .35

Direct Labor: .25

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	5800	18	590
February	4500	6	560
March	4790	12	575
April	5500	15	615
May	6250	20	660
June	5100	10	552
July	5545	16	630
August	5390	12	608
September	5300	11	635
October	4920	12	550
November	5450	14	582
December	5420	14	610

Selling and administrative expenses, all fixed, amounted to $187,000 last year.

---

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 (c) a multiple regression using both number 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: 854,680 boxes to break even.

I know how to do number 1, I need help doing number 2.