Bugs-B-Gone Report – CSE 1111 Final Project
CSE1111/1112 – Autumn 2013
Final Project – Bugs-B-Gone 100 Points
Page 4 of 4
Bugs-B-Gone Report – CSE 1111 Final Project
The Final Project integrates the database, spreadsheet, presentation, and word processing skills you have learned this quarter including using Mail Merge. The purpose of the project is to use these skills to solve a realistic problem that you might encounter in the real world. Please read through the entire assignment before proceeding. It is recommended that once you read the problem, you consider how to best organize the data. A little time spent in planning may save you a great deal of time later.
Page 4 of 4
Bugs-B-Gone Report – CSE 1111 Final Project
Problem Statement
Congratulations! You’ve been hired as an analyst at the prestigious Bug-B-Gone Extermination Firm, Inc. Your first job as an analyst is to perform some routine analysis of the company’s operations. You will have the company database (located on Carmen under Pre-Lab Files - BugBGone.accdb). For testing convenience, a small sample of the entire database has been given to you. The database is arranged as follows:
Customers : This table contains information relating to all businesses and individuals that your company has dealt with in the past. CustomerID is a unique numeric identifier of the customer. Each customer record also contains the customer’s Name, Address, City, State, Zip, Residential, Distance and Rooms fields. The Residential field indicates whether this customer is a residential customer (non-residential customers are considered a business). Distance, specifies the distance to this customer’s location from Bugs-B-Gone headquarters. Rooms, is the number of rooms in this home or business.
Vermin : This table lists all the vermin your company has the capability to exterminate. VerminID is a unique numeric identifier of this bug. Name is the vermin’s name, and Type is used in classifying this vermin (to be used in Part III).
Treatments : This table contains one record for each extermination job at a particular customer’s location. TreatmentID is a unique numeric identifier of this particular treatment. Customer and Vermin are the customer and vermin involved in this treatment session, respectively. The PoisonLbs field indicates how many pounds of poison were applied for this treatment. The Date field is the date of this treatment session.
Part I - Customer Analysis
Your supervisor has asked for an analysis of the treatment patterns and the resulting revenues and expenses from these treatments. To conduct this analysis, you will need to gather the following information from the company database for each customer:
· The customer’s profile (name, id, # rooms, distance from Bugs-Be-Gone, Residential or Business, etc.).
· The number of times this customer’s location was treated.
· The total number of pounds of poison used at this location.
After you have retrieved this information from the database, you will need to perform the following calculations (Hint - you may find is easier to copy the results to Excel at this point).
1. The total revenues earned from this customer based on the amount charged for these treatments. Our customer pricing contains three elements. These elements are added together to arrive at the total fees charged. They include a base fee plus a transportation charge plus a poison application fee.
· The Base fee is $25.00 per treatment. This is applied to each visit.
· Transportation fees are $3.00 per mile traveled roundtrip to/from the customer’s location for each treatment. Remember the distance listed is the number of miles one way that will be traveled.
· Poison application fees (labor and materials) vary depending on the customer:
o If the number of rooms at this location is more than 20, we charge the customer a rate of $15.00 per room per application for the poison. Otherwise the room charge is $20.00 per room per application.
2. The total expenses incurred by treating this customer. The expenses include the following:
· Cost of gas: this is based on the distance traveled to and from the location, the number of trips made to this location, and the cost of gas, which is currently $0.75 per mile.
· Cost of poison: the cost of purchasing and applying the poison is $2.00 per pound.
3. The net profit for this customer (i.e., Revenue – Expenses).
4. Customer Status
· Is this customer a frequent customer (True/False)? A frequent customer is defined as one that has had at least 3 treatments based on your sample data.
· Is this customer considered a poor profit customer (True/False)? They are considered a poor profit customer if the profit made from them is less than the average profit made on all customers.
· Is this a valued customer (True/False)? They are considered a valued customer if they either:
o Give us a profit of more than $1000.00, or
o They are a frequent customer and are not a poor profit customer.
5. Summaries
· After calculating the total company expenses, revenues and profit, calculate the total profit for Residential customers and Business customers. Prepare a chart comparing the relative percentage contributions of each group to profit.
· Also determine the number of customers in each customer status category (frequent, poor profit, and valued).
Part II - What-if Analyses
As part of your job as an analyst, you need to estimate the effect of three different possible scenarios on the existing data you have calculated. Assume that the changes are made to the data calculated in Part I and are not cumulative (each scenario should be considered independently). Hint: copy your results from Part I before making changes, so the original analysis is still available.
1. We are considering raising the base fee from $25.00 to $40.00 per treatment. How would this affect our profits (and the ratio of residential to business profit in the chart)?
2. We expect an increase in the cost of gas from $0.75 to $1.00 per mile. To help offset this, we are planning on raising the charge per mile to the customer to $3.50 per mile. Discuss the effects. Is it a drastic change in profits?
3. You would like to increase your profits by increasing your fees mainly for business customers. To accomplish this, since most of these customers have many rooms at their location, you’d like to modify the charge for poison application per room for only customers with more than twenty rooms, such that the new overall profit is 5% higher than your current profit. How much more would you need to charge per room, assuming all other pricing and cost structures remain the same?
Part III - Vermin Analysis
Bugs-B-Gone has a responsibility to submit the number of vermin exterminated and number of incidents to the Center for Disease Control (CDC), so that county-wide trends in vermin outbreaks can be monitored. To do this, you will need to gather the following information from the company database for vermin.
· Vermin name and type
· The number of treatments for this vermin during March, April or May of 2010.
· The total pounds of poison used to kill this vermin during March, April, or May 2010.
Since our exterminators use only the minimum amount of poison to exterminate a given vermin infestation, the number of pounds of poison used in a location is a good indication of the number of vermin killed. Calculate the estimated number of vermin killed by type during this time period based on the following table. Each pound of poison kills a certain number of vermin according to the following table:
Vermin Type / Number of Vermin Exterminated per Pound of PoisonA / 150
B / 30
C / 0.1
D / 0.01
E / 100
Create a summary table that lists vermin id, vermin name and vermin type, the number of treatments, total pounds of poison used, and the estimated number of bugs killed. Below your table create a chart showing, by vermin name, the number of treatments performed. Label the chart with the vermin name. (Hint – use Access to group the information together and then use a reference table in Excel to assign the appropriate poison usage.)
Part IV - Bug-Bopper Purchase
Your supervisor has found a great deal on a slightly-used Bug-Bopperä All-Purpose Extermination Vehicle. He wants you to calculate the payments on this $50,000 vehicle if we get a loan from the bank at 6% annual interest compounded monthly over the next 5 years. Payments will be made in equal monthly installments to the bank. We will be making an initial down payment of 25% of our total profits (use Part I calculations) shown in the database. How much will we need to set aside per year to make these payments? Place your calculation on a new worksheet called PartIV! in your workbook.
Part V - Picture Search
We are going to write a letter to all business customers (see Part VI). But before we do this, we need to find a picture of the most prevalent vermin. Search the Internet for a picture and save it to your computer for use in your letter mailing. For copyright reasons, include the page you located this image in the footer of your letter.
Part VI - Business Letter
The company wants to send a brief letter to all business customers (i.e. NOT residential). Use the mail merge facility of MS Word to create this letter. This letter should include the picture you obtained from the web, your company’s letterhead (be creative) and the corresponding customer’s name and address information, as well as the current date (this should automatically change each time the letter is opened). The body of the letter should inform your customers of the need for vigilance against the three top vermin (include the picture from Part V) and information on your pricing structures. Be brief and concise in your letter. Finally, include your name at the bottom. Submit a copy of the merged letter document, and the main document. Include a sample letter to a customer in your hard copy for the instructor.
Part VII - PowerPoint
Now is the time to collect all the information from all your analyses and summarize the results in a PowerPoint presentation. You may copy information from files created in previous sections, but do not simply copy the data directly with no explanation of the data. You will not need everything from your analyses – you need to decide what is important and what is not. Some suggestions are:
A description of each analysis (Parts I-VI).
· Part I – A summary of the customer data and the appropriate chart embedded in PowerPoint.
· Part II – The final results of you findings for each of the 3 what-if analyses
· Part III – A summary by vermin type and chart
· Part IV – A description and results of your Bug-Bopper financing
The presentation should be brief but clearly written and include headings and page information. Include at least one slide that uses animation and/or slide transitions. Your assumptions and conclusions must be carefully explained to management.
What To Turn In
Before the due date, you should use the submit tool to submit any files you created or used during the final project. This usually includes
· The original database file with any queries you used included
· The Excel file(s) with all of your analyses
· The PowerPoint file containing your presentation
· The template of the Mail Merge document
· The merged document
In addition to submitting these files (no later than due date indicated on page 1), you should also turn in a hard copy of the following to your instructor (please check with your instructor on how to submit these documents):
· A copy of the mail merge template document
· A sample (one letter only!) of the merged document
Final Notes
· Start early!! Plan on spending 10-20 hours on this project, and don’t neglect the planning stages. A little time spent planning the layout of your spreadsheets and reading ahead can save you a lot of time in the long run.
· You may ask your instructor for general advice, but the actual work must be your own. This is a real-world test of the skills you have learned during the quarter.
· This is an individual project. You may consult other students for general questions, but your final work must be your own. If two projects look similar, it is considered Academic Misconduct.
Page 4 of 4