Question

Activity-Based Costing and Activity-Based Management ABC, Retail Product-line Profitability Free work cells Answer cells Fitzgerald Supermarkets (FS) operates at capacity and decides to apply ABC analysis to three product lines: baked goods, milk and fruit juice, and frozen foods. It identifies four activities and their activity cost rates as follows: Ordering Delivery and receipt of merchandise Shelf-stocking Customer support and assistance $95 per purchase order $76 per delivery $19 per hour $0.15 per item sold The revenues, cost of goods sold, store support costs, activities that account for the store support costs, and activity area usage of the three product lines can be found on the "PRODUCTLINEINFO" tab. The revenues, cost of goods sold, store support costs, activities that account for the store support costs, and activity-area usage of the three product lines are as follows: Baked Goods Milk and Fruit Juice Frozen Products $60,000 $41,000 $66,500 $51,000 $50,500 $32,000 Financial data Revenues Cost of goods sold Store support Activity area usage (cost-allocation base) Ordering (purchase orders) Delivery (deliveries) Shelf-stocking (hours) Customer support (items sold) 24 14 60 36 44 120 170 15,400 150 20,200 20 7,960 Under its simple costing system, FS allocated support costs to products at the rate of 30% of cost of goods sold. 30% (Always use cell references and formulas where appropriate to receive full credit.) 1. Use the simple costing system to prepare a product-line profitability report for FS. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions or Productlineinfo tabs you will be marked wrong.) Total Revenues Costs Cost of goods sold Store support Total costs Operating income Operating income/Revenues percentage Baked Goods $60,000 $19,000 41,000 12,300 113,300 $6.700 Milk and Fruit Juice $66,500 $15,500 51,000 15,300 132,800 $200 Frozen Products $50,500 $18,500 32,000 9,600 92,100 $8,900 $177,000 $53,000 $124,000 $37,200 $338,200 $ 15,800 2. Use the ABC system to prepare a product-line profitability report for FS. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions or Productlineinfo tabs you will be marked wrong.) Baked Goods $60,000 $19,000 41,000 4,180 9,120 Revenues Costs Cost of goods sold Ordering Delivery Shelf-stocking - Customer support Total costs Operating income Operating income/Revenues percentage Milk and Fruit Juice $66,500 $15,500 51,000 2,280 4,560 2,850 3,030 130,220 Frozen Products $50,500 $18,500 32,000 1,330 2,736 380 1,194 88,140 Total $177,000 $53,000 $ 124,000 $7,790 $16,416 $6,460 $6,534 $338,200 3,230 2,310 119,840

I don't know if my numbers are right, but I need help calculating the operating income and the operating income in revenues percentage

Answer 1

Requirement 1 & Req. 2 as follows:

1 Baked Goods $60,000 $41.000 $12,300 $53.300 Milk and Fruit Juice $66,500 $51.000 $15,300 66300 D Frozen Product $50,500 $32,000 9600 41600 Total $177,000 124000 37200 161200 2 Revenues 3 Less: COGS 4 Less: Store supports 5 Total Cost 6 7 Operating Income 8 Operating Income Revenue percentage 9 10 2. Use ABC Sytem $6.700 11.17% $200 0.30% $8.900 17.62% $15,800 8.93% 11 Baked Goods $60,000 Milk and Fruit Juice $66,500 Frozen Product $50,500 Total $177,000 12 Revenues 13 14 Less: COGS 15 Ordering Cost ($95 purchase per order) 16 Delivery (S76 per delivery) 17 Shelf-stocking (hours) ($19 per hour) 18 Customer support ($0.15 per item sold) 19 Total Cost 20 Operating Income 21 Operating Income Revenue percentage $41.000 $4.180 $9.120 $3,230 $2.310 $59,840 $160 0.27% $51.000 $2.280 $4,560 $2.850 $3,030 $63,720 $2.780 4.18% $32.000 $1,330 $2.736 $380 $1.194 $37,640 $12,860 25.47% $124,000 $7,790 $16,416 $6,460 $6,534 $161,200 $15.800 8.93%

___________________________________________________________________________________________

Working as follows:

Baked Goods $60,000 $41.000 =B3*30% =B3+B4 C Milk and Fruit Juice $66,500 $51.000 =C3*30% =C3+C4 D Frozen Product $50,500 $32.000 =D3*30% =D3+D4 Total $177,000 124000 =SUM(B4:D4) =E4+E3 =B2-B5 =B7/B2 =C2-C5 =C7/C2 =D2-D5 =D7/D2 =E2-E5 =E7/E2 1 2 Revenues 3 Less: COGS 4 Less: Store supports 5 Total Cost 6 7 Operating Income 8 Operating Income Revenue percentage 9 10 2. Use ABC Sytem 11 12 Revenues 13 14 Less: COGS 15 Ordering Cost ($95 purchase per order) 16 Delivery ($76 per delivery) 17 Shelf-stocking (hours) ($19 per hour) 18 Customer support (S0.15 per item sold) 19 Total Cost 20 Operating Income 21 Operating Income Revenue percentage Baked Goods $60,000 Milk and Fruit Juice $66,500 Frozen Product $50,500 Total $177,000 $41.000 =44*95 =120*76 =19*170 =0.15*15400 =SUM(B14:B18) =B12-B19 =B20/B12 $51.000 =24*95 =60*76 =19*150 =0.15*20200 =SUM(C14:C18) =C12-C19 =C20/C12 $32.000 $124.000 =14695 | =SUM(B15-D15) | =36*76 =SUM(B16-D16) =19*20 =SUM(B17D17) =0.15*7960 =SUM(B18:D18) =SUM(D14:D18) =SUM(E14:E18) =D12-D19 =E12-E19 =D20/D12 =E20/E12