Question - Spreadsheets
The country manager for Uber in Australia would like to know if the current pool of registered drivers with their cars can keep up with the demand for rides by customers in South East Queensland.
Use spreadsheet software such as Microsoft Excel or LibreOffice Calc to perform the following actions. You must use formulas for all calculations. (Refer to Workshops 5 & 6 for help.)
• Create a table (Table 1) that lists the number of requested rides for each month of 2015 for each of the three areas in South East Queensland (Brisbane, Gold Coast, Sunshine Coast). Fill the table with numbers of your choice. Paste a screenshot of the completed table into your Assignment Answers document.
Outline of Table 1
• Create another table (Table 2) that lists for each of the three areas the total number of rides fulfilled by all cars in that area in 2015.
Fill the table with numbers of your choice. (You only need to enter the yearly total for each area, i.e., one value for each area, not monthly values for each area.) For each area, make sure that the number of fulfilled rides is not greater than the total number of requested rides in 2015 (see the data in your Table 1), since a ride can only be fulfilled if it has been requested.
Then calculate the monthly average number of fulfilled rides for each of the three areas. Store the result of your calculation in the same table (Table 2).
Paste a screenshot of the completed table into your Assignment Answers document.
Outline of Table 2
• Create a new table (Table 3) to calculate for each month and each area how many rides were requested that were not fulfilled (i.e., the difference).
Using the data in Table 3, calculate the average number of rides that were requested but not fulfilled for each month.
• Using the data in Table 3, calculate the total number of rides that were requested but not fulfilled for each area. Paste a screenshot of the completed table into your Assignment Answers document.
Outline of Table 3
• Represent the results of your calculation for Point D above ("average number of rides that were requested but not fulfilled for each month") by using a 2D linechart. Format the chart appropriately and give it a title.
Paste a screenshot of the chart into your Assignment Answers document.
Represent the results of your calculation for Point E above ("total number of rides that were requested but not fulfilled for each area") by using a 2D columnchart. Format the chart appropriately including data labels and give it a title.
Paste a screenshot of the chart into your Assignment Answers document.
• Create a new table (Table 4) that lists the number of kilometres ridden per customer in each month of 2015. The table has the following columns:
• CustomerID
• Customer Name
• Month
• Kilometres
Fill the table with data for 5 customers that ride every month, i.e., you should have 5 customers x 12 months = 60 records.
Outline of Table 4
Create a pivot table based on the data in Table 4 that aggregates the kilometres for each customer. Add a slicer/filter to your pivot table that allows you to select the months for which the kilometres should be aggregated.
Paste a screenshot of the pivot table and slicer/filter into your Assignment Answers document.
• One of your customers listed in Table 4 notifies you that his/her name changed because he/she got married.
• In order to update the customer's name, how many records in Table 4 would you have to change?
• This is a case of a data __________ problem. What type of information system can we use to minimise these problems?
Explain your answers to a) and b) by writing them in the spreadsheet. Merge cells as required to make your text easily readable.
Paste a screenshot of your answers into your Assignment Answers document.
Tables attached are:-
Attachment:- Tables.rar