Computer science (code 30424) a.y.
2018-2019
Practice session 4 – Stones
Stones Inc. is a company that operates in the precious stones sector with 40 retailers in Europe, Asia
and America. Each retailer regularly buys from the head office various amount of precious stones,
which are then carved and sold. The retailers pay to the head office the price of the purchased stones
in local currency. Stones Inc., which contacts these retailers on a regular basis, sets payments deadlines,
which are not always respected. The top management wants to analyze the cost of the phone calls
made to the retailers and the situation of late payments of its invoices.
Before starting the exercise, you are asked to rename the file Stones.xlsm as Stones XX
NNNNNNN.xlsm, where XX is the number of your class (e.g. 15) and NNNNNNN your student ID (e.g.
1234567).
The file you have just renamed consists of the following worksheets:
Invoices, where you can find a complete list of all the invoices which have not been paid so far
Retailers, which contains the retailers data
Data, which contains the exchange rates of the main currencies (expressed as the foreign
monetary value of 1 euro), and the levels of risk set by the Stones management for the invoices
yet to be paid
Phone calls, which contains a list of phone calls made by Stones in 2018
You are asked to solve the following five problems:
1) First, list next to each invoice, in two new columns of the Invoices worksheet, the city of each
retailer’s branch and the currency of the invoice, with reference to the data available in the
Retailers worksheet. Since the formula must be written once and then copied in the two
columns, do not use the number of columns but the field heading.
Since the invoices are expressed in different currencies, it is necessary to convert them into
euros: calculate in a new column of the Invoices worksheet the equivalent amount in euro for
each invoice, with reference to the rates expressed in the Data worksheet. Remember that
exchange rates are expressed here as the amount of foreign currency needed to get one euro.
2) In the Invoices worksheet calculate therefore, in a new column named Payment deadline, the
expiration date for each payment considering that:
For invoices issued from July 1, 2018 (included) on, payment should be made within 90 days
For invoices issued before July 1, 2018, the payment should be made within 180 days
In a new column of the Invoices worksheet named Time to/from deadline, calculate the
difference in days between the current date (making sure that it is automatically updated day by
day) and the deadline of each invoice. If the deadline is in the future, the result should be a
negative number.
3) The Data worksheet contains the different levels of risk set by the Stones Inc. management for
the invoices yet to be paid, according to the days that have passed since their maturity (for
example, from 0 to 29 days the risk is considered “very low” etc.). List in a new column of the
Invoices worksheet, based on the table available in the Data worksheet, the level of risk
corresponding to each invoice. If the deadline is yet to come, the result should be “On time”.
1
Apply a format to the cells of the Retailer code field of the Invoices worksheet so that the cells
corresponding to invoices with time from deadline equal or higher than 100 and with one of the
highest 15 amounts in euro are automatically highlighted with a fill color of your choice.
4) In a new worksheet, create a pivot table based on data of the Invoices worksheet that shows the
average invoice amount in euro based on the deadline year and month and on the city of the
retailer. Then:
Set the field Currency as Filter of the pivot table and make sure to show all the currencies
but the Japanese yen (JPY)
Filter the City field and show only Berlin, Milan, Washington and Zurich
Sort City labels so that they show in sequence: Washington, Milan, Zurich and Berlin
In a new worksheet named Average, use a function to calculate, for each city, the average
amount in euro (use data from the column Amount in euro) of invoices with invoice date after
10/1/2018. If there are no invoices with date invoice date after 10/1/2018, the text “No invoices”
must be shown. Sort the list of Average worksheet by city, in ascending order.
Create now a macro named Format, to be recorded in the current workbook, which performs
the following tasks in the Retailers worksheet:
apply a bold and italic format to the values in the City column (excluding the header)
apply a fill color of your choice to the column headers
sort the table based on the retailer code (in ascending order)
5) In a new column of the Phone calls worksheet named Call duration, calculate the duration
(length) of each call expressed in hours, minutes and seconds.
In a new column of the Phone calls worksheet named Day, use a function to show "Weekend"
or "Weekday" according to the day of the week in which the phone call was made (considering
here as "weekday" every day from Monday to Friday, although possibly corresponding to civil or
religious holidays).
The current call rates applied to Stones Inc. change based on the day of the week in which the
phone call is made, according to the following scheme:
Phone calls made on weekdays (Mon-Fri) € 0.19 per minute
Phone calls made during the weekend (Sat & Sun) € 0.15 per minute
In a new column of the Phone calls worksheet named Cost of call, calculate with the most
appropriate function, the cost of each phone call. Note: Calls rates are per minute, but the final
cost must consider also seconds and second fractions.
Apply the appropriate formatting and organize data in an appropriate manner, in order to ensure
maximum readability and give a neat and professional appearance to the worksheets.
Save the changes and close the file.
