Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Acct 4 5 2 Project 3 - ABC Analysis by Amy Ford Amy s Interiors designs and sells office furniture configurations. The company s customers

Acct 452 Project 3- ABC Analysis by Amy Ford
Amys Interiors designs and sells office furniture configurations. The companys customers are hospitals, K-12 schools, doctor offices, churches, municipalities and universities.
Amys interiors purchases the chairs and furniture from various wholesale furniture providers. Amys provides the key services of designing the furniture layouts and installing the furniture.
Amys Interiors consults with potential clients first to determine the scale of the project. Based on the location sizes and needs, Amys will negotiate a contract price with the client. Each client will then work with an interior designer to finalize the design. Some customers require a site visit, which Amys arranges for no additional cost to the customer. The clerical staff enters customer orders to schedule deliveries with various vendors. The warehouse team unloads shipments from vendors and then loads trucks for delivery of items to the customer location. Amys team of installers arrives on site the day after the delivery to unload, setup and install the customers design.
Amys offers a no-questions asked customer support service for one year following the installation. These support hours include fixing scratches in the tables and desks, adjusting settings and locations and other various items that the customer needs after using the furniture during the first year. Amys does not charge for post-sale support.
As the company has grown, so have the costs related to running the business. As operating costs near $7,000,000 for the past year, Amys Interiors is looking for analysis to determine if the current operations can be improved. Amy heard about activity-based costing (ABC) at a professional development conference and has already completed the first steps of implementing ABC.
Amy identified seven activities that will be used to analyze the operating costs.
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,100PreOrder 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 Cost Drive r Driver Quantity
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.00
Delivery Pounds shipped 3,552,500
Installation # of installer hours 6,000
Post-Sale Customer Support # of support hours 2,400

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Accounting A Comprehensive Guide For Beginners

Authors: Robert McCarthy

1st Edition

1638180474, 978-1638180470

More Books

Students also viewed these Accounting questions

Question

Explain IAAS

Answered: 1 week ago