Job Costing Spreadsheet Assignment

Job Costing Spreadsheet Assignment

Learning Objectives

1.  Understand the data available in job costing and revenue subsystem of an accounting system.

2.  Be able to summarize large amounts of data using a pivot table.

3.  Be able to use the vlookup function to match data with a common key value from different sources (i.e. the job costing and revenue systems)

4.  Be able to interpret cost and profitability data.

Job Cost Pivot.xls contains cost and revenues related to the operations of Custom Cabinets, a manufacturer of cabinets for the home building industry. Custom Cabinets manufactures two different types of cabinets. The first type of cabinet, standard, is made according to predetermined specifications. With standard cabinets, the home builder must design their kitchens around the cabinet designs available. The second type of cabinets, custom, is made to the exact specification of the home builder. With custom cabinets, the home builder can design the kitchen exactly the way the home owner wants it, without the constraints of the preexisting cabinet designs that are available. Custom Cabinets receives orders from home builders and then manufactures the cabinets requested in a job shop environment.

Your goal in this exercise is to determine the average materials, labor, overhead and profit earned, in dollars, on standard versus custom jobs. In order to do this, you have requested that data be extracted from the accounting system of Custom Cabinets. This data can be found in Job Cost Pivot.xls. The first worksheet, Costs, contains the date, job number, type of cost (materials, labor and overhead) and cost amount from the job costing subsystem of the accounting system. The second worksheet contains the job number, job type and total revenue, which was downloaded from the revenue subsystem of the accounting system. Note that, in addition to standard and custom jobs, Custom Cabinets also sells some direct materials to builders that are not processed into cabinets; for example the builder might want to purchase some wood that matches the cabinets to use in surrounding construction. These sales are listed as Retail sales in the revenue spreadsheet and are not being used in this exercise (their cost data would be found in the purchasing subsystem). In order to determine profitability for the jobs, you will need to use the following general steps:

1.  Create a pivot table to summarize the total costs for each job. Make sure your pivot table shows totals for labor, materials overhead and total cost.

2.  Create a separate spreadsheet with columns for job number, job type, total labor, total materials, total overhead, total cost, total revenue and total profit. Each custom and standard job should have a row in this spreadsheet. The Job number and cost columns for this spreadsheet can be copied from the job cost pivot table (use copy and paste, DO NOT try to use a cell reference/formula). A Vlookup function should be used to find the job type and total revenue from the revenue worksheet that correspond to each job. You can then create a formula to compute the profit for each job.

3.  Finally, create a pivot table to determine the average revenue, labor, materials, overhead and profit for each type of job – standard and custom.

4.  Write a short memo to your boss, Darrel Swenson the owner of Custom Cabinets, describing the pivot table results and explaining their implications for decisions made about the two product lines by Custom Cabinets. Include relevant numbers from the pivot table in support of your recommendations.

Formatting: All dollar values in your spreadsheets should be formatted to include commas and should show no cents. All other formatting should be professional.

Building a Pivot Table
(these instructions assume you are using Office 2007)

The goal of a Pivot Table is to summarize a large amount of data – for example, product costs – based on categories of interest – for example, by job number, type of cost, date incurred.

Begin with a data spreadsheet showing the data of interest and category information. For example, if you are interested in costs (the data of interest) one column should contain the cost information, and the other columns should categorize the costs, for example, by job number, cost type, date incurred as shown in this example.

To make a Pivot Table, place your cursor anywhere within the data spreadsheet (as shown above). Click the Insert Tab and Click the Pivot Table button. Under “Choose the Data You Want to Analyze” you should automatically see a reference to the range where the data is located on the data spreadsheet. Under “Choose Where You want the Pivot Table Data to be Placed” you can decide whether you want the data on a new spreadsheet or you can specify an existing location. Click OK, and you should see a grid like the following.

If your grid does not look like the one above, make sure that the pivot table is still the currently selected object. Click the Pivot Table Tools, Options tab, click Options, the Display tab, and Check the “Classic Pivot Table Layout” box. Press OK.

In order to build a pivot table and conduct your data analysis, the following dimensions of data should be specified.

·  The categories that will be rows headings of pivot table.

·  The categories that will be the column headings in the pivot table.

·  The data of interest that will be summarized in the body of the table for each column and row.

To specify these values, simply take the column titles shown under “Choose Fields to Add to Report” and drag them to the appropriate area on the Pivot Table grid. Start with the Rows (Job Number), then the Columns (Cost Type). (It is not necessary to have both row and column categories if you want to summarize on only one factor). Finally, place the data field (Cost) to be summarized in the “Drop Data Items Here” location. Excel will automatically sum up the data of interest that corresponds to the row and column categories. So if your rows are Job Numbers, and your columns are Cost Types, and your data of interest is Cost, Excel will sum the total cost for each job/cost type combination. You can summarize multiple data elements if you wish. For example, if your spreadsheet contained both cost and revenue, you could summarize both costs and revenues by dragging both fields into the data area.

By default, the body of the Pivot Table shows the sum of all of the data values for each row category and column category combination. However, you can also have Excel summarize the data in other ways, for example, by computing average. To change the way that the data is summarize in the body of the Pivot Table, RIGHT click in the data area to display the menu shown, click Summarize Data By, and choose a different option – count, average, max, min, etc.

Using a VLookup

Vlookup is a function that helps you find data in a list. For example, if you have a list showing manufacturing jobs and their job types and total revenue like Table 1, Vlookup will help you find and display the job type and total revenue to complete Table 2, as shown below.

Table 2

VLookup takes on the following form: VLOOKUP(LookupValue, Table Array, Col Index No)

Assume you are trying to lookup Job Type from Table 1 to display in Cell B2 of Table 2. In Cell B2, you would type “=Vlookup(“ . Next, you would add the Lookup Value and Table Array to the function by pointing to the relevant cells with your mouse as described below.

LookupValue is the value to search for. In the example, the LookUp value is the Job Number found in Cell A2 (480). Therefore, the first value in the Vlookup is the cell reference A2, which can be entered by pointing to cell A2. Next you will enter a comma, and proceed on to enter the Table Array.

Table Array is the table of data to search in. In the example, Table 1 is the Table Array, so, you should enter Revenue!$A$2:$C$142 to reference the data table. Do this by clicking and dragging to select the cells on the Revenue sheet, and then pressing the F4 key to add the dollar signs. In the resulting reference, Revenue! denotes the spreadsheet where the data is located, and $A$2:$C$142 is the cells containing the data. Note, since you will want to copy the formula you are creating in cell B2 down to all of the cells in Column B (so that you can look up the Job Types for all of the jobs) you should use the $ signs in front of the row and column numbers. This is called an absolute cell reference. When an absolute cell reference is used in a formula and the formula is copied to another cell, the cells being referenced will not change – i.e. if you copy the formula in Cell B2 to any other place, the Table Array will always be Revenue!$A$2:$C$142. If you did not use the $ signs to make an absolute cell reference, then the cell reference will adjust as the formula is copied – for example, your reference to the lookup value in cell A2 will change to A3 when you copy the formula in cell B2 down one row to cell B3.

Col Index No is column number from the Table Array that you want to display data from. In the example, you want to display the Job Type, which is in column 2.

So, your Vlookup function for Cell B2 should be =VLOOKUP(A2, Revenue!$A$2:$C$142,2). You would use a similar function to lookup the Total Revenue amount for Cell C2. Then these two formulas can be copied down to all of the cells in columns B and C.