Scenario (A): Increase the False Alarm Fees for Businesses to $250.00 and increase it for residential customers to $150.00. Highlight any input/output values that are affected with yellow fill color.

Class	Base Fee/Call
1	$100
2	$125
3	$175
4	$200
5	$300
6	$350
7	$400

Scenario (B): Change the per-call base fees as listed on this table. Highlight any input/output values that are affected with yellow fill color. (the chart above is for B)

Scenario (C): Determine how much SECURITY SERVICES needs to raise its Idle Fee in order to have a total revenue of $41,000.00. Highlight the Idle Fee and Total Revenue values with yellow fill color.

I need help figuring out what formulas to use for each of these steps

This is for a final project, and I attached the other instructions below.

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.

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

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

SECURITY SERVICES 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 your work from your Part I worksheet onto 3 additional worksheets (NOT WORKBOOKS). Name the new worksheets PartIIA, PartIIB, PartIIC, one for each of the alternate scenarios. This is done so that your original analysis is unaffected by the changes you will make.