Reference no: EM132360926

ASSIGNMENT SCENARIO

You are an analyst for a company named Business Intelligence Global(BIG), which offers data analytics research service to large organisations and governments.

Although Australian rural businesses make a considerable contribution to the Australian economy, there is very little prior research on the matter. BIG is contracted by Shepparton City Council to conduct research on the performance of Shepparton manufacturing businesses.

The provided dataset includes a random sample of 300 businesses.

You have received an email from the Shepparton City Council that contains specific questions thatyou must answer.

Here is the email from the Shepparton City Council:

To: Data Analyst at BIG

From: Shepparton City Council

Subject: Analysis of the provided data set

In an effort to develop a greater understanding of the entire Shepparton business community ofmanufacturer's like the ones captured in the sample, we would like some information.

Regarding thedata in the attached Excel Spreadsheet, please provide us with the answers to the following questions:

1. Gross Revenue per annum is the most important measure that the council is interested in. Can you provide us with an overall estimate of the average Gross Revenue for all manufacturing businesses of this type?

You will need to produce the relevant tabulated summary statistics and graph(s). Then, you will need to calculate a 95% confidence interval for average Gross Revenue.

2. Are there any differences in the overall proportion of businesses from the four different manufacturing industry sectors? That is, is there any one manufacturing industry sector sampled that proportionally, is represented more so than the others?

You will need to produce the relevant tabulated summary statistics and graph(s). Then, you will need to calculate, compare and contrast, 95% confidence interval estimates for the proportion of businesses in each manufacturing industry sector.

3. Are there any differences in the estimate of the average Gross Revenue between manufacturing Businesses with different numbers of employees?

You will need to create a variable called "Business Size" by converting the numerical variable "Number of Employees" to a categorical grouping measure based on the information provided in the Data Description sheet. Then, you will need to create suitable cross-tabulation(s) and graph{s). Further, you will need to calculate, compare and contrast, 95% confidence interval estimates for the averageGross Revenue for each Business Size.

4. Are there any relationships between the various sales sources (%SalesLocal, %SalesVic, %SalesAust, %SalesOS) and different supply sources (%SuppLocal, %SuppVIC, %SuppAus,%SuppOS)?

You will need to calculate suitable association measures and create relevant graph(s).

5. Assuming that the Gross Revenue for every industry is approximately normally distributed,answer the following questions for each industry separately:

a. What is the probability that Gross Revenue exceeds $40,000,000?

b. What is the probability that sales would be less than $30,000,000?

c. What is the value of Gross Revenue for each industry sector, such that only 10% ofthat industry sector will achieve it annually?
To answer this question, you will need to do the probability calculations for each manufacturingindustry sector separately.

6.

a. It has been suggested that the average Gross Revenue (per annum) of these types of industry sectors (inclusive), is now more than $35million. Does this data confirm this hypothesis?

b. Regarding just the food product manufacturing industry sector: Is there sufficient evidence to conclude that the proportion of industries in this sector has fallen below 25% of allindustries?

To answer these two questions, you will need to consider all industries together (a) and, the food productmanufacturing industry sector separately (b) and conduct appropriate hypothesis testing.

Finally, | would also like an interactive Dashboard for me to explore the performance of ALL businessesin the region.

ASSIGNMENT INSTRUCTIONS

The assignment consists of three parts:

Part 1: Data Analysis
When conducting the analysis, you will apply techniques from descriptive analytics, visualisations, probabilities, hypothesis testing, and confidence interval calculation. Hence, you will use various tables, graphs, and summary measures. When exploring data, we often produce more results than we eventually use in the final report, but by investigating the data from a number of angles, we can develop a much better ‘feel' for the data: a deeper understanding of the data. Always ensure thatyou consider relevant modelling assumptions.

The analysis section you submit should be on Q1 to Q6 sheets of the Excel file. Where possible, it is always useful to produce both numerical and graphical statistical summaries as sometimes, something is revealed in one that is not obvious in the other. Your analysis should be clearly labelled and grouped around each question. Poorly presented, unorganised analysis, or excessive output willbe penalised.

Only use cells B2 to 226 for the results and the rest of the sheets for calculations. Only the informationin these cells RANGE (B2:Z26) will be marked.

Part 2: Email

You are required to reply by email, detailing all essential information and relevant conclusions from your data analysis. You are allowed no more than 2 pages to convey your written conclusions. Remember you should use font size 11 and leave a margin of 2.54 cm.
Please consider the following dot points very carefully.

• Keep the English simple and the explanations succinct. Avoid the use of technical statistical jargon. Your reader will not necessarily understand complicated statistical terms, thus yourtask is to convert your analysis into plain, simple, easy to understand language.
• The email is to be written as a stand-alone document (assume that the Shepparton CityCouncil will only read your email). Thus, you should NOT have any references in the email to your analysisand you should NOT include any charts and tables in your email.
• Use an email format for your reply. That means the email heading (e.g. To:, From:, Subject:) should be included, the recipient should be addressed at the beginning and the signature orname of the sender should be included at the end.
• When composing your reply, make sure that you actually answer the questions asked. Cite(state) the summary statistics of importance without referring to your analysis section. Do NOT copy the questions in the email.
• Sequentially number your answers in both your email and your analysis (1, 2 ... 6) to matchthe Shepparton City Council email.
• Include a simple introduction at the start of the email and a summary/conclusion at the end.
• Marks will be deducted for the use of technical terms, irrelevant material, poor presentation / organisation / formatting and emails that are over two pages long or copy questions in theemail.

Part 3: Interactive Dashboard
The minimum requirement is a neat, functional, interactive dashboard. It is expected that the dashboard include up to 5 interactive components. The Microsoft Excel file should contain a separatesheet for the interactive Dashboard.

The following questions will help guide you in designing an interactive dashboard.

1. What are the most appropriate visualisations for the dashboard?

2. What about choices of colour?

3. How can you make the dashboard interactive?

Attachment:- Assignment Spreadsheets.rar