Assignment 2
BFA504 Semester 1, 2021
Data Analytics and Data Visualization
Assignment Type: Individual
Weighting: 20%
Due Date: 10th of May (Monday: 11:59 PM)
Submission: Upload files on to MyLO. Click here for link
Submission Files Word file of Report to manager
Excel file(s) showing calculations
Tableau Workbook File
Context
You are a financial accountant for an Australian manufacturer called “OzMade”. Recently the entire accounting and finance department attended a workshop on Data Analytics in Accounting. Your manager is excited about employing the new skills and knowledge gained from the workshop and sends you the following email:
------------------------------------------------------------------------------------------------------------------------------------------
Hi [Your Name],
As you know, the board of directors have been interested in establishing a presence in the North American market for a while. Obviously COVID has hampered all these plans but they’re optimistic that the world will return to normal (whatever that looks like) by next year.
In preparation for this they’re requested that I assist them in deciding on which state in America we should open our first shop when things return to “normal”.
So remember that exercise we did at the data analytics workshop? The one where we had to calculate Altman’s Z to determine the risk of bankruptcy. Well, I did some further research and found there’s many bankruptcy prediction models. I’m quite interested in this model called Ohlson’s O-Score. I’ve attached a word document explaining the model and how to calculate it. There’s also an excel template that you could use to check the formulas for the model.
I’d like you to calculate the Ohlson’s O-Score for the following companies in the attached excel file. I got the new graduate to extract the data of US manufacturing companies for the years 2019 and 2020. I’ll only require you to calculate the O-Score for 2020 but to do that you’ll need 2019 data as well.
I’m not sure if the data is accurate but even if it isn’t at least you can develop a template that we could use to calculate the O-Score.
Once you calculate the O-Score, I’d like you to use Tableau to present the data in a Map of the United States of America. For each State, you should show the average O-Score of US manufacturing companies in that state. I want the colour scheme to be green to red. Green representing low risk of bankruptcy and red representing high risk of bankruptcy. I also want the data labels to show the names of the states. I will use the map to identify those states with the greatest risk of bankruptcy of US manufacturing companies. I believe that these are the states that the company should target as it must be cheaper to set up in these states and once COVID is over, people will start returning to manufacturing shops.
Could you send me the excel file with the template and also a report that:
i.) presents your analysis of the data visualization map on Tableau based on my methodology described above;
ii.) evaluates my methodology;
iii.) discusses the limitations of Ohlson’s O-Score as a predictor of bankruptcy; iv.) evaluates the quality of the data compiled by the graduate;
v.) discusses any other issues that could be relevant in our analysis.
This is all a very rough idea so there could be flaws in my methodology. I’m not entirely sure if we should focus on bankrupt states or safe states. It all depends on what we think will happen after COVID. Your analysis might even lead me to conclude that maybe we shouldn’t invest in the US at all. So I’m really looking forward to what your analysis will inspire and it will help me improve my methodology so that I can provide more accurate and relevant advice to the board of directors.
I’m also not sure about the quality of the data collected by the graduate. I gave him a very short deadline so there could be errors in the data. I don’t expect you to check the data but please let me know if the final data visualization doesn’t seem to make sense in light of what we would expect during COVID.
Lastly, if there any other issues that you identify or even a better way of helping the board in their decision to establish a store in the US, then please also provide a short note on that in the report.
Grateful if you could send me all the files by the 10th of May as my presentation to the board is at the end of May.
Should you have any questions or queries please feel free to contact me.
Kind Regards,
Geoff Bayzos
What you’ll need to complete the assignment?
Files
All the necessary files for the assignment can be found here.
i.) US manufacturing Companies 2019 to 2020 (Excel)
This is the excel file that was compiled by the graduate. The file is very similar to the excel file used in the lab exercise on Altman’s Z. It also contains additional details such as the states where the head quarters of the companies are based and there is an additional variable titled “FO”. This represents “Funding Operations” and is necessary for calculating Ohlson’s O-Score. There are also two additional sheets titled “GNP” and “Data Dictionary”. The GNP sheet contains the US Gross National Product index for 2019 and 2020 and is necessary for calculating the first variable in Ohlson’s O-Score. (Hint: use VLOOKUP() to input the GNP index for the respective year in each row. You can then apply the formula to calculate the Adjusted Size). The “Data Dictionary” sheet provides definitions for the data in the “2019_2020” course. ii.) How to calculate Ohlson O-Score (Word)
A word document that provides a clear explanation on calculating Ohlson’s O-Score. iii.) Ohlson O-Score excel template (Excel)
This is a template for calculating Ohlson’s O-Score. You can use this template to check the excel formulas for calculating Ohlson’s O-Score. You can also use it to test whether your final formula (for a row) used in your excel spreadsheet is correct.
Software
You all should have access to either a word processor software (Microsoft Word, Apple Pages) and a spreadsheet software (Microsoft Excel, Apple Numbers).
You will also be required to use Tableau to generate the map visualization. The trial version of the software is available for free download for 14 days. Students can also download a student version for one year. You have to upload a scanned copy of your ID card to access this free one-year version.
Main report to be submitted
Your assignment will consist of a number of files. The main file that will be graded will be the report to the general manager. While his email was informal, your report will be formal in tone. The report will address the questions raised by the general manager in his email. You should divide your report into sections and this could be based on the issues he has highlighted in his email. You can begin with an introduction and then by presenting the data and discussing the US states that have the greatest risk of bankruptcy based on their O-Score. You should insert an image (Label as Figure 1) of the map generated from Tableau clearly showing the colour scheme and the names of the states. The name of the Tableau sheet showing the map should be as follows: ID Number_FirstNameSurname e.g., 12345_GlennFinau. An example of how this chart should be presented is shown on the next page:
The next sections can then focus on evaluating and critiquing the general manager’s methodology, Ohlson’s O-Score and the data collected by the graduate. You can also have another section highlighting any further issues or this could be incorporated in the conclusion. Sources used in the assignment should be referenced correctly both in-text e.g., Finau (2021) and in the bibliography. The rubric for the report is attached in the appendix. There is no word limit but it is expected that the report should be between 1,000 to 2,000 words in length.
Other Files for Submissions
While not directly marked, you are expected to upload the following files as proof that you analysed the data yourself:
- Excel files used in the analysis (could be more than 1 depending on the approach used by the student)
- The Tableau workbook used to generate the data visualization map
All files must be uploaded in order for the word report to be graded. If any of these files are submitted after the due date then the assignment will be deemed late and attract a 10% penalty for each business day late. The files will also be checked to ensure that student is the actual author of the files and that the data in the files matches the report. Any discrepancies will be thoroughly investigated and potentially could attract penalties.
Appendix Assignment 2 Rubric (20 marks)
Criteria Exceeds Standard Meets Standard Nearly Meets
Standard Does Not Meet
Standard No Evidence
Points 4 3 2 1 0
Demonstration of data analytics principles
(Use of excel functions and
Tableau) Student has demonstrated a great depth of knowledge and skill in their use of excel and Tableau. Student has demonstrated a good depth of knowledge and skill in their use of excel and Tableau. Student has made a fair attempt and demonstrated some understanding of excel and Tableau however, there were a number of issues which highlights a lack of competency. Student has demonstrated a lack of understanding of data analytics in their n their use of excel and Tableau. There is no evidence to demonstrate learning or understanding of excel or Tableau.
Analysis and interpretation of data Student has demonstrated excellent skills in analyzing the data and interpreting it to the general manager. Student has demonstrated very good skills in analyzing the data and interpreting it to the general manager. Student has made a fair attempt at analyzing and interpreting the data however, there were a number of issues which highlights a lack of competency. Student’s analysis and interpretation is incorrect, unclear and fails to assist the general manager in their decision making. There is no evidence to demonstrate the student has analyzed and interpreted the data.
Evaluation of methodology and Ohlson’s model Student has demonstrated an excellent evaluation of
the manager’s methodology and
Ohlson’s model. All of the student’s evaluations are relevant and even exceed the model solutions. Student has demonstrated a very good evaluation of the manager’s methodology and Ohlson’s model. All of the student’s evaluations are relevant and are aligned to the model solutions. Student has made a fair attempt at evaluating the methodology and Ohlson’s model, however some of the points in the model solution were not raised in the student’s answer. Student’s evaluation of the methodology and Ohlson’s model is superficial, too general and unclear. There is no evidence that the student has evaluated the manager’s methodology and Ohlson’s model.
5
Evaluation of the quality of the data compiled by the graduate Student has demonstrated an excellent evaluation of the quality of the data compiled by the graduate. All of the student’s evaluations are relevant and even exceed the model solutions. Student has demonstrated a very good evaluation of the quality of the data compiled by the graduate. All of the student’s evaluations are relevant and are aligned to the model solutions. Student has made a fair attempt at evaluating the quality of the data compiled by the graduate, however some of the points in the model solution were not raised in the student’s answer. Student’s evaluation of the quality of the data is superficial, too general and unclear. There is no evidence that the student has evaluated the quality of the data.
Structure,
Language and
Referencing The report has an excellent structure that is clear, logical and provides a coherent framework for the story.
The language and tone of the report is appropriate for a formal business report.
There are no or very minor and few grammatical or spelling mistakes in the report.
There is proper referencing used in the report and references are relevant to the ideas to which they support. One of the factors identified in “Exceeds Standard” criteria is not sufficiently met. Two of the factors identified in “Exceeds Standard” criteria is not sufficiently met. Three of the factors identified in “Exceeds Standard” criteria is not sufficiently met. All of the factors identified in “Exceeds Standard” criteria is not sufficiently met.
6
GET ANSWERS / LIVE CHAT