Accounting Information Systems

Introduction
This project requires students to develop data analytic and visualisation skills via designing a
dashboard to report to management. Dashboards are a key tool for presenting and reporting
accounting and other data from a firm’s AIS in a format that is timely and informative for technical
and non-technical managers. The demand for graduates with data analytics skills has exploded, while
the tools and techniques are continuing to evolve and change at a rapid pace. This project is an
opportunity to develop your data analytic skills using a variety of tools including Excel and Power BI.
In this project, you will be using Power BI to analyse the sales and cost/margin transactions for an
insurance company. You will first have to find and correct errors in the data set using excel – applying
data cleaning skills. Then using PowerBI, you will sort the data, join tables, format data, filter data,
create calculated fields, create charts, and other visualizations, and will draw conclusions based on
these results which you then present to management as a ‘Performance’ dashboard.
General Learning Objectives
1. Clean the data in a data set
2. Analyse sales & margin trends and other issues
3. Interpret findings – the evidence in the data
4. Design, execute and present a management dashboard to convey your analysis and findings.
Assessment Tasks
Your assessment is broken into two components:
1. Develop a dashboard based on the materials provided to report to management (20%); and
2. Provide a digital presentation introducing your dashboard to management (5%).
2 | P a g e
Riskie Insurance Corp Sales Data
You have been hired by Riskie Insurance Corp (RIC) to improve and modernize their executive
reporting with an emphasis on data analytics and visualization. You are supplied with a data set based
on real-life data from a US based national insurance company. The data set contains more than 65,000
insurance sales records from 2021. All data and names have been anonymized to preserve privacy.
Your also have some summary prior year comparative data.
Steps & Tasks to Address
There are several steps or tasks required to take the raw data file and prepare and analyze the data
leading up to presenting the key results via a dashboard to management. In analyzing the sales records
in the data set you will need to:
1. Clean the data: There are some typographical errors in the data set in the Region
and Insurance Type fields. Find and correct these errors. You can find and fix these using your
excel skills if you like.
2. Calculate the variable cost and contribution margin for each policy sold – do this computation
in PowerBI.
3. Analyze and present the total the sales revenue, variable cost, and contribution margin
for each Insurance Type in an appropriate manner. Highlight or flag in your analysis and
dashboard the following items:
a. Which Insurance Type had the highest total contribution margin?
b. Which Insurance Type had the lowest total contribution margin?
c. How many insurance policies were sold in each Insurance Type?
d. What is the average contribution margin per policy in each Insurance Type?
e. Other issues or key trends you identify?
Contribution margin is the dollar contribution per
sale and is computed as the selling price per policy
minus the variable cost per unit. “Contribution”
represents the portion of sales revenue that is not
consumed by variable costs and so contributes to
the coverage of fixed costs.
Variable cost is that portion of the sales dollar per
policy that covers the variable costs of making the
sale – you are supplied the variable cost percent for
each policy type so:
Variable Cost per policy = VC% x Policy $Sale.
3 | P a g e
Calculate the contribution margin ratio for each Insurance Type & rank from the highest to the
lowest contribution margin ratio. Do these rankings agree with the rankings you found in
Requirement 3? Should these two rankings always be the same? Explain. Present this analysis on
your dashboard in any many you deem appropriate. Which will present better to management $
margins or margin % or should you convey both – if so why and how?
5. Calculate the contribution margin ratio for each state (see Week 10 Slides on how to create a
PowerBI formula for this). Rank the states from the highest to the lowest contribution margin
ratio. Which state(s) had a contribution margin ratio greater than 75%? Explore the data – is
there anything else you can identify at the state level that is an interesting and important
insight/relationship in the data?
6. Within each region, what was the most profitable state in the most recent year, as measured
by the contribution margin ratio? The least most profitable state in each region? How have
they performed vs the prior year? Which areas are improving, and which are not? Present this
analysis in an appropriate way that allows management to drill down from region to state
and/or using an appropriate visualization.
7. The main challenge from management: Your manager has challenged you to move past the
basic analysis above and find issues and insights in the data that management should consider.
Remember managers wants reports to cover three categories of issues about the operations:
(a) good performance to celebrate; (b) “normal business” to tick off (OK is still good); and (c)
problems areas/issues to think about addressing.
The address the management challenge, analyze all the information you have gathered or
created above. Are there any other insights you can provide via visualizations and analysis for
management? For instance, are there any time dynamics in the data? Consider extending your
analysis to identify areas for growth or pruning (dropping states/products/employees?), cost
control, pricing and promotion, sales performance or HR action, trends over time or by
subcategory or by salesperson, or other issues for management attention. What trends
or takeaways do you see? Any strategy implications that management should consider?
NOTE: We will have a lab in Week 10 that provides a step-by-step tutorial
video to guide you through the Power BI portions of the case analysis
using a small sample data set.
Data Dictionary
The data set includes the following data fields:
• Region: This field contains the region in which the insurance was sold. There are six US
regions: Midwest, New England, North Central, Northeast, Southeast, and West.
Contribution margin ratio is the $ contribution margin
divided by $Sales for each policy. The contribution
margin ratio = (Sales – Variable Costs) ÷ Sales
4 | P a g e
• State: This field contains the state in which the insurance policy applies. The data is from
sales to the 48 states in continental US and the District of Columbia. (RIC Insurance does not
offer insurance in the states of Alaska and Hawaii.)
• Salesperson: This field contains the name of the salesperson who sold the policy.
• Insurance Type: This field contains the type of insurance policy.
• State Type: This field is a combination of the State and Insurance Type fields.
• Sales: This field contains the selling price of the insurance policy.
• Date of Sale: This field contains the date that the policy was sold.
• Invoice No: This field contains the invoice number.
• Country: This field contains the country in which the policy was sold. RIC Insurance only sells
policies in the US.
There is a separate data table (excel sheet) for variable cost percentages for each policy type. The
fields in this sheet are as follows:
• State Type: This field is a combination of the State and Insurance Type fields.
• Variable Cost Percent Actual: This is the variable cost of each policy for the current year.
• Variable Cost Percent Budget: This is the variable cost by policy type budgeted for the
current year and is based on last year’s actual cost ratio.
Finally, there is a sheet with summary information for sales performance for prior years called Last
Year. This report summarizes the sales performance by type, salesperson, and state.
Assessment Submission Items
1. Dashboard (20%)
To address all the tasks above (including the data
cleaning in excel) you are required to develop a
dashboard in PowerBI with appropriate analysis and
visualizations and a management memo. There is no
set answer as to how to present the analysis and
dashboard – this is a free-form activity. The idea here is
that you spend time applying the core dashboard principles (see slide) using the PowerBI desktop
tool. You will be graded both on answer the core questions, addressing the dashboard principles
and the sales and performance insights you develop.
In the past some students have needed to include a management memo to address the questions
in the task sheet as part of their submission. If you adopt this approach this memo must be short.
However, I recommend an alternative approach that you incorporate any textual
summary/explanation/answer into the dashboard rather than add an “answer” document
(memo). A well-designed dashboard should not need an additional supporting document
explanation (over and above the oral video). Ideally the dashboard should be self-contained. The
challenge is to design your dashboard to communicate with management at the same time as
“answer” the questions for grading.
5 | P a g e
2. Digital Presentation (5%)
Given we cannot all present in class, but also to replicate management communication processes in large disperse organisations, you are required to provide a digital presentation introducing your dashboard and its functionality to management which includes highlighting any key dynamic functions as well as the core insights. Limit the presentation to 5-10 min absolute maximum – remember management are busy and have short attention spans so focus on quality not quantity. Also remember a good design should be self-evident and easy to see the insights. Use whatever technology you have available to produce your presentation. A mobile device (smart phone) video of you and your dashboard screen is probably the lowest common denominator but still effective communication. The content matters not the production values of the video but do look “professional”. You can use screen capture technology if you have it, but this is not expected.
Consultations with Management
To complete the dashboard challenge, management expects that you will need present drafts for feedback. Your instructors are the management team, and we will challenge you on the communication effectiveness of your design. This formative feedback process simulates the work environment, and it is critical to your success that you engage with management and that you do this early. You will not have time the reflect and adapt to the feedback if you try to do this at the due date.
Uploading
Upload your SIDno_Dashboard.pbix file, excel (data cleaning) and management memo with at least your ID number in the filename and same for the video file to the appropriate areas on iLearn Assessment Tab.