I need help calculating the actvity rate and the customer profitability analysis for the project below. Amy has asked you, the new staff accountant, to proceed with the ABC analysis. Your job is to design a spreadsheet that will calculate the seven activity rates for the seven activities listed in Table $3$ and prepare a test of customer profitability analysis for a sample of four recent customers. The goal is that any staff accountant at the company would be able to use your spreadsheet to recalculate activity rates in the future and to analyze future customers.

All formulas should be prepared using cell references within Excel.

Your starting point is the provided Excel spreadsheet that has an existing starting tab labeled Table $2-$ Activity Analysis and Table $3-$ Cost Drivers. Start a new tab with the name $$Activity Rates$$ to calculate the necessary steps to finding the seven activity rates. You should use formulas to cell references in the given tabs in the template to assist your analysis. $($Remember$,$ any member of your team should be able to open the $$Activity Rates$$ tab and understand what is taking place.$)$

After preparing your $$Activity Rates$$ tab, prepare a $$Customer Analysis$$ tab for the four customers listed previously. Your tab can type in any information from Table $4$ as given data but all calculated fields should be using references to the given data and the activity rates tab. Your customer analysis should find the operating income for each customer and the operating margin.

Cost

Clerical wages $$65,200$

Salesperson Commission and wages $$741,300$

Interior Designer Wages $$295,800$

Warehouse Wages $$322,100$

Installer Wages $$165,000$

Advertising $$100,000$

Promotional Materials $$25,000$

Factory equipment depreciation $$65,400$

Maintenance $$45,500$

Shipping $$4,978,500$

Utilities and Fuel $$162,300$

Total $$6,966,100$Table $2$ Activity Analysis

PreOrder Consultations Interior Design Site Visits Activities Warehousing Activities Delivery Installation Post$-$Sale Customer Service Total

Clerical wages $50\%15\%5\%10\%20\%100\%$

Salesperson Commissions and Wages $40\%40\%20\%100\%$

Interior Designer Wages $80\%20\%100\%$

Warehouse Wages $80\%10\%10\%100\%$

Installers Wages $95\%5\%100\%$

Advertising $75\%10\%15\%100\%$

Promotional Materials $70\%15\%15\%100\%$

Factory equipment depreciation $50\%10\%40\%100\%$

Maintenance $60\%10\%30\%100\%$

Shipping $100\%100\%$

Utilities and Fuel $20\%60\%10\%10\%100\%$Table $3-$ Cost Drivers and Quantities

Activity CostDriver Driver Quant

PreOrder Consultation # of sales calls $315$

Interior Design # of design hours $7,960$

Site Visit Activities # of site visits $180$

Warehousing Activities Cost of Goods Sold $$19,750,000\mathrm{.}00$

Delivery Pounds shipped $3,552,500$

Installation # of installer hours $6,000$

Post$-$Sale Customer Support # of support hours $2,400$