A Table 3 - Cost Drivers and Quantities Activity PreOrder Consultation Interior Design Site Visit Activities...

Transcribed Image Text:

A Table 3 - Cost Drivers and Quantities Activity PreOrder Consultation Interior Design Site Visit Activities Warehousing Activities Delivery Installation 1 2 Post-Sale Customer Support 3. 4 5. 6 7 8 9 0 1 2 3. 4 5 B C D E F Cost Driver # of sales calls # of design hours # of site visits Cost of Goods Sold Pounds shipped # of installer hours # of support hours Driver Quantity 315 7,960 180 $ 19,750,000.00 3,552,500 6,000 2,400 6 7 8 Table 1 Costs to Analyze Table 2 - Activity Analysis Table 3 - Cost Drivers Activity Rates Customer Analysis Ready Accessibility: Good to go Interiors designs and sells office furniture configurations. The company's customers are hospitals, K-12 schools, doctor offices, churches, municipalities and universities. interiors purchases the chairs and furniture from various wholesale furniture providers. provides the key services of designing the furniture layouts and installing the furniture. Examples of types of furniture Amy's Interiors designs and installs: Fabric Stackable Chair with Armrests -Blue Sturdy, durable chairs stack neatly when not in use. Classic look and comfort for breakrooms and reception areas 2 thick foam padded seat with 2" back. Steel frame. Powder-coat finish. Stocks up to 5 high for convenient storage. Move with Dolly or Hand Truck, sold separately. OFFICE SIT/STAND STOOL Perfect companion to an adjustable height desk. Work comfortably whether you sit or stand. Tilts, pivots and leans to relieve pressure points. Nonslip base. 1 foam seat with fabric upholstery, CONFERENCE ROOM CHAIR Sit in comfort during long meetings. High-back style in bonded leather with plush foam. 5' thick seat and back. also designs and installs computer workstations, computer labs, student desks and conference room desks. Example of types of tables and workstations Amy's can design and install: MOBILE TRAINING TABLES Downtown Training Tables Training Tables Baning Tables Traning Tabies Adjuble Height Calore images D CAF TABLES Bring people together for a meeting, lunch break or study session. 1% thick top with smooth, scratch-resistant laminate surface. Sturdy. 4-prong steel base. Leveling feet provent wobble. Easy to clean. 30 high. Stackable Chairs available. Office fates Mobile Pedestal F Cederg Conference labies Room Dividers Bookcases Source for case example products is Uline (photos and details) Interiors consults with potential clients first to determine the scale of the project. Based on the location sizes and needs, 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 Amy's 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. Amy's team of installers arrives on site the day after the delivery to unload, setup and install the customer's design. 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. Amy's 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 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. Below are the summaries of the work Amy has already done to start the ABC analysis. Table 1 Costs to Analyze Cost Clerical wages $ Salesperson Commissions and Wages $ 65,200 741,300 Interior Designer Wages $ 295,800 Warehouse Wages $ 322,100 Installers 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 TOTAL $ 162,300 $ 6,966,100 Table 2 Activity analysis Table 2 Activity Analysis Clerical wages PreOrder Consultations 50% Interior Design Site Visits Warehousing Post-Sale Activities Activities Delivery Installation Customer Service Total 15% 5% 10% 20% 100% Salesperson Commissions and Wages 40% 40% 20% 100% Interior Designer Wages 80% 20% 100% Warehouse Wages Installers Wages Advertising 80% 10% 10% 100% 95% 5% 100% 75% 10% 15% 100% O Promotional Materials 70% 15% 15% 100% Factory equipment depreciation. 50% 10% 40% 100% 2 Maintenance 60% 10% 30% 100% Shipping 100% 100% Utilities and Fuel 20% 60% 10% 10% 100% Table 3 Cost Drivers Table 3-Cost Drivers and Quantities Activity PreOrder Consultation Interior Design Site Visit Activities Warehousing Activities Delivery Installation Post-Sale Customer Support Cost Driver Driver Quantity # of sales calls # of design hours # of site visits 315 7,960 180 Cost of Goods Sold $ 19,750,000.00 Pounds shipped 3,552,500 # of installer hours 6,000 # of support hours 2,400 Amy has provided Table 2 in the attached Excel spreadsheet to help you get started with the analysis. Amy has identified four customers to analyze based on the results of the ABC allocations. Table 4 Sample Customers Sample Customers Des Moines Public Works Bright Space Dental Braxton CC Keystone High School Sales $ 68,000 $ Cost of goods sold 52.120 125,000 $ 95,600 Gross margin Driver Base # of sales calls # of design hours $ 15,880 $ 29,400 $ 52,000 $ 41,900 10,100 $ 350,000 264,800 85,200. Driver Quantity Driver Quantity Driver Quantity Driver Quantity 5 20 8 22 # of site visits Pounds shipped # of installer hours # of support hours 10 1 3,500 11,700 8,200 8 25 2 49,800 45 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. Requirement 1: Excel analysis with tabs "Activity Rates" and "Customer Analysis" prepared by student. Submit with the file name "Project 3_Lastname.xls" or "Project 3_Lastname.xlxs". Requirement 2: Write a memo to Amy with the results of the Customer Analysis. Use data visualizations to present the results of the customer analysis to Amy in the memo. Your data visualization should not be the copy of the Excel work, instead it should be a chart or graph that tells the story. What business strategies should Amy consider to improve future profitability based on the current business, activity rates and customer analysis? You should provide two specific strategy recommendations. Your memo should be double spaced. Your memo should be 2-3 pages including the visualizations. Submit your memo as a Microsoft Word or PDF file with name "P3 Memo_Lastname.docx" or "P3 Memo_Lastname.pdf". You are submitting two files to this Western Online Assignment (the Excel and the Memo). Course Objectives for Project 3: CO 1: Summarize contemporary management techniques and apply to business situations. CO 2: Explain implementation of competitive strategies. CO 5: Cost products and services in an activity based costing system. CO 11: Communicate business decisions to users. Requirement 3 is your presentation and peer responses. Details are found in the Project 3 Discussion area of Western Online. Project 3 Grading Form for Excel and Memo: Requirement 1: Excel Points Possible Your Score Activity rates tab - all formulas with cell references 5 Activity rates tab - labeling and instructions for another user to understand in future 5 Activity rates tab - accuracy 5 Customer analysis tab - formulas 5 Customer analysis tab - labeling & instructions 5 Customer analysis tab accuracy 5 Requirement 2: Memo Professionalism and grammar Data visualization Explained customer analysis Business strategies 5 5 5 5 Total Project 3 for Requirements 1 & 2 50 0 A Table 3 - Cost Drivers and Quantities Activity PreOrder Consultation Interior Design Site Visit Activities Warehousing Activities Delivery Installation 1 2 Post-Sale Customer Support 3. 4 5. 6 7 8 9 0 1 2 3. 4 5 B C D E F Cost Driver # of sales calls # of design hours # of site visits Cost of Goods Sold Pounds shipped # of installer hours # of support hours Driver Quantity 315 7,960 180 $ 19,750,000.00 3,552,500 6,000 2,400 6 7 8 Table 1 Costs to Analyze Table 2 - Activity Analysis Table 3 - Cost Drivers Activity Rates Customer Analysis Ready Accessibility: Good to go Interiors designs and sells office furniture configurations. The company's customers are hospitals, K-12 schools, doctor offices, churches, municipalities and universities. interiors purchases the chairs and furniture from various wholesale furniture providers. provides the key services of designing the furniture layouts and installing the furniture. Examples of types of furniture Amy's Interiors designs and installs: Fabric Stackable Chair with Armrests -Blue Sturdy, durable chairs stack neatly when not in use. Classic look and comfort for breakrooms and reception areas 2 thick foam padded seat with 2" back. Steel frame. Powder-coat finish. Stocks up to 5 high for convenient storage. Move with Dolly or Hand Truck, sold separately. OFFICE SIT/STAND STOOL Perfect companion to an adjustable height desk. Work comfortably whether you sit or stand. Tilts, pivots and leans to relieve pressure points. Nonslip base. 1 foam seat with fabric upholstery, CONFERENCE ROOM CHAIR Sit in comfort during long meetings. High-back style in bonded leather with plush foam. 5' thick seat and back. also designs and installs computer workstations, computer labs, student desks and conference room desks. Example of types of tables and workstations Amy's can design and install: MOBILE TRAINING TABLES Downtown Training Tables Training Tables Baning Tables Traning Tabies Adjuble Height Calore images D CAF TABLES Bring people together for a meeting, lunch break or study session. 1% thick top with smooth, scratch-resistant laminate surface. Sturdy. 4-prong steel base. Leveling feet provent wobble. Easy to clean. 30 high. Stackable Chairs available. Office fates Mobile Pedestal F Cederg Conference labies Room Dividers Bookcases Source for case example products is Uline (photos and details) Interiors consults with potential clients first to determine the scale of the project. Based on the location sizes and needs, 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 Amy's 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. Amy's team of installers arrives on site the day after the delivery to unload, setup and install the customer's design. 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. Amy's 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 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. Below are the summaries of the work Amy has already done to start the ABC analysis. Table 1 Costs to Analyze Cost Clerical wages $ Salesperson Commissions and Wages $ 65,200 741,300 Interior Designer Wages $ 295,800 Warehouse Wages $ 322,100 Installers 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 TOTAL $ 162,300 $ 6,966,100 Table 2 Activity analysis Table 2 Activity Analysis Clerical wages PreOrder Consultations 50% Interior Design Site Visits Warehousing Post-Sale Activities Activities Delivery Installation Customer Service Total 15% 5% 10% 20% 100% Salesperson Commissions and Wages 40% 40% 20% 100% Interior Designer Wages 80% 20% 100% Warehouse Wages Installers Wages Advertising 80% 10% 10% 100% 95% 5% 100% 75% 10% 15% 100% O Promotional Materials 70% 15% 15% 100% Factory equipment depreciation. 50% 10% 40% 100% 2 Maintenance 60% 10% 30% 100% Shipping 100% 100% Utilities and Fuel 20% 60% 10% 10% 100% Table 3 Cost Drivers Table 3-Cost Drivers and Quantities Activity PreOrder Consultation Interior Design Site Visit Activities Warehousing Activities Delivery Installation Post-Sale Customer Support Cost Driver Driver Quantity # of sales calls # of design hours # of site visits 315 7,960 180 Cost of Goods Sold $ 19,750,000.00 Pounds shipped 3,552,500 # of installer hours 6,000 # of support hours 2,400 Amy has provided Table 2 in the attached Excel spreadsheet to help you get started with the analysis. Amy has identified four customers to analyze based on the results of the ABC allocations. Table 4 Sample Customers Sample Customers Des Moines Public Works Bright Space Dental Braxton CC Keystone High School Sales $ 68,000 $ Cost of goods sold 52.120 125,000 $ 95,600 Gross margin Driver Base # of sales calls # of design hours $ 15,880 $ 29,400 $ 52,000 $ 41,900 10,100 $ 350,000 264,800 85,200. Driver Quantity Driver Quantity Driver Quantity Driver Quantity 5 20 8 22 # of site visits Pounds shipped # of installer hours # of support hours 10 1 3,500 11,700 8,200 8 25 2 49,800 45 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. Requirement 1: Excel analysis with tabs "Activity Rates" and "Customer Analysis" prepared by student. Submit with the file name "Project 3_Lastname.xls" or "Project 3_Lastname.xlxs". Requirement 2: Write a memo to Amy with the results of the Customer Analysis. Use data visualizations to present the results of the customer analysis to Amy in the memo. Your data visualization should not be the copy of the Excel work, instead it should be a chart or graph that tells the story. What business strategies should Amy consider to improve future profitability based on the current business, activity rates and customer analysis? You should provide two specific strategy recommendations. Your memo should be double spaced. Your memo should be 2-3 pages including the visualizations. Submit your memo as a Microsoft Word or PDF file with name "P3 Memo_Lastname.docx" or "P3 Memo_Lastname.pdf". You are submitting two files to this Western Online Assignment (the Excel and the Memo). Course Objectives for Project 3: CO 1: Summarize contemporary management techniques and apply to business situations. CO 2: Explain implementation of competitive strategies. CO 5: Cost products and services in an activity based costing system. CO 11: Communicate business decisions to users. Requirement 3 is your presentation and peer responses. Details are found in the Project 3 Discussion area of Western Online. Project 3 Grading Form for Excel and Memo: Requirement 1: Excel Points Possible Your Score Activity rates tab - all formulas with cell references 5 Activity rates tab - labeling and instructions for another user to understand in future 5 Activity rates tab - accuracy 5 Customer analysis tab - formulas 5 Customer analysis tab - labeling & instructions 5 Customer analysis tab accuracy 5 Requirement 2: Memo Professionalism and grammar Data visualization Explained customer analysis Business strategies 5 5 5 5 Total Project 3 for Requirements 1 & 2 50 0