Borne Company manufactures two models of industrial componentsa Basic model (20,000 units) and an Advanced Model (5,000 units). The company considers all of its manufacturing overhead costs ($1,618,900) to be fixed and it uses plantwide manufacturing overhead cost allocation based on direct labor-hours. It is considering implementing an activity-based costing (ABC) system that allocates all of it manufacturing overhead to four cost poolsAssemble and Pack (allocated based on direct labor-hours), Machining (allocated based on machine-hours), Order Processing (allocated based on the number of orders processed), and Setups (allocated based on setup hours). An ABC implementation team has completed the activity-based cost allocations and is seeking your help in creating some Tableau visualizations that contrast these cost allocations with the companys plantwide approach. Download the Excel file, which you will use to create the Tableau visualizations requested by the ABC implementation team.

Upload the Excel file into Tableau by doing the following:

Open the Tableau Desktop application.

On the left-hand side, under the Connect header and the To a file sub-header, click on Microsoft Excel.

Choose the Excel file and click Open.

Since the only worksheet in the Excel File is Borne Company it will default as a selection with no further import steps needed

Create a 100% stacked bar chart that shows the percent of each activitys total cost that is allocated to each product using the activity-based approach:

Double click on Sheet 1 at the bottom of your workbook and rename it Activity Cost Summary

On the left-hand side under Dimensions (sometimes labeled as Tables), click on Activity and drag it to the Columns area above the blank sheet.

Click on Measure Names under Dimensions and drag and drop it onto the Color Marks card ().Click on the drop down in the Measure Names pill under Marks and choose Filter

Make sure that only Advanced % and Basic % have a check in their box (uncheck the rest of the boxes)

Click OK

On the left-hand side under Measures, click on Measure Values and drag it to the Rows area.

In the resulting stacked bar chart, click on the x-axis label of Null and choose Exclude

To improve viewing, locate the Standard dropdown option in the menu bar at the top of the screen. Click on that dropdown and choose Entire View.

To insert numerical values at the top of each bar in your chart, click the Text button () in the menu bar at the top of the screen.To format the labels, click on the Sum(Advanced %) pill under Measure Values and choose Format

Under the Pane tab click on the Numbers: drop down and choose Percentage

To format the labels, click on the Sum(Basic %) pill under Measure Values and choose Format

Under the Pane tab click on the Numbers: drop down and choose Percentage

Your visualization should appear as follows:

Required: 1a. For the Assemble and Pack activity: What percentage of the cost is allocated to the Basic Model? What percentage of the cost is allocated to the Advanced Model? 1b. For the Machining activity: What percent of the cost is allocated to the Basic Model? What percent of the cost is allocated to the Advanced Model? 1c. For the Order Processing activity: What percent of the cost is allocated to the Basic Model? What percent of the cost is allocated to the Advanced Model? 1d. For the Setups activity: What percent of the cost is allocated to the Basic Model? What percent of the cost is allocated to the Advanced Model? (For all requirements, round your answers to 2 decimal places.)

1e. Which of the following statements is true? (You may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.)

check all that apply

The ABC system shows that the Advanced model consumes a higher percentage of the unit-level activity costsOrder Processing and Setupsthan the Basic model.unanswered

The ABC system shows that the Advanced model consumes a higher percentage of the batch-level activity costsOrder Processing and Setupsthan the Basic model.unanswered

The ABC system shows that the Advanced model consumes a lower percentage of the batch-level activity costsOrder Processing and Setupsthan the Basic model.unanswered

The ABC system shows that the Advanced model consumes a lower percentage of the unit-level activity costsOrder Processing and Setupsthan the Basic model.

Model	Activity	Basic Cost	Basic %	Advanced Cost	Advanced %	Allocation Method	Basic Total	Advanced Total
Basic	Assemble and pack	$ 231,396	66.67%
Basic	Machining	$ 277,200	51.22%
Basic	Order processing	$ 58,500	19.34%
Basic	Setups	$ 32,150	7.51%
Advanced	Assemble and pack			$ 115,704	33.33%
Advanced	Machining			$ 264,000	48.78%
Advanced	Order processing			$ 243,950	80.66%
Advanced	Setups			$ 396,000	92.49%
Basic						Plantwide Allocation	$ 1,079,250
Basic						Activity-Based Allocation	$ 599,246
Advanced						Plantwide Allocation		$ 539,650
Advanced						Activity-Based Allocation		$ 1,019,654