In this exercise, you are going to create a capacity report for the Cutting Department at Kinkaid following the steps below.

a. Using the worksheet “DB 7-1 Cutting Data Template” provided for you in the Excel database, DB 7.1, fill in the amounts for good units produced and reported line hours for 20x6.

b. Calculate the total number of productive, nonproductive, and idle hours.

c. Enter the amount of committed costs and managed costs, making sure to adjust the dollar amounts to actual dollars (they are currently stated in thousands).

d. Total your costs to get a check number for your capacity report.

e. Calculate the hourly rates for committed and managed capacity costs.

f. Move to the tab named “DB 7.1 Cutting Report Template.” Using the information in your data sheet, fill in this entire report. 

g. Calculate all of your percentage hours.

h. Enter your capacity hourly rates. Multiply each of the total hours cells by their corresponding rate. Where applicable, fill in noted subtotals.

i. Calculate percentages of dollars for each category to fill Column H.

j. Calculate cost per part for each category in the capacity report.

k. Transfer these final totals to the worksheet “DB 7.1 Est. Part Costs Template.”

l. For the remaining departments, you only need to do the calculations on the data templates. The capacity reports will automatically generate.

m. Once you have transferred all of the part cost data from your capacity reports, you will calculate the percentage each type of cost (e.g., competitive, idle) of the total cost per part by department on the “DB 7.1 Est. % Part Cost Templt” worksheet.

REQUIRED:

Turn in the worksheets DB 7.1 Est. Part Cost Template and DB 7.1 Est % Part Cost Templt to your instructor along with your answers to the following questions: Which department seems to have the highest level of productive cost? Nonproductive cost? Idle cost? Which summary data sheet was more informative? Why?