Due: Wednesday, April 19, 2017 by Midnight

100 Points

No Late Projects will be accepted

Page 1 of 5

The Final Project integrates the database, spreadsheet, presentation, and word processing skills you have learned this semester 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 1 of 5

Problem Statement

Congratulations! You’ve been hired as an analyst at the prestigious No More Pests 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 - NoMorePests.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 No More Pests 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 No More Pests, Residential or Business.
  • The number of times this customer’s location was treated.
  • The total amount of pounds of poison used at this location.

Remember to set up your database with the relationships before running your queries. 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). Calculate for each customer the Base Fee, Transportation Fee, and Poison Application Fee for revenue. Then calculate the Total Revenue. Calculate Cost of Gas and Cost of Poison for Expenses. Then calculate the Total Expenses.

  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 $50.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:
  • 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 $30.00 per room per application.
  1. 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.
  1. Calculate the net profit for this customer (i.e., Revenue – Expenses).
  2. Calculate the Customer Status for each category.
  • 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:
  • Give us a profit of more than $1000.00, or
  • They are a frequent customer and are not a poor profit customer.
  1. Summaries
  • Determine the number of customers in each customer status category (frequent, poor profit, and valued) that are true.
  • After calculating the total company expenses, revenues and profit, calculate the Total Profit for Residential Customers and Business Customers set this up as a summary area on your spreadsheet.
  • Prepare a chart comparing the relative percentage contributions of each group (residential and business) to profit. Place the chart beside the Summary Total Profit for Residential and Business Customers. Name your chart Profit Breakdown, include a legend, and percentages.

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. Each What If Analysis should use the original Part 1 values.

  1. We are considering raising the base fee from $50 to $75.00 per treatment. How would this affect our profits and the ratio of residential to business profit in the chart? Highlight your answer in green. Name this spreadsheet What If 1.
  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. Name this spreadsheet What If 2. Calculate the change in profits and highlight in green.
  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 15% 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? Highlight your changes in green. Name this spreadsheet What If 3.

Part III - Vermin Analysis

No More Pests 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 ID, name and type
  • The number of treatments for this vermin during April, May, and June of 2016.
  • The total pounds of poison used to exterminate this vermin by customer during April, May, or June of 2016.

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 Poison
A / 150
B / 30
C / 1
D / 2
E / 100

On a spreadsheet named Vermin Analysis create a summary table that lists vermin id, vermin name and vermin type, the number of treatments, total pounds of poison used, and the calculated number of pests killed. Below your table create a chart showing, by vermin name, the number of customers treated. Include a chart title, axis titles and data labels. (Hint – use Access to group the information together and then use a reference table in Excel to assign the appropriate poison usage.)

Identify the top 3 vermin exterminated. Highlight in green.

Part IV – Commercial Vehicle

Your supervisor has found a great deal on a slightly-used commercial vehicle. The supervisor wants you to calculate the payments on this $50,000 vehicle if we get a loan from the bank at 4% 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) How much are the monthly payments? What are the yearly payments? Place your calculations on a new worksheet called Vehicle in your workbook.

Part V - Business Letter

The company wants to send a brief letter to all business customers (i.e. NOT residential). Use the mail merge from MS Word to create this letter. This letter should include your company’s letterhead (create your own and be creative) and the corresponding customer’s name and address information, as well as the date, The body of the letter should inform your customers of the need for vigilance against the three top vermin 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 template document and merged letter document.

Part VI – Power Point

Now is the time to collect all the information from all your analyses and summarize the results in a Power Point 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 – as a minimum include:

A description of each analysis (Parts I-VI).

  • Part I – A summary of the customer data and the appropriate chart.
  • Part II – The final results of your findings for each of the 3 what-if analyses.
  • Part III – A summary by vermin type and related chart.
  • Part IV – A description and results of your commercial vehicle financing.

The presentation should be brief but clearly written. Include a Power Point design theme, title page with your name, at least one slide that uses animation and slide transitions.

What To Turn In

Before the due date time, 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 semester.
  • 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 or parts of any files are copied from any other source other than what is provided, it will be considered Academic Misconduct. You will receive zero credit.
  • You must be attendance during Lab sessions, a 10% deduction from your total project points will be taken for each Lab you are not in attendance. Only exception: If your project is complete and submitted to Carmen you do not need to be present at Lab.
  • Good Luck!!

Page 1 of 5