The W. A. Franke College of Business

ACC 256: Principles of Accounting – Managerial

Fall Semester, 2009

Spreadsheet Assignment #2 Due 12/02/09 beginning of class

Objective

The objective of this spreadsheet assignment is to further your understanding of segmented income statements, ROI calculations, and residual income calculations. This assignment is worth 10 course points. To earn maximum points it is important to correctly answer the five questions that appear at the end of this assignment sheet.

Instructions

Bikers Bakery is headquartered in Phoenix. The firm manufactures, sells, and distributes organic bread from four bakeries in Phoenix, Kingman, Tucson and Flagstaff. Operating information for the most recent year is presented in Table 1 below:

Table 1: Operating Information

Phoenix / Kingman / Tucson / Flagstaff
Units Sold / 65,000 / 60,000 / 55,000 / 80,000
Unit Price / $ 4.90 / $ 5.10 / $ 5.00 / $ 5.50
Variable Expenses per Unit
Variable COGS / $ 1.70 / $ 2.10 / $ 1.80 / $ 2.20
Selling and Distribution Expenses / 0.10 / 0.10 / 0.10 / 0.10
Traceable Fixed Expenses
Depreciation / $ 40,000 / $ 34,000 / $ 28,000 / $ 48,000
Management Salaries / 75,000 / 85,000 / 70,000 / 90,000
Advertising / 50,000 / 30,000 / 40,000 / 45,000
Warehouse Rent / 10,000 / 8,750 / 9,500 / 10,500
Training / 3,500 / 3,000 / 2,000 / 4,000
Traceable Segment Assets / $ 200,000 / $ 170,000 / $ 140,000 / $ 240,000
Common Corporate Expenses
CEO Salary / $145,000
Administrative Staff / 60,000
Headquarters Depreciation / 20,000
Minimum Required Rate of Return / 15%
Common Headquarters’ Assets / $ 150,000

Required:

1. Copy Table 1 into the first tab of an Excel workbook. Rename the tab “Input”.

2. Rename the second tab of the workbook “Segmented Income Statement”. Prepare a Income Statement segmented by region for Bikers Bakery using a format similar to the top schedule in Exhibit 12-3 in your text. Show individual expenses in your segmented statement, do not aggregate them. You should use only Excel formulas and cell references to the input sheet as you produce your income statement.

3. Copy Table 2 into your spreadsheet below the segmented income statement.

·  Compute Margin, Turnover, ROI and Residual Income using formulas and cell references.

Table 2: Performance Metrics:

Phoenix / Kingman / Tucson / Flagstaff
Margin (Based on Divisional Margin)
Turnover
ROI
Residual Income
Assets
Minimum Required Rate of Return
Minimum Return
Residual Income

4. The Flagstaff facility is operating at capacity. At the beginning of the current year the manager of the facility considered an expansion that would increase capacity. The expansion would result in an additional investment of $60,000. With the expansion, an additional 17,000 loaves of bread could be sold. The expansion would also increase fixed expenses as shown in Table 3 below:

Table 3: Flagstaff Expansion.

Flagstaff Expansion:
Units sold / 17,000
New Assets / $ 60,000
Additional Depreciation / $ 12,000
Additional Management Salaries / 25,000
Additional Advertising / 5,000
Additional Training / 2,500

·  Copy Table 3 to your input sheet, below the original data.

·  Copy the segmented income statement from requirement 2 to create a third tab in your spreadsheet. (Left click on the segmented income statement tab, choose Move or Copy, check the box that says Create a Copy, and click OK.) Rename the tab “Expansion’.

·  Delete the columns for the Total Company, Phoenix, Kingman, and Tucson. Do not delete the Income Statement labels or the data from the Flagstaff column. Add a column after the Flagstaff column entitled Expansion, and another column for “Flagstaff with Expansion” as shown below

Flagstaff / Expansion / Flagstaff with Expansion
Sales, etc. / $440,000 etc.

·  Using formulas and cell references fill in the income effect of the expansion. Stop at the Division (Segment) margin line. Do not include common fixed expenses.

·  Add the Expansion column data to the original Flagstaff data to fill in the Flagstaff with Expansion column.

·  Compute Margin, Turnover, ROI and Residual Income using formulas and cell references for the Expansion and Flagstaff with Expansion data.

·  Save your workbook.

5. Using the information from your spreadsheet, prepare answers to the following questions:

1.  Using ROI and NOT considering the expansion, which segment is the top performer? Which is the worst performer?

2.  Using Residual Income and NOT considering the expansion, which divisions have acceptable performance?

3.  From the perspective of the firm, if the minimum rate of return is 15%, should the Flagstaff facility expand? Briefly explain.

4.  Assume that ROI is used to measure managers’ performance. The Flagstaff manager rejected the expansion. Why?

5.  Would the project have been rejected if Residual Income was used to measure managers’ performance?

Briefly explain.

Submission Instructions: Save a copy of your workbook in your ACC 256 folder on the J: drive. The workbook should have two worksheets:

In addition, sign and hand in this sheet at the beginning of class on the due date. You must sign this sheet in order for me to grade your spreadsheet.

I understand that Spreadsheet 2 is an individual assignment. If it is found that I have completed this assignment by copying another’s work, in whole or in part, or allowed another student to copy my work, I understand that I will receive an F as my course grade in ACC 256.


Student Signature______


Student Name (PRINT)______

Date______

2