Beta Auto Dealers Case Study
Case
Beta Auto Dealers, a Honda dealership with two locations in the metropolitan Phoenix area (Mesa and Peoria), sells new and used Honda vehicles and provides service and repair
Subject:ManagementPrice:40.01 Bought8
Share With
Beta Auto Dealers Case Study
Case
Beta Auto Dealers, a Honda dealership with two locations in the metropolitan Phoenix area (Mesa and Peoria), sells new and used Honda vehicles and provides service and repair. The company currently employs eight salespeople and carries eight Honda models: Accord, Civic, CR-V, Element, Insight, Odyssey, Pilot, and S-2000. Beta Auto Dealers is one of many vehicle dealerships in the Phoenix area and currently is struggling to stay abreast of the competition. Fred Beta, owner and president of Beta Auto Dealers, is convinced that business can be improved with better marketing and more information and has requested a management meeting.
The following discussion took place in the Peoria location boardroom on a hot July afternoon. Present at the meeting with Fred were Jake Radis and Patrick Murray, dealership managers at the Mesa and Peoria locations, respectively, and Lisa Koepke, the company controller. Lisa had asked Hudson Bradfield, a recent college graduate who had double majored in accounting and information systems, to join the meeting. Hudson, in his role as Assistant Controller has been charged with tactical and strategic planning of information systems. Fred opened the meeting:
Fred: I’m convinced that budgeting more for advertising is critical to our growth. It seems that our competition is more visible in the media. I’ve decided to increase our advertising budget 10 percent and have been in discussions with our advertising agency, MediaWorks, about how to spend the additional money most effectively. As you know, we currently have seven touchpoints that bring customers into the dealership: newspaper ads, television ads, radio ads, website, word-of-mouth, drove-bys, and Beta owners. MediaWorks is suggesting that we use the additional advertising monies on television advertising. Before I make a decision, however, I’d like to know if this is the area to focus on.
Jake: I know that television advertising generates a lot of customers for the Mesa location and agree this is where we should spend our money. Here’s the June data from our visit information system. As you can see, television advertising generated the most visits to Mesa. [See Table 1.]
Lisa: I know that you enter the touchpoint only for the customer’s first visit, so blank touchpoints represent repeat visits. The 56 visits from television ads correspond to only new customers and not to customers who have previously visited the dealership.2 We had 57 visits in June that were from customers who had visited before. Since we don’t enter a touchpoint for repeat customer visits, we don’t know what touchpoint generated these repeat visits.
Jake: Well, don’t ask us to collect that data. If we ask customers every time they come in the dealership what advertising medium brought them in, they would stop coming in! Currently, the system tracks touchpoint as part of the initial visit. A customer should have only one touchpoint.
Fred: I agree that touchpoints relate to customers. Is there any way to modify the system to associate a touchpoint with a customer rather than with a visit?
Lisa: Unfortunately, not. Mesa and Peoria use the same systems, but the systems aren’t networked, and some data are coded differently. I think that certain input data are called one thing in Mesa and another thing in Peoria. That was, no doubt, a result of minimal communication between locations when we implemented our systems. At this point, making changes to our systems is cost prohibitive. I don’t even know the programming language used or where to obtain the programming expertise. Apparently, the software vendor went out of business several years ago. We really need to do a massive overhaul of systems in the near future, but I guess that is a conversation for another day. [Lisa glances at Hudson].
Pat: Even if we ignore that problem, I disagree with Jake that we should spend more money on television advertising. I have the June printout from my location’s visit information system. At Peoria, we also get a lot of customers based on television ads—in June alone, it was 25 percent of our customers. However, most of these customers are just tire kickers as they always seem to wander around and never buy anything. I think they just don’t have anything better to do on the weekend! [See Table 2.]
Jake: Maybe your salespeople just aren’t convincing them to buy! Just kidding.
Fred: I’m sure that both of you have trained your salespeople well. I don’t know the average number of visits before a customer purchases, but sales were up last month. Lisa, how can we find out if we are generating sales from customers who visit the dealership because of television advertising?
Lisa: Remember that the sales information system is part of our accounting information system, while visits are recorded in a separate, stand-alone system. We input touchpoint data into the visit system but not the sale system. The sales data do not reference a visit number, and the visit data do not include a sales number. Therefore, we are not able to report the touchpoint for a sale. Maybe we could get Hudson to manually match up sales with the corresponding visit information to see what touchpoint generated each sale [Hudson winces here ... not exactly high-level systems work]. Of course, that isn’t a very efficient way to do that. Also, we still have this problem with missing touchpoints for repeat visits.
Pat: If someone is going to manually collect data, then let’s add some additional analyses. Right now, I get only two reports: Total Sales by Vehicle Model (which includes quantity sold, total sales dollars, and gross margin for each vehicle model) and Total Sales by Salesperson (which includes total sales dollars for each salesperson). While I can print these out at any time, they don’t give me enough detailed information on each salesperson’s performance. Only knowing the total sales dollars for each salesperson doesn’t give me the full picture of how each salesperson is doing. For example, are some of the salespeople more proactive in greeting customers on the lot, or are all salespeople basically greeting the same number of customers? Also, are certain salespeople better at converting their visits to sales?
Jake: I agree with Pat that it would be helpful to get more information on salespeople’s performance. The dealership gets money from Honda for selling Honda warranties and financing. I want to encourage our salespeople to sell these add-ons. I also want to know who sacrifices top price for a sale (thus cutting into our gross margin percentage). Obviously, a sale is better than no sale, but selling close to list price is what we want. I want to reward salespeople who generate high gross margins and sell the most add-ons, for these salespeople drive the hard bargain! Right now, we compile this information manually, which is really time consuming. Therefore, we see this information only periodically.
Lisa: I know, I know. It would be great if the systems talked to each other, and we could generate these types of reports, but again, we are working with an inflexible, mainframe system built in the early 1980s. Right now, Hudson and I are stretched trying to close the books every month. For a dealership this size, a financial close cycle of almost three weeks is too long, but consolidating the data just takes that much time! We’ve talked about installing an ERP system, but ...
Fred: Yes, I said it was too expensive right now. Hopefully, Hudson can help us find something down the road. Hudson, haven’t you already looked into some type of ERP system for our company?
Hudson: We’ve made a lot of progress researching various ERP vendors. However, ERP software for car dealerships is still fairly expensive for a small dealership like ours. Combine the software cost with implementation and conversion costs, and it’s even more expensive. I know a lot about databases and Access specifically. Until we convert to an ERP system, we could easily build a database to combine the existing data, and that way we could generate the needed reports without a lot of manual work.
Lisa: That sounds good. We could use a database to help us analyze data from our existing system. We already have Access. So, we could extract data from our existing customer, visit, and sales programs, and then import this data into a database that we could use to analyze some of the issues that we’ve discussed.
Fred: It would be great to get more data analysis, so we can make better decisions. For example, for things like radio and newspaper advertising, we could choose to target these ads toward specific cities or specific vehicle models. If we decide to increase our spending on these touchpoints, where would our money be most effective?
Jake: I agree with Fred. I could be more effective at managing the Mesa dealership with better information. For example, I know that the Accord is our big seller. However, I don’t know what touchpoints are most effective for selling the Accord, or where most of our Accord customers are located.
Pat: I’d like more information as well. During customer visits we collect information about touchpoint, customer location, age, and gender. We could do so much with this data. For example, we could tailor our advertising and sales if we knew whether gender and age are associated with sales of specific vehicle models or associated with visits by touchpoint. Also, we spend a lot of money on our web presence. Is this investment generating sales for us? Could the database answer these questions?
Lisa: Yes, it could. Once we have created the database and imported the data, we should be able to generate more useful information.
Fred: Sounds good to me. Why don’t you look at what we’ve got and see how a database could help us analyze our information? How long would it take to design this?
Hudson: We could probably have a prototype up and running in a couple of weeks.
Fred: Okay, let’s set up a meeting in two weeks once you’ve built a sample database and performed some initial analysis.
Beta Auto Dealers Visits and Sales Business Processes
To begin the database design process, Hudson wrote the following narrative describing the visits and sales business processes:
In a car dealership, it is very important for salespeople to make early contact with the customer. Therefore, salespeople are constantly looking for customers either driving or walking around the lot or entering the dealership. The first point of contact occurs when a salesperson greets the customer. The salesperson tries to find out what the customer’s needs are and will show the customer vehicles if the customer desires. During the visit, the salesperson obtains the customer’s name and telephone number and asks if the customer has been to the dealership location before. If the customer has not previously visited the location, then the salesperson also obtains the customer’s address and asks where the customer heard about Beta (e.g., newspaper, radio, etc.) to help management determine which advertising touchpoint was effective in bringing in that particular customer.
After the customer’s visit, the salesperson records information in the visit information system. For a new customer, the salesperson creates a new customer record in the system and inputs the customer’s name, complete address, and telephone number. The visit information system assigns a unique identifier as the customer number. For a customer who has previously visited the location, the salesperson uses the name and/or telephone number to look up the customer number. For each visit, the salesperson enters both the date and day of week of the visit, the customer name and number, and the vehicle model(s) the customer viewed during the visit. The salesperson also enters his/her own name and number in the visit system, in case the customer comes back to the dealership (i.e., the salesperson could then continue to assist this customer in hopes of making a sale). Salespeople also use the visit information to make follow-up contacts with a customer. The salesperson inputs the advertising touchpoint for a new customer in the visit system and leaves the field blank for a customer that visited the dealership before.
A customer may visit the dealership multiple times, and additional visits are recorded as new records in the visit information system. Additionally, a customer may talk with one salesperson on one visit and come in and request the same salesperson or choose to work with a different salesperson on another visit. Also, a customer may visit different locations, but the dealership has no way of knowing this, as each location tracks its own customers, visits, and sales, separately. Currently, salespeople work at one location, but sometimes may work at another location if that location is short-staffed or running a promotion.
When a customer decides to purchase a vehicle, the salesperson handling the sale fills out a sales form with information about the sale (and this is the salesperson that gets the commission). In the rare event that a customer purchases more than one vehicle at a time, each vehicle is recorded as a separate sale.
If a customer finances a vehicle or purchases an extended warranty through the dealership, the salespeople complete additional forms, from which a bookkeeper enters the information into the system. See Figure 1 for the data fields that were extracted from these information systems. You can also view the Microsoft Excel workbook containing data extracted from the company’s information systems.
Requirements
Assume the role of Hudson, Assistant Controller, in analyzing the extracted data to address the information needs revealed in the conversation. Conduct the analysis in the following phases using the Excel file (BetaAutoData.xls):
Draw a conceptual data model
Draw a conceptual data model of the business entities as represented by the conversation, the business processes, and data attributes using an REA model. Indicate minimum and maximum cardinalities. Refer to Beta’s business processes and data defined above. Use Chapter 17, Figure 17-6 of your textbook as an example (13e: page 509; 14e: page 533).
Customer
Cash Received
Cash
Salesperson
AR Clerk
Sell Vehicle
Inventory
Note: A few assumptions must be made about resources and the payment event. Assume that Beta Auto Dealers only sells inventory that is in stock, thus customers will not be ordering vehicles and every sale is only for one vehicle. Inventory is categorized based on model information. Additionally, assume that the dealership has multiple cash accounts and that customer payments are always deposited in the same cash account. Since the dealership offers financing, customers can make monthly payments on their vehicles so customers can buy now and pay in installments later. Any other assumptions that need to be made should be consistent with the business processes in a typical auto dealership.
Populate the database and implement the conceptual data model
Import data from BetaAutoData.xls into the database. A colleague has cleaned the “dirty” data for you, so the Excel file is formatted appropriately for Access. You will need to create the relationships and assign the primary and foreign keys in each table.
Note: when populating Access, you will also have multiple tables that are not part of the prior conceptual data model
Create queries and prepare a memo
Create queries to satisfy to answer the following questions:
What touchpoints result in the largest number of visits?
Is newspaper advertising more effective at creating sales in certain cities than in other cities?
What touchpoints are most effective at creating gross margin?
Do some salespeople generate higher gross margin percentage compared to other salespeople?
Exploratory question: Think of a question that could be answered with this data and provide the results in the memo.
Write a memo explaining the results of the querying and implications for the dealership. Incorporate the results of your queries in the memo and copy the resulting table from the query into the memo.
Note: Lucidchart.com appears to be an easy option for making an REA diagram. These this diagram can be made using any program (e.g., Word, Excel), but this is an option that may make it easier.
Memo
To:
Recipient Name
From:
Your Name
cc:
Name
Date:
Date
Re:
Subject
Adapt this memo to address the case study (please use your own name) The subject line should be very specific to the topic or purpose of the memo. A vague subject line could cause the recipient to discard the memo (or delete the email) without reading it.
Begin the memo with an executive summary. This paragraph should set the tone for the memo. When writing a memo to someone who has requested information, refer to that person’s request in the executive summary.
Response to the specific questions can be answered in bullet format, but provide an answer to the question and then copy the table from Access that supports that answer.
This is the answer to question one. Below is a table where that conclusion is drawn from.
Touchpoint
Number of Visits
Television
1
…
As with letter format, the body of memos should be single-spaced, with blank space between paragraphs and around headings. As with all business communications, apply visual design principles. Justify the text; straight left and right margins are visually appealing. Be sure to run a spellcheck and a grammar-check on your memos.
SQL Instructions
The steps to use SQL start out the same as using the interface. You will need to create the relationships, then click on Create, then Query Design. When the Show Table box displays, just choose Close. Even though the View button now shows SQL, you will need to click the down arrow and select SQL to get the Syntax Box.
Note: In SQL, you will have to type the information exactly as displayed but the spacing doesn’t matter. This whole syntax could be done as a single line. I placed the syntax so each line is a single command to help clarify what each command is doing.
