# Excel Applications for Accounting Principles

Business

## Quiz 25 :Segment Income Statement Dept

PROBLEM DATA Ottawa Scuba Supply, Inc., has divided its operations into two departments: Supplies, devoted to selling scuba tanks and supplies; and Apparel, devoted to selling sports clothing and underwater apparel. Departmental expense accounts are kept for direct expenses, but indirect (common) expenses are not allocated until the end of the accounting period. Selected data at June 30, the end of the current fiscal year, are as follows: The bases for allocating indirect expenses are as follows: REQUIREMENT As the accountant for Ottawa Scuba Supply, you have been asked to prepare a departmental income statement. Review the worksheet DEPT that follows these requirements. All of the problem information relating to direct expenses has been entered into the Answer Section. You will be allocating the indirect expenses to the appropriate departments.
Free
Essay
Answer:

Answer:

Open and review the worksheet indicated. In this problem you will prepare Company O's departmental income statement. You will use this income statement to assess performance. The data is already inputted into the worksheet. The unedited worksheet should look like this:

PROBLEM DATA Ottawa Scuba Supply, Inc., has divided its operations into two departments: Supplies, devoted to selling scuba tanks and supplies; and Apparel, devoted to selling sports clothing and underwater apparel. Departmental expense accounts are kept for direct expenses, but indirect (common) expenses are not allocated until the end of the accounting period. Selected data at June 30, the end of the current fiscal year, are as follows: The bases for allocating indirect expenses are as follows: REQUIREMENT Open the file DEPT from the website for this book at cengagebrain.com. Enter the formulas where indicated on the worksheet. As a hint, FORMULA1 is =(B20/D20)*B8. The cells that contain zeros now have been preprogrammed. They will change to nonzero values as the formulas are entered. Enter your name in cell A1. Save your file as DEPT2. Print the worksheet when done. Also print your formulas. Check figure: Operating income, Supplies department (cell C47), \$68,076.
Free
Essay
Answer:

Answer:

Open the file indicated. Input the formulas where indicate in the departmental worksheet using cell references so that the worksheet automatically updates when any values are changed. Use the information in the instructions to determine how each direct expense is allocated to each department when creating the formulas. When you are finished entering all of the formulas, the departmental income statement for Company O should look like this:

PROBLEM DATA Ottawa Scuba Supply, Inc., has divided its operations into two departments: Supplies, devoted to selling scuba tanks and supplies; and Apparel, devoted to selling sports clothing and underwater apparel. Departmental expense accounts are kept for direct expenses, but indirect (common) expenses are not allocated until the end of the accounting period. Selected data at June 30, the end of the current fiscal year, are as follows: The bases for allocating indirect expenses are as follows: REQUIREMENT What conclusions can be drawn from this departmental income statement
Free
Essay
Answer:

Answer:

Below is a copy of the departmental income statement for Company O.
Based on the information in this income statement, you can see that overall the company is doing okay. However, the apparel department is spending more money than they are bringing in with revenue. Company O either needs to reexamine how they allocate expenses to each department, or see what areas could be reduced in expenses.

WHAT-IF ANALYSIS The sales manager for the Apparel department maintains that rent should be allocated on the basis of net sales rather than on floor space. The manager thinks this is fairer since the Supplies department occupies more "prime location" floor space than does the Apparel department. Alter FORMULA1 and FORMULA2 (cells B40 and C40, respectively) on the worksheet to see what effect this "fairer" allocation would have on each department's operating income. Save the revision as DEPT4. Print the worksheet when done.
Essay
Answer:
WHAT-IF ANALYSIS Comment on the results of this change and on the appropriateness of the new allocation scheme.
Essay
Answer:
CHART ANALYSIS The sales manager of the Apparel department feels she has her direct expenses under control, but she is still concerned with the amount of indirect expenses allocated to her department. She has prepared a chart showing the percentage of sales made by her department and the percentage of expenses incurred. Open DEPT2 and click the Chart sheet tab. Then open DEPT4 and click the Chart sheet tab. Is her concern warranted Explain. When the assignment is complete, close the files without saving them again.
Essay
Answer: