Mackleby Autos Case Project
Overview
Mackleby Autos is an auto repair shop with locations in Tempe and Mesa. The company has set prices for oil changes
($25) and fixing flat tires ($25). For other services, the shop manager will estimate the parts and labor for the repair and
quote this price to the customer. This price is charged to the customer even if the parts and/or labor charges differ from the
estimate. This has been William Mackleby’s policy as he wants customers to know upfront exactly what they will pay for
a repair. William Mackleby (the owner) has always advertised that his shop will perform work on any vehicle. But that is
becoming problematic as cars are becoming increasingly more complex.
In the repair shop, they keep a log of each service that is completed. They record the customer number, date the work is
completed, the mechanic that did the work, the number of minutes that the mechanic actually spent on the job, the total
cost of the parts and the service charge.
In the accounting office, they keep records that track the customer number, the total charge of all services performed for a
customer during the shop visit, any promotional discounts that the customer received, and the final amount charged the
customer. All records are kept manually.
Mackleby believes that company management doesn’t have a good handle on their business. Management cannot easily
identify repeat customers. Management cannot easily determine whether work on some types of cars is more profitable
than others or whether they should be focusing on one type of repair. They have no way of evaluating which mechanics
are bringing them the most profit. They do not know how to evaluate the effectiveness of their promotions.
Your group has been hired by Mackleby Autos to help them with their concerns about obtaining better information from
the data they are calculating. Consultants have already started on the project and designed a database for the company.
This database is the MackelbyAutosDatabase.accdb found here on Blackboard.
Additional Information
When a customer comes in, they can have one or more services performed on their car. Each service is provided by one
mechanic. However, since a customer can have multiple services provided at a time, several different employees can work
on the sale. For example:
John Doe could bring in his Ford for a Hose/Belt Replacement and an Oil Change. Mechanic A could do
the Hose/Belt Replacement and Mechanic B could do the Oil Change.
It is important for the company to track which mechanic does what service – in case there are any questions or problems
with the vehicle or the warranty, Mackleby Autos needs to be able to determine which mechanic did the work. They also
need to be able to determine where the work was performed (the Tempe or Mesa location)
Customers can visit many different times throughout the year (e.g. on Monday they get their brakes fixed and on
Wednesday they have an oil change or they come in once a month for an oil change). Customers may have multiple cars
to bring in (e.g. they bring in their Ford for an oil change and then their Mercedes for work on the brakes). However, if a
customer brings in two different cars at once, the work on each car is assigned a different sale number. For example:
John Doe brings in his Ford for a Hose/Belt Replacement and his Mercedes for an oil change. The
services performed on the Ford would be on one Sale#; the services on the Mercedes, on another Sale #
Each location assigns a customer number to their customers as well as a sale number. The Tempe location uses 5 digit
customer numbers (starting with a one) and 5 digit sales numbers that are preceded by a “T-”. The Mesa location uses 5
digit customer numbers (starting with a five) and 5 digit sales numbers that are preceded by an “M-“.
CIS 235 1 Spring 2013
Mackleby Autos Case Project
After the work is completed, the customer goes to the cashier for their sales invoice and to arrange payment. At that time,
the cashier totals up all of the work done, applies any coupons/promotional discounts and calculates the net amount of the
total sale. The company has three types of coupons/promotional discounts.
1. “Repeat Oil Change” – Mackleby Autos sends out a coupon to customers that have previously been in for
an oil change. The coupon is sent to the customer’s home address 3 months after the last oil change and is
good for $5 off of their next oil change. These are good at any location.
2. “Newspaper Ad” – Mackleby Autos runs a weekly coupon in the newspaper that offers $20 off of any
service of $75 or more. These are good at any location.
3. “Flyer” – Mackleby Autos periodically sends out flyers to their customers. Flyers are good for a 10%
discount on any visit.
For this project, you do not have to worry about tracking cash receipts or deposits.
In your analysis use a fully weighted mechanics cost of $28.52 per hour.
Homework Requirements
Collectively we will do the first 6 steps of this project in class. The remaining steps will be done individually as HW6.
You will need the following files, which can be found on Blackboard to complete this assignment:
MackelbyAutosDatabaseLess3 .accdb
MackelbyAutosOneMonthDataForLoading.xlsx
Satisfaction Survey.xlsx
You are to do the following things to your database in class:
1. Use the information from the MackelbyAutosOneMonthDataForLoading.xlsx workbook to complete
populating the MacklebyAutosDatabaseLess3.accdb database. Most of the data has been loaded but you need to
load the data into the Employees, Services and Sales_Services tables. Use the appropriate light-blue colored
worksheets to do this. Remember that as you load the data into each table, the order in which you load the tables
is important.
2. In the database create a table called SurveyResults with the appropriate fields for loading the data from the
Satisfaction Survey excel file.
3. Create the appropriate relationship(s) between this SurveyResults table and the rest of the database. The survey
measures satisfaction on a scale of 1 to 10.
4. Load the data into this table.
5. Note that the database has a query in it called CalculationsDetail. This query already has the following fields inn
it:
SaleNumber
Service
Promotion
ServiceCharge
OCDisc – the discount calculated from the Repeat Oil Change coupon
NewsDisc – the discount calculated from the Newspaper Ad
6. Create the following new fields:
FlyerDisc – the discount calculated from the Flyer mailing
TotalDisc – the total of all discounts
NetServiceCharge – ServiceCharge minusTotalDisc
CIS 235 2 Spring 2013
Mackleby Autos Case Project
Complete the homework assignment by starting with the MackelbyAutosDatabasePlusSurveyPlusCalc.accdb database
which reflects what we have done in the 6 steps above. Make a copy of this database and rename it in the following format
so that it is easy for me to see whose homework I am looking at:
FirstNameLastNameMackleby.accdb
7. Add the following additional fields in the following order to the CalculationsDetail query:
Employee
AutoMake
Number of Minutes
Cost of Parts
LaborCost – calculated based on a fully weighted mechanics cost of $28.52 per hour
Profit - calculated as the NetServiceCharge charge minus the Cost of Parts minus the LaborCost
Sort the query in ascending order by SaleNumber.
8. Create a report that presents the total profitability of various services performed on various makes of cars. To
create this report, create the appropriate crosstab query and then base your report on the crosstab query.
Profit is defined as the NetServiceCharge charge minus the CostOfParts minus the LaborCost
The report will have 6 columns: Auto Make, Brakes, Flat Tire Fix, Hose/Belt Replacement, Oil Change
and Total-All Services. The first column will contain the name of the Auto Make. The subsequent
columns will contain total profit for each category.
There will be a row for each make of car (Auto Make).
The report will have a summary row with dealership (all makes) totals.
Amounts should be formatted as currency and show dollars and cents.
In the report footer include an observation about the profitability of performing oil changes and what
action this observation suggests
The report footer will also contain your name.
9. Create a crosstab query that will show the average level of Satisfaction broken down by car serviced and by
location doing the servicing. Incorporate this query in a simple report changing the title and adding Labels as
needed to make it understandable. Make all amounts appear formatted with 2 positions after the decimal.
CIS 235 3 Spring 2013
Powered by TCPDF (www.tcpdf.org)
