ISYS2056 Lecture Notes - Lecture 10: Microsoft Access, Data Redundancy, Meridian Regional Airport
Document Summary
Get access
Related Documents
Related Questions
Please answer only "Discussion Question" at theend.
Western States Stationery Case
Western States Stationery was founded in the late 1800s in theSan Francisco area as a one-man operation producing notepaper andcards for sale in the cityâs general stores. By 2004 it had grownto a large paper products corporation with sales more than $2billion.
One division of the company produces specialty paper products,such as writing paper, envelopes, note cards and greeting cards.Another division manufactures and prints business forms. By 2002 itwas one of the top 5 firms in the business forms industry.
For some period, the companyâs sales growth had been relativelyflat and profit margins had been difficult to maintain. Competitionamong suppliers was much more intense than in prior years. In 2002management decided it would venture into business forms inventorymanagement services. This was an area that management thoughtoffered several advantages. These services were thought to beâvalue addedâ allowing the firm to differentiate itself fromothers. Differentiation implied higher margins and the potential tofurther bind customers to Western, reducing the threat they mightswitch to an alternative supplier. Finally, the concept wasrelatively new, with little competition from large firms, therebyoffering high sales growth potential. Western embarked on acampaign to enroll its corporate clients in a program it calledTotal Forms Control (TFC).
By 2004, sales from TFC were about $60 million. Western hadestablished TFC as a separate operation within the business formsdivision. The services provided under TFC included warehousing anddistribution of forms (including inventory financing) as well asinventory control and forms usage reporting. TFC used asophisticated computer system network, which enabled them tomonitor a clientâs forms inventory, forms usage and orderingactivity.
As part of its distribution services, TFC also offeredâpick-packâ services where workers opened full cartons to pick theexact number of forms requested by the clients. TFCâs operatingphilosophy was that a well-run warehousing and distribution networkwas vital to any forms management program ---âwe know what you need⦠the right product at the right place at theright time.â
For a small number of clients TFC also offered "desk topdelivery," where TFC personnel would distribute the forms toindividual offices within the customerâs company (otherwise formswere usually delivered only to a receiving dock area). As acomprehensive forms management provider, Western's product linealso had to be comprehensive. Their product line includedeverything from stock computer printout paper and fax paper tocustom designed forms tailored by Western's design consultants tomeet the exact business needs of the client.
TFC also had the ability to custom design its forms managementservices to meet the needs of each of its clients. TFC clientsranged from small businesses, which desired only basic inventorycontrol, to those who had comprehensive forms managementprograms.
CURRENT COST ACCOUNTING SYSTEM
Western operated its forms manufacturing and TFC activities asseparate profit centers. Western manufactured business forms in 13locations. The transfer of product from manufacturing operations toTFC was at arm's length with the transfer price set at fair marketvalue. Although the company encouraged internal sourcing forcustomer orders, TFC salespeople had the option of outsourcingproduct.
Clients who participated in the forms management program kept aninventory of forms at one of Western's 10 distribution centers. Theforms were distributed to the client as they were needed. Theclient was charged a service fee to cover the cost of warehousingand distribution based on a percentage of the cost of sales of theproduct for that month, regardless of the specific level of serviceprovided to that client. The standard charges were as follows:
⢠Warehousing/Distribution, 20.5% ofproduct cost
⢠Inventory financing, 4.7% of productcost
⢠Trucking to the customer, 7% ofproduct cost
If a TFC client made use of any of the distribution services,they were typically charged a price for the forms which was highenough to allow for an additional 20.5% of product cost to coverwarehousing and distribution expenses (everything from storage andrequisition handling up to and including desk top delivery), plus4.7% to cover the cost of capital tied up in inventory, and 7% tocover trucking costs. These percentages were determined based onactual 2002 financial data so that on an aggregate basis, in total,all expenses were covered (see Exhibit 1). Sales prices weregenerally determined by applying a 20% mark-up on the combined costof the product (business forms) and the 32.2% charge for TFCservices. To some degree, prices charged on customer accounts couldvary from the standard formula, based on what the sales departmentthought customers would pay.
UNDERSTANDING CUSTOMER PROFITABILITY
With TFC profitability suffering in October 2002, GeneralManager John Malone began to question the appropriateness of thedistribution charges: "The Business Forms Division used to earn a20% Return on Investment (ROI). But returns have been dropping forseveral years. TFC is projected to earn an ROI of only 6% for 2004.Something tells me that we are not managing this business verywell! It seems to me that the charges for services needs closerscrutiny."
John looked through his records and found two accounts ofsimilar size, accounts A and B, which were handled by differentsalespeople. Accounts A and B had essentially the same annual
sales of about $79,000 with the cost of the product being$50,000. Under the current system, these accounts carried the sameservice charges, but John noticed that these accounts were similaronly in the value of the product being sold; they were verydifferent on the level of service they required from Western.Malone double-checked his records and confirmed that the twoaccounts had indeed generated identical sales revenues (see Exhibit2).
In the past year, customer A had submitted 364 requisitions forproduct with a total of 910 lines (all of them "pick-pack") whilecustomer B had submitted 790 requisitions with a total of 2500lines (all of them "pick-pack"). Customer A kept an average of 350cartons of inventory at the distribution center while customer Bkept 700 on average. Customer B's average monthly inventory balancewas $50,000 ($7,000 of which had been sitting around for a wholeyear) while that of customer A was only $15,000. In addition,customer B had requested desk top delivery 26 times during the pastyear, while customer A did not request desk top delivery at all.John had support staff research the truck operating costsassociated with these customers. Mileage to and from each customerwas estimated and multiplied by an estimated cost per mile. Becauseof the greater activity on customer B's account, a shipment wentout three times a week at an annual trucking cost of $7,500 whileCustomer A required only one shipment a week at an annual truckingcost of $2,250.
With corporate breathing down his neck, John Malone turned toTFC Controller Melissa Dunhill and Director of Operations TimCunningham for help. As a first step, they could provide John withthe following information:
The total expenses for the distribution centers in 2002 wereestimated as follows (in 000's):
Rent | $1,504 |
Depreciation | $208 |
Utilities | $200 |
Salaried payroll | $945 |
Fringe benefitsâsalaried | $164 |
Taxes/Insurance | $110 |
Postage & supplies | $96 |
Hourly payroll â admin | $476 |
Fringes â hourly admin | $69 |
Variable warehouse payroll | $1,599 |
Warehouse fringes | $336 |
Total | $5,707 |
John said, "How am I going to use this information to solve myproblem?" "Well," Tim said, "if we can figure out, without goingoverboard of course, what exactly goes on in the distributioncenters, maybe we can take these financial numbers and assign themto the activities. If we can do that, we'll have a much better ideaof what it costs to serve our various clients." Tim knew that twoprimary activities took place in the distribution centers â thewarehousing of forms and the distribution of those forms inresponse to a customer requisition. He decided to talk to somepeople in the field to get more specific information.
DISTRIBUTION CENTER: ACTIVITY ANALYSIS
John and Tim visited Western's Kansas City, MO distributionfacility. Site manager Wilbur Smith confirmed, "All we do is storethe cartons and process the requisitions. I'll tell you, the amountof warehouse space we need just depends on the number of cartons.If we got into some flexible lease programs and changed aisleconfigurations, we could probably adjust our space requirements ifthe number of cartons we stored was to change. The other thing thatbothers me is that we've got some inventory that's been sittinghere forever.
The team then interviewed warehouse supervisor, Rick Fosmire, "Idon't care if I get a hundred requisitions with one line each orone requisition with a hundred lines on it, my guys still have togo pick a hundred items off the shelves. And those darned"pick-pack" requests. Almost everything is "pick-pack" nowadays. Noone seems to order a whole carton of 500 items anymore. Do you knowhow much more labor it requires to pick through those cartons? Andon top of that, this desk top delivery is a time drain for my guys.It's not like my guys don't have enough to do."
John and Tim returned to Denver with a better idea of whathappens in a distribution center. From what they observed, theybroke the distribution center down into 5 primary value-addedactivities: storage, requisition handling, basic warehouse stockselection, "pick-pack" activity, and desk top delivery.
They turned to Melissa for help assigning costs to activities.Melissa spent considerable time talking to each of the sitemanagers and warehouse supervisors. A consistent picture emerged.The majority (85%) of facility costs related to storage of businessforms. The remainder (15%) was necessary to handle theadministration of requisition processing. Warehouse workers wereinterviewed to determine where they spent their time. The basicprocess of finding and selecting items required half of their time(50%) - this includes finding and selecting either full box ordersor âpick-packâ orders. If the order also required âpick-packâ thisnearly doubled the required time as the workers opened cartons,counted items, etc. Thus, workers estimated that they spent 40% oftheir time dealing with âpick-packâ over and above basic formsselection. Preparing and handling desk top delivery shipments tookup the rest of their time (10%). Based on the above information,Melissa assigned costs to these activities as follows ($'000's).(See Exhibit 3 for calculations):
Storage | $1,625 |
Requisition Handling | $2,147 |
Basic Warehouse Stock | |
Selection | $967 |
Pick-Pack Activity | $774 |
Desk Top Delivery | $194 |
Total | $5,707 |
Tim then estimated the following for 2004 based upon historicalinformation and current trends:
On average, the 10 distribution centers scattered across thecountry will have combined inventories of approximately 350,000cartons (most cartons were of standard size).
TFC will process about 310,000 requisitions for 2004.
Requisitions will average 2.5 lines of different stationerymaterial requested and pulled.
About 90% of the lines will require an additional "pick-pack"activity (as opposed to shipping an entire carton).
They were still uncomfortable with the way inventory financingand trucking costs were charged out. John checked with the financedepartment and learned that Western obtained financing at the primerate (currently 8%) plus 2%. He thought they could just pass thatalong. "Our new computer system is coming on line soon which willtrack mileage we log to deliver shipments to each customer," saidTim, "so, using a per mile cost we can just charge the client forwhat it actually costs us." They all agreed that this soundedfair.
They were almost finished. "What about desk top delivery?" Timsaid. I think we should charge extra for it, but I don't want thisto get too complicated."
John said, "How much extra time does it take your guys onaverage to prepare a desk top delivery and then run around thecustomerâs company?"
"I'd say about an hour and a half to two hours."
"Alright. At $12 per hour, that's about $24 each time.
"Sounds OK to me. That ties pretty well to the $194,000 overallassignment, since we will process somewhere around 8,500 'desk top'requests this year."
The above information was used to derive activity / cost drivers(See Exhibit 4).
The entire management team, including Doug Kingsley, FinanceDirector of the Business Forms Division, felt that there had to bea better way of charging out distribution services and that thesolution would help TFC become more profitable. They now had a muchbetter understanding of the drivers of costs involved indistribution services. As the four headed off in Doug's SUV for theBroncosâ first home game, they tried to figure out how to use thisinformation to find a workable alternative.
SERVICES BASED PRICING (SBP)
"It would not be easy getting the sales force on board with anactivityâbased pricing program," John said. "Some of them getpretty stuck in their ways and don't like change. Some accountswould see increases because of the additional distribution chargesunder a Services Based Pricing (SBP) scheme. These salespeoplewouldn't be very happy. On the other hand, some salespeople may seetheir margins increase." Overcoming these organizational problemswould be only the tip of the iceberg.
Kingsley, as well as many of the senior managers at corporate,continued to be very concerned with TFC profitability. Whileeveryone thought TFC now had a better understanding of its costdrivers, they were not convinced that overall profitability wouldimprove without significant changes in marketing. They were stillwondering how to use their new activity based costing (ABC)analysis to improve the profitability of TFC. So they decided to doadditional analysis on their customer base.
The accounting department had maintained a database, whichshowed all activity against individual accounts and calculated acontribution from that account. Although TFC maintained 1100separate accounts, a large portion of the business came from veryfew accounts. The top 40 accounts represented 48% of the company'snet sales volume (see Exhibit 5).
As a way of understanding customer profitability, TFC managementreworked the information in the database as if the accounts hadbeen charged service fees based on actual usage, leaving net salesand product cost the same as before. They recalculated contributionbased on these figures. They ranked the accounts according toprofit contribution. Exhibit 6 shows the top 20 accounts for themonth of August and Exhibit 7 shows the bottom 20.
The team looked at all accounts where the revised contributionwas below 20% and determined that if these accounts were managed toa 20% contribution, the profit improvement would be $4.3 millionannually. Of those, the top 40 accounts (ranked by the contributionopportunity if improved to a 20% contribution) represented 70% ofthe $4.3 million opportunity.
Another way of summarizing the range of profitability across the1,100 customers is shown in Exhibit 8. This was a new way oflooking at account management which combined the effects of bothvolume and contribution margin. Since such a large piece of theopportunity rested with these few accounts, management determinedthat it might be possible to significantly improve profitability byconcentrating on individual account management. The team felt theywere on the right track for improving account profitability andwondered what should be the next step. They also wondered whatother issues might be important for improving the overallprofitability of TFC. Management called the ABC based pricingsystem SBP and was seriously considering adopting it for all TFCcustomers.
Case Questions to Consider
How did Total Forms Control (TFC) fit into Westernâs strategy?Was it performing up to expectations?
How did the existing accounting system handle the costs ofservices that TFC provided? Did this suit TFCâs needs?
Using the information in the case calculate ABC based servicescosts per unit of activity for the TFC business. Using these newactivity costs calculate distribution services and gross profit forcustomer A and customer B. What inferences do you draw about theprofitability of these two customers?
What does TFCâs analysis of individual customer accounts(exhibits 5-8) suggest about TFCâs overall customer profitability?What might managers do based on this information?
What are likely to be some of the issues in adopting ServiceBased Pricing (menu) pricing? Do you think that TFC shouldimplement the SBP pricing system?
Do you have any additional comments or recommendationsconcerning the Total Forms Control (TFC) business?
EXHIBIT 1 | |||
Calculation of Service Fee Charges | |||
(Current Method) | |||
('000's) | |||
2002 Product Sales at Cost | $24,059 | ||
2002 Warehousing/Distribution Expense | $4,932 | ||
...% of Product Cost | 20.5% | ||
2002 Average Inventory Balance | $10,873 | ||
2002 Average Cost of Capital | 10.40% | ||
Total Cost of Inventory Financing | $1,131 | ||
...% Product Cost | 4.70% | ||
2002 Total Truck Operating Costs | $1,684 | ||
...% Product Cost | 7.00% | ||
EXHIBIT 2 | |||
Actual Service Fee Charges | |||
(Current Method) | |||
Customer A | Customer B | ||
Product Cost | 50000 | 50000 | |
Warehousing/Distribution (20.5%) | 10250 | 10250 | |
Inventory Financing (4.7%) | 2350 | 2350 | |
Trucking (7%) | 3500 | 3500 | |
Total Service Fees | 16100 | 16100 | |
Markâup (20%) | 13220 | 13220 | |
Net Sales | 79320 | 79320 | |
EXHIBIT 3 | ||
Breakdown of Expenses by Activity | ||
('000's) | ||
Total Expenses | Share of Expenses | |
Storage | ||
Rent | $1,504x 85% | $1,278 |
Depreciation | $208 x 85% | $177 |
Utilities | $200 x 85% | $170 |
Total | $1,625 | |
Requisition Handling | ||
Rent | $1,504 x 15% | $226 |
Depreciation | $208 x 15% | $31 |
Utilities | $200 x 15% | $30 |
Salaries + Fringes | $1,109 | $1,109 |
Taxes/Insurance | $110 | $110 |
Postage & supplies | $96 | $96 |
Hourly Payroll + Fringes | $545 | $545 |
Total | $2,147 | |
Warehouse Activities | ||
Variable Warehouse Pay + Fringes | $1,935 | $1,935 |
Basic Warehouse Stock Selection (50%) | $967 | |
"Pick-Pack" Activity (40%) | $774 | |
Desk Top Delivery (10%) | $194 | |
Total | $1,935 | |
EXHIBIT 4 | ||
TFC Activities and Cost Drivers | ||
Activities / Cost Driver | Cost Driver in units | |
Storage / Cartons in inventory | (cartons) | 350,000 |
Requisition handling / Requisitions | (requisitioning) | 310,000 |
Basic Warehouse Stock Selection / Carton lines | (carton lines) | 775,000 |
"Pick-Pack" / âPick-Packâ lines | (Pick-Pack" lines) | 700,000 |
Desk Top delivery / Desk Top deliveries | (Desk Top deliveries) | 8500 |
Inventory financing / Inventory value | Not available or given elsewhere | |
Trucking / Mileage | Not available or given elsewhere | |
EXHIBIT 5 | ||
TFC Net Sales, 2003 | ||
Annual | No. of | % of TFC |
Sales/Account | Accounts | Net Sales |
>$300,000 | 40 | 48% |
>$150,000 | 53 | 19% |
>$75,000 | 86 | 15% |
>$30,000 | 143 | 11% |
>$O | 778 | 7% |
Total | 1100 | 100% |
EXHIBIT 6 | |||||
Top 20 TFC Accounts for August, 2004 | |||||
(Ranked by Contribution $) | |||||
Account | Actual Net Sales | Product Cost | ABC Based Service Costs | Revised Contribution | |
1 | 76,904 | 49,620 | 2,862 | 24,422 | |
2 | 130,582 | 74,396 | 34,578 | 21,608 | |
3 | 72,956 | 48,216 | 3,456 | 21,284 | |
4 | 64,903 | 37,981 | 6,574 | 20,348 | |
5 | 45,088 | 26,098 | 1,309 | 17,681 | |
6 | 104,689 | 62,340 | 25,356 | 16,993 | |
7 | 52,890 | 32,083 | 4,386 | 16,421 | |
8 | 38,902 | 23,087 | 1,245 | 14,570 | |
9 | 87,130 | 54,923 | 17,685 | 14,522 | |
10 | 67,935 | 42,012 | 12,290 | 13,633 | |
11 | 58,290 | 32,074 | 12,834 | 13,382 | |
12 | 84,589 | 54,023 | 17,528 | 13,038 | |
13 | 36,587 | 22,657 | 1,345 | 12,585 | |
14 | 47,890 | 32,'545 | 3,657 | 11,688 | |
15 | 56,294 | 27,801 | 16,923 | 11,570 | |
16 | 61,056 | 38,924 | 11,034 | 11,098 | |
17 | 56,902 | 32,789 | 13,904 | 10,209 | |
18 | 45,893 | 29,570 | 6,904 | 9,419 | |
19 | 62,954 | 41,034 | 13,746 | 8,174 | |
20 | 26,699 | 16,830 | 2,236 | 7,633 | |
Total | 1,279,133 | 779,003 | 209,852 | 290,278 | |
EXHIBIT 7 | |||||
Bottom 20 TFC Accounts for August, 2004 | |||||
(Ranked by Contribution $) | |||||
Account | Actual Net Sales | Product Cost | ABC Based Service Costs | Revised Contribution | |
1081 | 3,657 | 2,356 | 2,325 | â1,024 | |
1082 | 38,467 | 26,301 | 13,740 | â1,574 | |
1083 | 5,926 | 3,840 | 4,214 | â2,128 | |
1084 | 163 | 89 | 2,390 | â2,316 | |
1085 | 3,256 | 2,006 | 3,590 | â2,340 | |
1086 | 82,086 | 61,224 | 23,756 | â2,894 | |
1087 | 29,320 | 20,647 | 11,843 | â3,170 | |
1088 | 467 | 302 | 4,086 | â3,921 | |
1089 | 17,935 | 11,087 | 10,872 | â4,024 | |
1090 | 17,649 | 12,903 | 8,903 | â4,157 | |
1091 | 638 | 420 | 5,109 | â4,891 | |
1092 | 16,104 | 9,102 | 12,134 | â5,132 | |
1093 | 289 | 178 | 5,698 | â5,587 | |
1094 | 23,965 | 17,345 | 16,523 | â9,903 | |
1095 | 38,065 | 23,391 | 27,623 | â12,949 | |
1096 | 32,898 | 23,054 | 22,985 | â13,141 | |
1097 | 129,367 | 73,128 | 69,527 | â13,288 | |
1098 | 74,569 | 50,745 | 45,698 | â21,874 | |
1099 | 88,345 | 64,930 | 53,867 | â30,452 | |
1100 | 113,976 | 82,987 | 72,589 | â41,600 | |
Total | 717,142 | 486,035 | 417,472 | â186,365 | |
Exhibit 8
Current Operation Profit for 2004
The most profitable 5% of customers (55) contribute 80%
The next most profitable 45% of customers (145) contribute 220%.Profit could be 300% of the current level if we dropped theremaining 50% of customers (550)!
48% of customers (528) reduce profit by 140%.
2% of customers (22) reduce profit by 60%.
DISCUSSION QUESTIONS:
Discuss the following concepts only in the context of thisproblem.
1. Alternative Cost Driver Classification Schemes
2. ABC Implementation Issues
3. ABC and Customer Profitability Analysis
4. Activity Based Management
