ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT

ASSESSMENT TWO SEMESTER 1, 2021
ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT
Due Date/Time: 11:59 pm, Friday 30th April 2021 30% of Final Grade
Call Centre Case Study
Calls R Us is a small call centre which provides customer services and sales for corporate clients. They are
based in a converted warehouse in Melbourne’s outer Western suburbs with room for 8 Call Centre agents
who answer the calls plus offices for management staff.
The business is jointly owned by business partners, Cecilia, Thomas and Makani who met while they were
students during their undergrad at RMIT and were also working part time in a call centre, so they have some
understanding of how a call centre works. Now that they have started their own business, they are finding that
managing a call centre is challenging.
Their clients are various electronic retail businesses who sell similar categories of electronic goods. Calls R
Us provide sales and customer support services for their clients by acting as their call centre (outsourcing to a
third party). Calls R Us has many phone numbers which all come into the same call centre and each phone
number is allocated to a different client. Our client’s customers do not know that they have called a thirdparty call centre, as far as the customer is concerned, they have called the retail business directly.
The clients advertise their allocated phone number to their customers and the agents have a phone display
which displays the phone number the customer dialled (the one allocated to a client) so it is important the
5/1/2021 64773 – ASSESSMENT TWO SEMESTER 1, 2021ANALYSING A SPREADSHEET
https://www.australiabesttutors.com/Recent_Question/64773/ASSESSMENT-TWO-SEMESTER-1-2021ANALYSING-A-SPREADSHEET 3/6
agent makes note of the phone number on their display so they know which client the call will be about and
greet the customer accordingly. Occasionally this has caused some confusion. If an agent forgets to look at
the display or forgets which client the phone number was allocated to, they might answer with the wrong
client greeting or discuss another client’s products.
Agents are in a virtual queue to receive the next call that comes through to the call centre. After each call the
agent is given at least 10 seconds before they get another call so they can wrap up any leftover work from the
previous call. If they need more time to finish a complex call the agent can put themself at the back of the
queue or make themself unavailable until they are ready.
The call centre only operates as an inbound call centre (i.e. a customer initiates the call rather than outbound
calls where the call centre tries to generate business by calling customers). Operating hours are 9am – 5pm
seven days a week.
Calls R Us are paid a fee directly by their clients. $15 for calls that are resolved and $2 for calls which the
agent could not resolve, e.g. could not find the information the customer needed or did not make a sale. The
agent records the outcome of the call as resolved or unresolved in the CRM system the same whether it is a
sale or a simple service enquiry call.
Cecilia takes care of accounting and finance, Makani sales (of our call centre services to our clients) and
forecasting whilst Thomas manages the daily operations of the call centre. Call centre agents are rostered
onto variable shifts depending on how many clients they have and the number of calls they are expecting.
Matching the staff numbers to their expected call volumes is a key challenge of any call centre. Too many
staff and they sit idle eating into the profits. Not enough staff and the customer’s waiting times increase
making them frustrated and less likely to make purchases. The challenges of getting this right have been a
source of tension for the three partners. As the Operations manager, Thomas is keen to roster many staff on as
he is responsible for making sure calls are handled quickly and customer waiting times are low. Cecilia is
pressuring Thomas to reduce the staff however, as idle staff reduce their profit margins, “We can’t be paying
for people to read magazines and complete their uni assignments in between calls.” Cecilia also thinks
Makani is not doing a good enough job at forecasting the number of client campaigns and the number of calls
each campaign should expect. Makani thinks having more people than they need is better than not having
enough and is already concerned that they are missing too many calls as it is with a number of calls not
answered. Cecilia thinks there are more than enough staff, they are just not working efficiently.
The management team have not really been paying attention to the Customer Satisfaction ratings. Customers
are directed to an automated voice prompt to rate the call (1 Poor – 5 Excellent) once they have finished
talking to an agent. The average satisfaction ratings grouped by product are provided direct to the clients at
the end of every month via an automated report without the call centre management team’s involvement.
Thomas had a recent informal conversation with one of their clients who was concerned about the customer
satisfaction ratings. The client also queried why they were charged for some unresolved calls with an
unknown product. The client warned that without improvements the client would be looking for a new call
centre. Thomas thinks he should have further information before he raises the issue with the other partners
and clients.
Calls R Us are not paid by the customers calling the call centre but by their clients, so they need to make sure
they are responding to their client’s needs. On the other hand, if they do not provide a good service to the
customers, then their clients will not want to continue to use their services.
Cecilia, Thomas and Makani seek your consultation to help them understand the performance of their Call
Centre and how well their business has been running for the last month.
What should they be measuring? Average Wait Time (time the customer waits in a queue before they speak to
a real person) and Average Talk Time (how long it takes the operator to complete the call once they start
talking) or is it just important to know how many calls were resolved successfully and try to improve
revenue? Are there enough or too many staff rostered on to manage the number and duration of calls? How
well are they serving their clients and could this be improved?
A spreadsheet has been provided containing call record data for the month of January 2021.
Some of the data and calculations have been completed for you and you are guided to some of the other
calculations and tools. Costs have not been provided so you are not required to make calculations and
predictions regarding profitability of the business.
A summary sheet has been started for you. You may make your calculations using any Excel methods/tools
you are comfortable with.
5/1/2021 64773 – ASSESSMENT TWO SEMESTER 1, 2021ANALYSING A SPREADSHEET
https://www.australiabesttutors.com/Recent_Question/64773/ASSESSMENT-TWO-SEMESTER-1-2021ANALYSING-A-SPREADSHEET 4/6
Report Requirements
You are expected to undertake descriptive, predictive and prescriptive analysis of the data that Calls R Us has
provided and use Excel to analyse the data. The results of the analysis are then required to be presented in a
professionally organized report that analyses and discusses both operations so far and the various options
available to help improve future operations. Some specific expectations are as follows:
a) Analyse the January call records to identify the key variables/features (e.g. products, client satisfaction,
agents, call durations, call outcomes, etc). Has the Call Centre been well run?
b) Discuss at least 2 options to improve the performance of the variables/features identified in (a).
c) Predict the likely outcomes of these key variables/features for the first two weeks of February (assuming
no changes to business operations since January) and then compare the effect of the changes discussed in (b)
for the same two weeks.
d) Identify at least one business issue which the business should further explore to improve their business.
Clearly explain your reasoning using data to support your discussion. Justify how this would improve the
business.
e) Make two specific recommendations to the owners to improve the management of the business. Briefly
justify these recommendations (i.e. prescribe what should be done and why this would be helpful, drawing on
your previous predictive and descriptive analysis). Demonstrate the benefits to the business of acting on these
recommendations
Preparing the Business Report
Report structure and specifications
The analysis, findings and recommendations which you prepare for Cecilia, Thomas and Makani should be
outlined in a professional business report. This report should include:
? A title page – with a relevant graphic, report title, student name/number, course name (Business
Information Systems), course number (ISYS2056), your workshop time and your workshop tutors’ names.
? A Table of Contents (TOC) automatically generated by Microsoft Word. Default settings for the table of
contents should not be altered.
? A brief introduction which clearly outlines the purpose of the report.
? Presentation of the analysis and discussion regarding the results of your analysis. Relevant descriptive,
predictive and prescriptive analysis has been included.
? Relevant charts which are labelled correctly and assist the reader in understanding the results of your
analysis included in the appropriate analysis section
? A short concluding summary of the major findings of the report. (supported by relevant data: e.g., use final
figures you have calculated. etc)
? Clear recommendations that the business owner can adopt. These recommendations must be drawn from the
analysis you have carried out in the body of the report.
? You should incorporate good report writing practices – refer to “Writing a Business Report” on the
assignment page.
Report format
The report should be developed using the guidelines covered in Canvas and workshops. Specifically, you
should:
• Ensure your report is concise – No more than 1200 words. This word count includes title page and TOC. It
does not include appendices.
• Present the report in a professional, business format with no spelling or grammatical errors.
• Use appropriate headings and subheadings throughout the body of the report.
? Use double or 1.5 spacing.
? Fully justify all text in the report.
? Include a page header with a suitably formatted title (the header must not appear on the title page).
? Include a page footer with your name, student number and page number (the footer must not appear on the
title page).
? Include relevant charts and tables you have created in the body of your report (appropriately labelled). The
charts and tables (screenshots of your spreadsheet are not appropriate) must be discussed as part of the paper
(not in the appendix)
? In an appendix include screen shots of calculations (as discussed below) which you have used to assist in
5/1/2021 64773 – ASSESSMENT TWO SEMESTER 1, 2021ANALYSING A SPREADSHEET
https://www.australiabesttutors.com/Recent_Question/64773/ASSESSMENT-TWO-SEMESTER-1-2021ANALYSING-A-SPREADSHEET 5/6
your analysis. (Note: You do not need to refer to this appendix in the body of your report.) A guide to
inserting screen shots commences on page 4 of this document.
? Do not include references or citations in this report
Report Submission
The Business Report must be submitted, in MS Word format, by 11:59 pm Friday 30th April, via the link
provided in the “Assignments” area on the ISYS2056 Business Information Systems site on Canvas. A
Student Guide to submission (using Turnitin) is available at:
https://www.rmit.edu.au/students/support-and-facilities/it-services-forstudents/canvas/turnitin
A Guide to inserting a screenshot in ‘Word’
• Open and/or minimise the program/sheet you wish to create the screen shot from (in this case your Excel
document). As your screenshots are designed to show calculations, you should make sure you have
highlighted the relevant cell and the function is clearly visible in the function dialogue (fx) box.
• Open your ‘Word’ document.
• Select Insert/Screenshot/Screen Clipping.
• Select the sheet you wish to create the screenshot from. The screen should fade and a + appear. Use the + as
a cropping tool to select the required screenshot. Note: There is no need to show every cell. (See Figure 1 in
which one of the relevant cells is highlighted (B3) and the function for column B is clearly visible.)
• Label the screenshot appropriately.
Figure 1 – Screen shot of Countif function
If you need to create a screenshot of a column that is not located near the fx box (see Figure 2) you can:
• Select the relevant cell. Double click on the cell to show the formula.
• Create screen clipping as per the instructions above. Provide an appropriate label.
Figure 2 – Screenshot of Sumif function
To show several formulas in the one screenshot (see Figure 3) you can:
• Select the relevant cells.
• Select Formulas/Formula Auditing/Show formulas.
• Take the screenshot as per the directions above. (NOTE: Text needs to be large enough to be read clearly.)