The objective of this exercise is to attach costs for the Marketing, General, and Administrative Departments to specific activities performed by these departments. The data worksheets you will need for this problem are: Income Statement, Mktg, Gen & Admin Dept., DB 8.1 Purchasing Template, DB 8.1 Finance Template, DB 8.1 Human Rel’ns Template, DB 8.1 Info Systems Template, DB 8.1 Exec Admin Template, DB 8.1 R&D Template, DB 8.1 Sales & Mktg Template, and DB 8.1 Cust. Service Template. The instructions for eight of the templated worksheets are identical. These are summarized on the final template.

a. First, go to the worksheet titled DB 8.1 Purchasing Template and enter the budget for the department from the worksheet titled “Mktg, Gen & Admin Depts.” Make sure you multiply the budgeted amount by 1,000 to convert to actual dollars. You will then calculate the cost per activity as well as the amount of the activity cost assigned by the purchasing manager to Customer Value Add, Business Value Add Current, Business Value Add- Future, Business Value Add-Administrative, and Non-Value-Added Costs.

b. For the remaining seven departments, you simply need to enter the budget figure, in actual dollars, in cell D75. The rest of the sheet will automatically calculate. In other words, use all of the cost center information on the worksheet “Mktg, Gen & Admin Depts.”

c. You are now ready to develop the summary sheet. Go to the worksheet named “DB 8.1 Summary Sheet Template” and enter the data from their respective cells in Row 99 for each cost center. Summarize the worksheet. Then, for Columns C to H, first calculate the total dollars assigned to each category in Row 13, then calculate the percentage of the total cost for each of the value-based categories in Row 14.

REQUIRED:
Turn in the Summary Sheet Template to your instructor with the answer to the following question: Looking at the results of the analysis, what would you say about Kinkaid’s management? Be precise; use the results of your analysis to support your recommendations in a business case framework.