I’m working on a accounting multi-part question and need an explanation and answer to help me learn.
Note:I have done the work. But I can’t attract the Tableau file here for some reason, I have got two wrong Q 5 and 7.
Johansson Inc. is a manufacturing company that uses a predetermined overhead rate based on direct labor hours to apply overhead to individual jobs. For the current year, estimated direct labor hours are 90,000 and estimated factory overhead is $580,000. The following information is for the prior year.
Project 2 Steps:
Open a new Tableau Workbook from the File menu. Select the “Connect to Data” option in the Data tab on your blank worksheet page, or you can select Data Source in the bottom left hand corner. Next, select Microsoft Excel in the list of connection sources. You will then select the Excel data file you just downloaded in your file explorer.
Sheet 1: Within the Sheet1 tab, either click the dropdown arrow that appears on the Month attribute or right click the Month attribute. Hover over the Change Data Type Option and select Date in the list of options that appears.
Drag and drop month onto the Columns shelf. Either click the dropdown arrow or right click the Month attribute that is now on the Columns shelf. Select the first Month option that appears in the list of options.
Drag and drop Materials Inventory onto the Rows Shelf.
Drag and drop WIP Inventory onto the Y-axis.
Drag and drop FG Inventory onto the Y-axis.
Right-click the Sheet1 tab, choose Rename, type Part 1, and tap the Enter key. This completes Sheet 1.
Sheet 2: In a new blank worksheet, either click the dropdown arrow that appears on the Month attribute or right click the Month attribute. Hover over the Change Data Type Option and select Data in the list of options that appears.
Drag and drop month onto the Columns shelf. Either click the dropdown arrow or right click the Month attribute that is now on the Columns shelf. Select the first Month option that appears in the list of options.
Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable “Applied Overhead”. Insert the following equation into the blank calculation space, divide the estimated factory overhead by the number of estimated direct labor hours and then multiply by the Direct Labor Hours variable. It will look something like this, “(580000 / 90000) x [Direct Labor Hours]”. (NOTE: Commas cannot be used in Tableau formulas)
Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the dropdown menu. Title the new variable “Actual Overhead”. In the blank calculation space sum up all overhead variables. This will include Indirect Labor, Supervisory Salaries, Factory, Factory Depreciation, and Indirect Materials Used.
Drag and drop Actual Overhead onto the Rows Shelf. In the Marks box, under the dropdown menu of options, select Bar.
Drag and drop Applied Overhead onto the Detail mark.
Right click on the Y-axis and select “Add Reference Line”.
In the Scope section, select Per Cell. In the Line section, click the first dropdown menu on Value. Select SUM(Applied Overhead). Click OK.
Create a calculated field by either right clicking anywhere in the blank space of the Data pane and selecting Create Calculated Field or click the Analysis tab and select Create Calculated Field from the drop down menu. Title the new variable “Over-Under Applied”. In the blank calculation space create a variable that holds the string “Over Applied” if Applied Overhead for a particular month is more than the Actual Overhead or “Under Applied” if Applied Overhead for a particular month is less than the Actual Overhead. Do this using the following syntax: “IF [Applied Overhead] – [Actual Overhead] > 0 THEN “Over Applied” ELSE “Under Applied” END”. (NOTE: be certain to leave a space between “0” and “THEN” within the calculation)
Drag and drop Over-Under Applied onto the Color mark.
Right-click the Sheet2 tab, choose Rename, type Part 2, and tap the Enter key. This completes Sheet 2.
