Scenario 1

Parts a and b: Celimar Company's Western Division earned operating income last year as shown in the following income statement:

Sales	$480,000
Cost of Goods Sold	222,000
Gross Margin	$258,000
Selling and Administrative Expenses	210,000
Operating Income	$48,000

At the beginning of the year, the value of operating assets was $277,000. At the end of the year, the value of the operating assets was $323,000. Celimar Company requires a minimum rate of return of 12%.

Part c: Celimar Company's Western Division earned net income last year as shown in the following income statement:

Sales	$480,000
Cost of Goods Sold	222,000
Gross Margin	$258,000
Selling and Administrative Expenses	210,000
Operating Income	$48,000
Less: Income taxes (30%)	14,400
Net Income	$33,600

Total capital employed equaled $300,000. Celimar Company's actual cost of capital is 10%.

Open the attached spreadsheet. Complete question parts a - c on the spreadsheet. Be sure to use formulas in the gray shaded cells. The results of the formulas will be needed to perform the analysis required in the CengageNOW problem. Compare your spreadsheet answers for question parts a - c to the following Cornerstones to confirm that you have entered your formulas correctly.

a.

Step 1: Calculate the average operating assets. Step 2: Calculate Margin. Step 3: Calculate Turnover. Step 4: Calculate Return on Investment (ROI).

b. Using the data from part a, calculate the residual income. c. Calculate Economic Value Added (EVA) for the division.

Scenario 2

The executive team at Celimar Company's Western Division has requested that you perform an analysis. In some cases, you will need to rearrange the equations used in question parts a, b, or c to perform the requested analysis. Respond to questions parts d - f in this problem regarding the analysis.

d. Using the spreadsheet information above in parts a and b, determine the answers to the following if the Celimar company expects the ending assets to be $361,000 (instead of $323,000).

1. What is the Average Operating Assets?

2. Will the Margin change with the ending assets to be $361,000? Yes

3. What is the Margin (rounded to 2 decimal places)?

4. What is the Turnover (rounded to 1 decimal place)?

5. What is ROI?

6. What is the Residual Income?

e. Change the ending assets back to $323,000 on the spreadsheet in question part a. Using the spreadsheet information in part b, what is the Residual income if the Celimar Company increases the minimum rate of return to 15%?

f. Using the spreadsheet information above in part c, what is the EVA if the Actual Percentage cost of capital decreases from 10% to 8%?

g. Using the spreadsheet information in part c, what is the EVA if the income tax rate is changed from 30% to 20% assuming all the other information remained the same.