Here is data and some facts collected by an analyst after a visit to Pitt Pens factory. The factory produces pens. They started with basic Black and Blue pens in 2015, but last year introduced new Red and Purple pens, after their Marketing team recommended expanding the product offering. The pitch from marketing was that these new colors could command premium prices. However, the new pens unlike the traditional colors had a more complex manufacturing process set up and lower demand. Exhibit 1: SUPPORT EXPENSES Expense Category Expense Indirect Labor $20,000 Fringe Benefits (40% of labor expense on both direct and indirect labor) $16,000 Computer Systems $10,000 Machinery Depreciation $ 8,000 Machine Maintenance $ 4,000 Energy (for Machines) $ 2,000 TOTAL $60,000 Exhibit 2: TRADITIONAL INCOME STATEMENT BLUE BLACK RED* PURPLE* Total Sales $75,000 $60,000 $13950 $1,650 $150,600 Material Costs $25,000 $20,000 $4,680 $ 550 $ 50,230 Direct Labor $10,000 $ 8,000 $1,800 $ 200 $ 20,000 Overhead at 300% $30,000 $24,000 $5,400 $ 600 $ 60,000 Total Operating Income $10,000 $ 8,000 $2,070 $ 300 $ 20,370 Return on Sales 13.6% 13.3% 14.8% 18.2% 13.5%

New products RED and Purple pens.

Exhibit 3 : Direct Costs and Activity Drivers BLUE BLACK RED PURPLE Total Production Sales Volume 50,000 40,000 9,000 1,000 100,000 Unit Selling Price $1.50 $1.50 $1.55 $1.65 Materials Unit Cost $0.50 $0.50 $0.52 $0.55 Direct Labor hrs/unit 0.02 0.02 0.02 0.02 Machine hrs/unit 0.1 0.1 0.1 0.1 Production Runs 50 50 38 12 150 Setup time per run in hours 4 1 6 4 Total setup time hours 200 50 228 48 526 Parts Administration 1 1 1 1 4

Notes taken after Site Visit to the factory: (1) Traditional products were Blue and Black pens. Company introduced last year new Red and Purple pens, after recommendations from the Marketing team. Profitability as computed in Exhibit 2 shows these new pen types have higher profitability, but overall company profitability had come down from previous years. The profitability analysis was conducted using a simple/traditional cost allocation model often deployed for high volume production using an overhead rate. All of the plant's indirect support expenses (totally $60,000) were aggregated at the plant level and allocated to the four products based on direct labor content. Currently this burden overhead rate was 300% of direct labor. (2) Traces of Black color when making Red and Purple pens, created a quality challenge. (3) Fringe Benefits were 40% of labor expenses - direct and indirect together. Breakdown of Indirect Labor, including its fringe benefits: 50% was for scheduling or handling production runs; 40% was for physical change over from one color pen type to another and a function of the set up time required; 10% was for maintaining records for the four products ( Bill of Material, Specifications, inventory control etc) (4) Computer System expenses was for computer time in scheduling the production runs, about 80% and the remaining time 20% was for maintaining records on the four products like process and engineering change notifications. (5) The three category of expenses - Machinery, Maintenance and Energy were incurred to supply the machine capacity. The plant had a practical capacity of 10,000 hours of production time.

ASSIGNMENT: Q1 Use the cost drivers framework, activity based, to calculate the true profitability (return on sales) of the new pens (Red and Purple) and compare it to that of traditional pens Blue and Black. For purpose of this Quiz, (to reduce your computational effort )- calculate for BLUE and RED pens only and compare the results with the old cost way of costing (Exhibit 2). Q2 What actions are stimulated by the cost modelling analysis ? Suggested Activity Cost Drivers to analyze Indirect Costs: Activity Handle Production runs Set up Machines Administer Parts Run Machines Etc. Measured As Number of runs Set up time in hours Per product Machine hours Calculate the cost driver rate for each activity.

Please solve this using Excel, showing me the formulas you used while solving it. All the details will be more than appreciated.

Thank you!