This assignment is based on the Jamestown case which is available on D2L (ABC Jamestown Case). The purpose of this assignment is to give you an opportunity to learn about of some of the complexities involved in designing a real activity-based cost system and to start you thinking about how the resulting information will be used in managing a firm. We will not use the Part I requirements on p. 268. Instead, we will use the questions provided at the end of this assignment. We will focus on the three key customers that Jamestown is using as examples for its profitability analyses: Alanson, Boyne, and Conway. Exhibit 2 in the case provides estimates of profits from each of the three key customers. These estimates are not very credible to some managers at Jamestown. Use the information provided in Exhibits 4 and 5 and the accompanying text to create a new cost system that you can use to make better estimates of customer profits. More specific guidance is provided below for creating an alternative cost system to estimate customer profits. Guidance on designing a new cost system The controversies in the company appear to be focused on the costs of warehousing and shipping activities, so this is the part of the system that you need to redesign. A worksheet for cost system design is included in the Assignments folder. The row for "Special Handling" has been completed already. You will need to complete the other rows, choosing allocation bases (= cost drivers) based on information provided in the case. Here are some things to note: a) Typically we use budgeted MOH costs and budgeted activity levels to create MOH allocation rates. The 2007 activity costs from Exhibit 4 of the case are the budgeted" costs, and 2007 activity driver information are the "budgeted" quantity of the allocation bases. b) When taking numbers from the Exhibits in the case, be sure to pay attention to whether the numbers are given in $$, thousands of $$, physical units, etc. c) The total $$ volume of inventory stored at Jamestown is $16,630,890. The average dollar volume of inventory stored for a given customer can be estimated with this formula: COGS for customer * (customer's average number of days in inventory 365) d) Don't try to create separate allocation rates for automotive and non-automotive customers. The case provides no reason to believe that the costs of holding a given amount of inventory or processing an order of a given size are different for these two customer types. e) Assume that there is no difference in $$ size of order between orders that require special services and orders that do not. (Thus if 10% of a customer's orders require special services, then this represents 10% of its $$ volume of sales.) Excel requirements Always use formulas and cell addresses when you can, rather than typing in numbers. The output should be clearly labeled and easily readable. A reader should be able to understand quickly where all your numbers came from, by looking at your formulas and accompanying notes. For the analysis of profitability of each of the 3 customers, show 3 sections: (i) background data taken directly the case (e.g., number of orders, average days in inventory), (ii) intermediate calculations required (e.g., average value of inventory on hand, estimated sales volume of special orders), and (iii) a revised customer profitability statement in the same format as Exhibit 2 in the case. To submit: 1. Completed "Cost system" worksheet in the spreadsheet provided in Assignments folder. 2. Create a replacement for Exhibit 2, with new estimates of profit per customer. This should also be in Excel. When you calculate profits for the three key customers, you can use the same numbers for Sales, Cost of Goods Sold and General & Administrative Expenses that appear in Exhibit 2 of the case. 3. Brief answers to the following questions (Separate tab in your Excel file). a) In your new version of Exhibit 2, one customer will be much less profitable than it appeared to be in the original Exhibit 2, because the cost of serving this customer is high. According to your new cost system, what are two things that this customer does that generates such high costs for Jamestown? b) The time you have spent creating a new cost system is not worthwhile unless it enables Jamestown to make better management decisions that will improve its profits. Suggest two ways that Jamestown could improve its profits, which it could not do so easily without the information provided by your new cost system. Be sure to explain how the ABC costing system provides the information to support your suggested changes. Grading New cost system correct 1 points New customer profits correct 1 points Answers to questions 3a-b 2 points Use of formulas in Excel 1 points Check Figure: Conway Net Income = $58,120