Homework # 2
FALL 2016
Lubin School of Business Pace University
Pricing Services for Wireless Telecommunications
A new service of Satellite Phone with coverage all around earth (and near areas, excepting the dark side of the moon) has four operators.
Market
The Pricing on the market is based on a system of Inbound and Outbound calls.
Example of Prices on the Market
Company
A
B
C
D
Price Outbound per minute (Po)
$0.12
$0.13
$0.14
$0.15
Price Inbound per minute (Pi)
$0.023
$0.03
$0.015
$0.016
Base Price (Bp)
$54
$62
$51
$50
Total Minutes included (*)(Tot)
300
350
400
500
(*) Minutes can be used for Inbound or Outbound calls. After the minutes are exhausted, prices apply to the additional minutes used.
Usage (*) <= Tot Fb = Bp
Final Bill (Fb) =
Usage > Tot Fb = Mo x Po + Mi x Pi + Bp
Where
Usage – Tot = Mo + Mi (**)
(*) Usage: Total minutes used by the customer in one month
(**) Mo: Minutes Outbound, Mi: Minutes Inbound
Examples:
A client from Company A that used 200 minutes in 1 month, will pay a Fb = $54
A client from Company A that used 1000 minutes in 1 month and after the first 300 minutes, used 200 Outbound and 500 Inbound, will pay a Fb = 200 x 0.12 + 500 x 0.023 + 54 = 24 + 11.5 + 54 = $89.5
Model
Using Excel/VBA you will write a Subroutine that will:
· Assign a color to each company. Suggestion: Write on the spreadsheet the table of prices above. Use cells B3 to E10 to enter the data.
· In a Cartesian plane created in the Spreadsheet, using as X axis the total minutes used in a month from 0 to 300 (each column represents an increase of 10 minutes, starting in 0 and ending in 3000 minutes), and Y axis the percentage of minutes used in Outbound from 0% to 100%, each row is an increase of 1% in the Outbound minutes.
The program will do the following:
1. The user will be able to change the prices of the companies.
2. The program will be activated pressing one button inserted in the Worksheet.
3. The program will color the cells from G12 with the color assigned to the company with the lowest price for each cell (Total Minutes, % usage of Outbound minutes).
Hints:
· Suggestion: Use the cells from H12 to the right and below
Cell H12
0% Outbound
0 Minutes Tot
Cell I13
1% Outbound
10 Minutes Tot
Cell J14
2% Outbound
20 Minutes Tot
Cell J15
3% Outbound
20 Minutes Tot
Etc..
Etc..
Etc..
· Reduce the size of Height to 0.06 (or similar) and Width to 0.04 (or similar) from H12 to 301 columns to the right and from H12 to 101 rows below.
· Use a double “For k =1 to n “ statement to calculate all possible combinations from 0 to 300 (intervals of 10 minutes) and 0 to 100 (1% per row increase in Outbound).
Results and due date
The results of the HW should be on a spreadsheet and submitted as an attachment to mvergara@pace.edu on Friday October 14th, 2016 no later than midnight.
Submit your HW, even if the program doesn’t run properly.