Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

BUSA20001

Data Wrangling and Visualisation

Muesli Pty Ltd - Growth Analysis

2023 S2

Assignment 2

Overview

This assignment is worth 20% of the overall subject grade.

This assignment is due on Friday, September 22, 2023 at 11:59 PM.

The assignment must be submitted via the University of Melbourne LMS.

One person must upload one single ZIP or RAR file on behalf of the group, containing:

• One Tableau workbook. To score maximum marks, a single file must contain all reports, data sources and visualisations.

• One PDF file. This is the final version of your 2-page report, that must be created directly from Tableau.

• You may optionally submit sketches of your report designs, with explanatory notes. In the case where you are unable to achieve the full implementation of your design in Tableau, you may score additional marks for the design aspect based on your sketches. Note however, that you must describe colour assignments, calculations of data values in charts and KPIs where not obvious, and any filtering of data.

It is to be completed in groups of 2-4 students. You will be assessed as a group. All group members are expected to contribute equally to your group’s effort. Each group is responsible for managing the distribution of the workload for completing the project.

Learning Outcomes

This assignment will allow you to develop your ability to analyse data and present information to support operational and strategic decision making using a variety of technologies such as databases, programming languages and visualisation software.

More specifically:

• Communicate, using a mix of visualizations, a coherent, well-sequenced summary of a data analysis to inform an audience of the key findings of that analysis.

• Interpret and write a summary of the noteworthy patterns, trends and exceptions identified in a set of data.

• Create simple line, bar and column charts to visually display data.

• Select which chart types are relevant to each type of analytical inquiry.

• Use data visualizations to compare and contrast aggregate measures between subsets of data, or over time, in order to identify patterns, trends and exceptions present in data.

Preparation

Refer to the briefing document for important background information about Muesli Pty Ltd.

Download and install Tableau Desktop software. (See the LMS for instructions)

Obtain and enter a license key to register Tableau for educational use.

Objective

The CEO of Muesli Pty Ltd has asked your team to assess the company’s growth and short-term viability.

With the company being less than a year old, the CEO wants to get a perspective on sales and profit growth within the company.

Monthly Report

They’ve asked you to develop a 2-page report analysing revenue and contribution margin, and key factors affecting them. The scope of your report must primarily focus on the most recent 8 weeks of operations, up to and including the week ending Friday, September 1 st).

The company’s fixed costs cannot be changed in the short term, and production overheads per box of cereal do not vary for different products. Therefore, these are not of interest for this analysis. Profitability is driven by contribution margins, specifically Revenue minus Raw Material Cost. Since pricing is product and channel specific, and raw material costs product specific, you should have those two perspectives primarily in mind. You have been provided with a file containing raw material costs for each product.

Given the nature of the industry, the CEO is quite aware that available inventory could very well be a limiting factor for sales. Aside from some issues earlier in the year with getting raw materials on time, the main factory has been operating smoothly at, or near, maximum capacity. Since fulfillment is from the various warehouses, product and location are the main perspectives that should be considered when examining sales and inventory quantities.

The company didn’t do much research into preferences within the market and broadly chose 6 out of the possible 12 products that could be manufactured. There are 10 manufacturers in the industry, all of which are small enterprises, and very few produce all 12 products. There is an industry association, and a data sharing agreement among its members. Each week, each member uploads aggregate weekly sales data, which the association combines and distributes back to the members. You have been provided with a file containing this data. Note that there is a time lag to collect and distribute this information – you will not have data as recent as what is available in the company’s own database.

Analysis and Recommendation

You should benchmark the company’s performance against opportunity, as best as you can, given what data is available to you.

However, the key question on the CEO’s mind is, is the company prioritising the best products? Based on the descriptive and diagnostic evidence you present in your report, what priority should be given to each of the 12 products to best achieve revenue and profit growth?

The tone of your analysis and recommendation is informal - you’re offering guidance. However, you must express clear opinions supported by reasoned conclusions based on your interpretation of the data. The CEO will be unhappy with wild guesses, unexplained hunches, or generic business common sense. Be specific.

Technical Considerations

Once you have developed this report, the CEO will be wanting you to update and resend it every month or so. You will therefore want everything to be as automated as possible, so that you can quickly update your report with the most recent data.

Each week after updating the data and reviewing the situation as displayed within the charts included in your report, you would focus on reviewing your analysis, estimates and recommendations considering the new situation. It should take no more than an hour or so to produce each new report, once you’ve produced the first one. With each data update, the contents of your report should make it easy to also assess whether the operations and environment of the business has changed significantly enough to raise concerns or consider new opportunities.

To that aim, all constraints such as the report date and analysis period should be parameterized and dynamically updated. All computations must be fully automated based on those parameters. You are expected to test that these all function correctly (use a variety of past Friday dates, e.g., August 18 or 25).

You must use Tableau, directly connected to the source data, to produce your 2-page report.

You may NOT use any other tool (e.g., Excel) to edit data, perform calculations, create new data files, or as the source of data copy/pasted into Tableau.

You may NOT use any other tool to create visualisations.

You may NOT use any other tool (e.g., Word or PowerPoint) to layout and produce the PDF of your report.

Outside of that, you are free to build your solution using any combination of Tableau and SQL provided all SQL queries are included in a Tableau Data Source as Custom SQL, and therefore run by Tableau against the database. It’s fine to develop and test the SQL using Data Studio, then paste the SQL query into the data source; do not run the SQL and paste the data into Tableau.

If you wish to include rough estimates or predictions in your report, these do not have to be calculated directly in your report. You can perform these elsewhere and include them as plain text in the relevant paragraph. However, these should be on the sidelines in support of your main analysis, and not the basis for your main recommendations.

Marking Guide

Simplicity of End-to-End Solution

• There is a high degree of automation, with minimal manual processing or intervention required (see technical considerations).

• The implementation is simple and tidy. The number of files is minimal. The number of data sources is reasonable, and do not contain overly redundant elements. Each one should have a clear scope or purpose as identified by its name. Dimensions, measures, and parameters are organised and well named. Worksheet tabs are well organised and well named. 20%

Summary Report – Layout, Charts and Tables

• The content is relevant to the purpose of the report, as outlined in the brief.

• The sequence and flow of the report is logical and easy to digest, progressing in an orderly way that invites interest rather than mechanical progression.

• Placement of text and charts on individual pages assists the reader to assimilate the findings. A single readthrough should be all that is required to understand most of the key messages.

• Charts highlight findings or conclusions, presenting them in a readily perceivable way; and aren’t overloaded trying to support multiple purposes or messages.

• Accurate, free of computational, aggregational or data importation / filtering errors.

• Proportions are accurate and well chosen; axis intervals are equidistant.

• 6 to 12-word descriptive titles are well chosen to convey key takeaways. Subtitles provide additional, useful, information where necessary to clarify the content of charts.

• Font choices are similar, and text is hierarchical, horizontal and readable (size and contrast).

• Data are labelled directly instead of with colour legends, and intentionally ordered.

• Look and feel is consistent. The colour scheme is intentional and chosen to convey semantic meaning, highlight key patterns or emphasize key elements to focus attention on specific areas. Colour choices do not significantly impact the ability to understand content when printed in black and white, or by people with colour blindness.

• Free from unneeded decoration (including decorative colours); redundant or excessive data point labels; over-precision; distracting grid lines, tick marks or axis lines. 60%

Analysis & Recommendation

• The analysis and recommendations are clear, well-reasoned and easy to associate with the accompanying charts and tables.

• The report text (including titles) is a fair summation or conclusion of the accompanying charts and tables - there are no misinterpretations.

• The text is professionally written, and mostly free from spelling mistakes and grammatical errors. Sentences are well-constructed and of reasonable length. 20%