1. Fee Analysis for the Previous Year (2020)

Calculate the fees paid in 2020 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
2	$75
3	$100
4	$150
5	$200
6	$250
7	$300

• The Base Fee per call applies to False Alarm and Emergency Calls, and is based on the customers class as seen on this table. This fee should be identified in its own column on your spreadsheet and calculated for each customer. Write your formula on your spreadsheet so that it can be copied down and work for all customers.

• FA Fee (false alarm) To discourage customers from making false alarms an additional fee is applied to the customer if a call is a false alarm. Residential customers pay an FA fee of $130 per call. Business customers pay $230 per call. This fee should be identified in its own column on your spreadsheet and calculated for each customer. Write your formula on your spreadsheet so that it can be copied down and work for all customers.
• 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 SECURITY SERVICES charges an idle fee for such accounts. This $195 idle fee is applied to only those customers who have had no calls during the past year. This fee should be identified in its own column on your spreadsheet and calculated for each customer. Write your formula on your spreadsheet so that it can be copied down and work for all customers.

• Total Fees for customers - Calculate the total amount charged to the customer for all Fees for last years calls.
• Total of each Fee type - Calculate the total for each Fee type.

2. Customer Usage Analysis for Alternate pricing structures

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

• High Usage: A customer is in the high usage bracket if both
  • The total of False Alarm calls and Emergency calls was greater than 4.
  • False Alarm calls were greater than 1.
• Low Usage: A customer is in the low cost bracket if they are both
  • Not High Usage.
  • The total of false Alarm and Emergency calls are less than 2.
• Medium Usage: A customer is in the medium usage bracket if they are not in either a High Usage or Low Usage customer.
• Calculate the number of High, Low and Medium customers.

3. 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.

(I need help trying to figure out what formulas do I use for each of these step for A,B,C) and this is based on Excel