1. Extract Customer Information for Active Customers

Download the Resource 1111 Final Project Access SP20 file from SIMnet. You must use this file for your Final Project. Since you only have the database initially, you will need to write a query to collect this information from any Access table/s you think are necessary. Your query will need to list the CustomerID, Residential category, Class, the number of False alarms in the previous year, the number actual Emergency calls in previous year for only Active Customers. Name the query Part A. In Access remember to setup your relationship table first before doing a query.

CustomerID

Residential

Class

The number of False alarms in Previous Year

The number of actual Emergencies in Previous Year

Remember only active customers.

Download the Resource 1111 Final Project Excel file from SIMnet. Copy the results of your query to this Excel file and then continue in Excel with your calculations. Name this worksheet Part I. How you will setup/organize your spreadsheet is up to you; however for claritys sake the calculated results of each of the following items A-D should be presented on one sheet adding columns/rows as necessary. Use cell references for all of your given data input values and intermediate calculations in your workbook such that if any of the inputs later change, the values can be quickly and easily updated. Write equations so that they can be copied for more efficient spreadsheet design. Your workbook should be easy to read with information easily identified.

2. Fee Analysis for the Previous Year (2019)

Calculate the fees paid in 2019 by each customer based on the pricing structure described below. The fees paid include a base fee per call and a false alarm fee. In cases where customers have no alarm calls, a minimum idle fee is applied.

Class	Base Fee/Call
1	$50.00
2	$75.00
3	$125.00
4	$200.00
5	$250.00
6	$300.00
7	$500.00

• The Base Fee per call is based on the customers class as seen on this table.

• FA Fee (false alarm) To discourage customers from making false alarms an additional fee is applied to the base fee if a call is a false alarm. Residential customers pay an FA fee of $75.00 per call. Business customers pay $300.00 per call.

• Idle Fee There are costs associated with maintaining accounts, even for customers who have made no calls in the past year. These costs include personnel manning phones, the equipment provided at the locations etc. To cover these costs SMART SECURITY charges idle fee for such accounts. This $45.00 idle fee is applied to only those customers who have had no calls during the past year.

• Total Fees - Calculate the total amount charged to the client for last years calls.
• Grand totals - Calculate the grand totals for each fee type.

3. Customer Usage Analysis for Alternate pricing structures

SMART SECURITY is considering a new pricing structure based on security service usage levels (High, Medium or Low). For each customer, you want to determine (True/False) if this customer falls in that category (i.e. you want to make three new columns, one for each of the categories). The criteria for each category are as follows:

• High Usage: A customer is in the high usage bracket if either
  • The customer is a residential customer and the number of false alarms they called in was higher than the number of actual emergencies.
  • The customer is a business customer and the total number of calls they called in (false alarms and emergencies) was greater than 1.

• Low Usage: A customer is in the low cost bracket if they are both
  • Not in the high usage bracket.
  • The total number of calls they made is less than or equal to 2.
• Medium Usage: A customer is in the medium usage bracket if they are not in either one of the other two brackets.

4. Summary Information

Calculate the following summary information based on the original pricing structure (section B).

• Calculate the total revenue and the average revenues from business customers.
• Calculate the total revenue and the average revenues from residential customers.
• Calculate the total revenue and average revenues for all customers.

Part II Alternate Scenarios

SMART SECURITY has chosen to remain with the traditional pricing structures as described in Part I section B, however they are interested analyzing the effects of modifying the actual fees used. Your job is to note how each of these proposed changes will affect the companys revenues. Since each scenario is based on the numbers calculated in Part I, copy of your work from Part I onto 3 additional worksheets, one for each of the alternate scenarios (PartIIA, PartIIB, PartIIC), so that your previous analysis is unaffected by the changes you will make.

Class	Base Fee/Call
1	$25.00
2	$35.00
3	$85.00
4	$150.00
5	$190.00
6	$220.00
7	$300.00

Scenario (A): Decrease the False Alarm Fees for Businesses to $250.00, and increase it for residential customers to $125.00. Highlight any input/output values that are affected in green.

Scenario (B): Change the per-call base fees as listed on this table. Highlight any input/output values that are affected in green.

Scenario (C): Determine how much SMART SECURITY needs to raise its Idle Fee in order to have a total revenue of $38,000.00. Highlight the Idle Fee and Revenue values in green.

Part III Chart

Create a chart comparing relative percentages of the 3 fees. Use the values from Part 1 with the original fees. Your chart should include a title, legend and percent values.

Place your information and chart on a new worksheet named chart1.

Part IV Financial Analysis

Place your information on a new worksheet named financial.

SMART SECURITY wants to purchase a new building that costs $100,000. They can spare 10% of the total fees to use as a down payment on the building. The First Bank of Ohio is offering them a loan with an annual interest rate of 5% compounded monthly for 3 years. Determine the monthly payment using an Excel financial function. Highlight your answer in yellow.