Blue Ridge Manufacturing

Attached is an excel spread sheet that I need assistance with... Thank you for your help!

-------------
Blue Ridge Manufacturing

Blue Ridge Manufacturing produces knit apparel in a modern plant located in the Blue Ridge Mountain-region of North Carolina. Overall, the company is profitable, as the segmented income statement in the first tab of the spreadsheet shows.

The company sells three product lines, which differ in the amount of customization. The sports line is a v- or crew-neck sweater customized for school color and yarn type (bulky or fine gauge). Each sweater order requires an artistic development of the school's logo. After development, the logo is either knitted into the sweater or sewn onto it (like athletic letters). The children's line is a collection of infant sweater and pull on pants. These are standard and vary only by color and size. The designer line contains a variety of standard products, redesigned each season, and is sold to department stores. These lines vary in sales volume, with the designer line selling most, the children's line selling a close second, and the sports line having the smallest sales.

Recently, sales of the sports line have increased, although the nature of the products has not changed. Management cannot account for the increasing market share and wants more information before devoting an even greater share of production resources to this market segment. The cost accounting system is fairly sophisticated with respect to variable product costs, but thus far indirect costs have been allocated to products based on their contribution margin. Management has concern that indirect manufacturing expenses may be affected by which product line is produced.

During a review of this issue, management realized that many general overhead items represented resources consumed in proportions different than the relative gross profit (contribution margin) of the product line (their current basis for allocation of the indirect manufacturing expenses). Management decided to use activity-based costing to determine product line profitability (income). The table in the second tab of the spreadsheet contains the results of the management's analysis. The indirect costs have been pooled into 12 activity pools, and an activity measure has been selected for each pool. The amount of each activity measure consumed by each product line is also given. Note that the total costs are the same as the total indirect costs in the segmented income statement.

1. Use an Excel spreadsheet to determine the amount of indirect costs that would be attributable to each product line using activity-based costing (ABC). Data is in the ABC Analysis tab of the file.

2. Using sales, variable product costs, and commissions originally given (in the Segmented Income Statement tab of the Excel file), and your findings from part one, recalculate the profitability of each product line (using an Excel spreadsheet). (You will basically be replacing the indirect cost row in the spreadsheet in the Segmented Income Statement with your answers to part one, and recalculating income for each product line. The total indirect costs and the total income will be the same. The amounts for each product line will just be different than those originally given.

3. The firm was considering devoting a greater share of the production resources to the sports line. What do your results in part two imply about the product line's continued success, and whether the firm should devote more resources to that line? You should consider and answer the following questions: Should the sports line be dropped? Can you determine why market share (sales) for the sports line was increasing? Are there other things the firm could do to make the sports line more profitable, related to pricing, product options, cost reduction, etc.? What are the benefits of an ABC allocation of indirect costs that a single, volume-based allocation cannot provide?