Background The assignment requires you to analyse a given data set, interpret and draw conclusions from your analysis, and then convey your conclusions in a written email. Scenario You are Vic Rankin, and you work as an analyst for a company called Business Intelligence (BI). BI are data analytics research service consultants that provide data modelling solutions to large organisations and governments, as well as supporting various niche research projects across a variety of industry sectors. Australian rural communities make a considerable contribution to the Australian economy yet very little research has been done on the specific business communities that support the rural sector. For example, little is known about the market place confidence and expectation of businesses that exist in the townships and small rural cities that support our urban communities. BI has been contracted because some rural municipal authorities have identified a need to focus expenditure and related activities to assist in business growth. One such municipal authority is the Ballarat City Council. In an effort to understand the marketplace confidence of the Ballarat businesses that support their local community, BI has taken a random sample of 150 businesses. As well as asking them for useful demographic characteristics, BI have asked questions about trading patterns, business confidence and business expectations. You have received an email from the Ballarat City Council that contains specific questions that you must answer. Email from the Ballarat City Council To: Vic Rankin From: Ballarat City Council Subject: Analysis of Marketplace confidence Dear Vic, In an effort to begin to develop a basic understanding of the Ballarat business community we would like some summary information. Regarding the data you recently collected on business confidence and expectation, please provide me with the answers to the following questions. Your responses will assist us in determining where the Councils’ resources might best be directed. 1. Regarding the age and number of employees of businesses in Ballarat. a. Can you provide an overall summary of the age of businesses? b. Can you also provide a summary on the number of people that these businesses employ? c. There are 5,750 registered businesses in the Ballarat region. Please provide an estimate of the total number of employees for all registered businesses combined. 2. Does the age of a business appear to have any effect on the number of people employed? 3. We know that the current economic climate is not favourable and so we are very interested to learn if this is reflected in the attitudes of local businesses. a. Can you please summarise the level of confidence in the local region's business environment for the next financial year? b. Further, can you please summarise whether or not businesses have a growth strategy? 4. Is there any evidence of a relationship between confidence in the local region's business environment for the next financial year and having a growth strategy? 5. Lastly, we are interested to see if: a. The amount spent on training per person each year is affected by the number of employees in the business. b. The amount a manager earns is affected by the manager’s age. I look forward to your response. Sincerely, Ballarat City Council The assignment consists of two parts: Part 1: Data Analysis In order to prepare a reply to the Ballarat City Council’s email, you will need to examine and analyse the dataset, BusinessConfidenceDATA_T2_2016.xlsx, thoroughly. When conducting the analysis, you will apply techniques from descriptive analytics and visualisations, and hence will use various tables, graphs and summary measures. Sometimes we refer to this type of analysis as ‘exploratory data analysis’. When exploring data, we always produce more results than we eventually use in the report, but by investigating the data thoroughly from a number of angles, we develop a much better ‘feel’ for the data: a deeper understanding of the data. For ease of analysis, most variables in the data set are presented alphanumerically in adjacent columns as both a number and text. You should use the variable most suited to your analysis. The variable codes and questions asked are summarised in the spread sheet “variables” and the data matrix is presented in the spreadsheet “Data Matrix”. The Ballarat City Council has asked for a number of tasks to be done. The following guidelines for each question should be followed: 1. A summary of the age and number of employees of businesses. a. Here you should only analyse the “HowLongTrading” variable by itself. Produce relevant statistical output (summary measures, tables, charts...) to summarise the important features of this variable. b. Here you should only analyse the “ACTUALNumberEmployees” variable by itself. Produce relevant statistical output (summary measures, tables, charts...) to summarise the important features of this variable. 2. Does the age of a business affect how many people are employed there? Here you should compare and contrast the number of employees for each of the four groups of “HowLongTrading”. 3. Growth strategy and a confidence in the business environment over the next financial year. a. and b. You should summarise each of the variables separately. 4. Is there evidence of a relationship between confidence in the local region's business environment for the next financial year and having a growth strategy? You will use the same variables you used in 3 above. Investigate the proportions of respondents indicating the various levels of confidence for businesses that do have a growth strategy and businesses that don’t have a growth strategy. 5. Various relationships. Here you will need to use an appropriate approach that allows you to investigate the relationships mentioned. General guidelines: • The analytical techniques you will use will depend on the type of data you have (for example, numerical or categorical, one variable or more than one variable). • The analysis section you submit should be no more than 8 pages of computer output (ie. output that you have copied into your Word document from Microsoft Excel). When you conduct your analysis, you will produce much more than this initially, but you should trim it down to only show the most relevant results in your maximum of 8 pages. Within the Word document, your analysis should be presented in the same sequence that the questions have been asked by the Council, and be clearly labelled and grouped around each question. Poorly presented or unorganised analysis or excessive output (more than 8 pages) will be penalised. • Save your computer analysis frequently. Part 2: Email You are required to reply by email, detailing essential information and conclusions from your data analysis. You are allowed no more than two pages to convey your written conclusions. Keep the English simple and the explanations succinct. Avoid the use of technical statistical jargon. Your reader will not necessarily understand even simple statistical terms, thus your task is to convert your analysis into plain, understandable language. General instructions: • The email is to be written as a standâ€alone document (assume that the Council will only read your email). Thus, you should not have any references in the email to your analysis, nor should you include any charts and tables within it. • Use an email format for your reply. That means the email heading (eg. To:, From:, Subject:) should be included, the recipient should be addressed at the beginning and the signature or name of the sender should be included at the end. • When composing your reply, make sure that you actually answer the questions asked. • Sequentially number your answers in both your email and your analysis (1, 2 … 5) to match the Council’s email. • Include an 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. When you have completed the email, it is a useful exercise to leave it for a day, return to it and reâ€read as if you knew nothing about the analysis. Does it flow easily? Does it make sense? Can someone without prior knowledge follow your written conclusions? Often on reâ€reading, you become aware that you have made some points in a clumsy manner, and you find that you can reâ€phrase them much more clearly. Submission: In summary, your submission will comprise of ONE document only (a Microsoft WORD file). To get the Microsoft Excel output into Microsoft Word, you need to copy your relevant analysis output and paste it with the paste special option (paste as a picture). Your single submission document should be presented in the following order: (1) the written email, comprising of no more than two pages, then (2) your analysis, which will be no more than 8 pages of output.