Short Analysis about Business Analytics

This week's tutorial provides an overview of Excel, how to set up a data set, and how to structure your spreadsheet when performing data analysis. Note that Microsoft Excel is a powerful general purpose spreadsheet. It is widely used throughout the business world across all industries. However, its statistical functionality is rather limited and only covers basic statistical techniques.

This is sufficient for introductory business analytics subjects like MISS 71 . For more advanced techniques, analysts use specialist software such as AS, SPAS or Maintain. The instructions given in all the tutorials are for Excel 2010. Students working with different versions of Excel should still be able to follow the Instructions. Specifically the alms of this tutorial are to : understand how to organism data In Excel Know how to structure the different parts of a statistical analysis (INPUT, PROCESS,Apply absolute and relative cell referencing In Excel use basic excel function Copy formulas from one cell to another or to a range of cells Practice drawing graphs in Excel Scenario Cinnabar is a manufacturing company which employees over 3,000 people.

Management is concerned about the wide variation in productivity between employees and whether employee Job satisfaction and their sense of Job security plays any part in this problem.The organization also has staff planning issues that need addressing, including planning to replace workers who retire, undertaking a acquirement drive for new employees and providing adequate promotion opportunities within the company. The company has conducted a survey of 48 of their staff, collecting data on these and related Issues. Tutorial 1 Page 1 Open the data file Download the file Cinnabar_10th . XSL from Cloudlike.

Save It to your hard drive (H: If working from the Decking computer labs) and give It a suitable name such as 10th . XSL. To run the program, select Starts All Programs I Microsoft Office I Excel.At the top of Excel (Microsoft Office Ribbon), click on File tab, select . Understanding the DATA SET To perform any analysis in Excel, the data must be organized as a data set or data matrix.

See Figure 1 below or select the Crowbar-Data worksheet tab. Note the following: Rows: Each row in a data set represents an entity of interest. In this case an employee. The row holds the complete record for a single employee.

Columns: Each column represents one field or one variable. For example, the last column holds the weekly salary for all employees in the data set.Headings: To identify each arable, it is usual to place a heading at the top of each column. Sometimes row headers are also included to identify individual records. Data: The actual data is located in the body of the table Headings Records Figure 1: Part of the Cinnabar data set Data Variables Answer the following questions for the Cinnabar data set: What is an employee? What is 'Deduces? The part of the data set shown in Figure 1 shows 10 variables. True or False? 3.

STRUCTURING your spreadsheet Page 2 At the bottom of your spreadsheet you will notice five (5) worksheet tabs.See Figure 2 below. The first two provide background details and explanations about the data set. The third contains the actual data set while the last two are for performing data analysis. When performing any data analysis, you follow the basic steps: INPUT PROCESS OUTPUT INPUT is the actual data set. PROCESS includes manipulating the data and possible to include all three steps on the one worksheet.

However, this can easily lead to mistakes and confusing or unrecognized results. It is generally better to keep the three steps separate.In the Cinnabar spreadsheet, the Cinnabar-Data worksheet contains the data set (INPUT). Generally, no calculations or changes should be made to this worksheet once the data has been entered.

All formulas, manipulation of data, summaries, and other intermediate calculations (PROCESS) should be done in the Working worksheet. The Results worksheet holds the final output such as graphs, tables and other key results. Figure 2: Worksheets in the Cinnabar spreadsheet 4. Basic CALCULATIONS and FORMULA In this step, our aim is to practice working with formulas and functions in Excel.At this stage you are not expected to fully understand the data analysis output.

Select the Working worksheet tab and enter the following headings. In cell CO, enter the function: In cell CO, enter the function: =MAX('Cinnabar-Data'! J:JOY) In cell CO, enter the function: =Mil('Cinnabar- Data'! J:JOY) In cell CO, enter the formula: =CO - CO Repeat for the other three (3) variables in columns D to F. Page 3 Answer the following questions: What is the average age of the 48 Cinnabar employees in the sample? What is the largest weekly salary? What is the range of ages of Cinnabar employees? What is the average department? Interpret this value. 6. 7.

SAVE your work Save your work by pressing CTR+S or selecting File tab I Save from the top of Excel. You should save your work regularly, every 10 - 1 5 minutes. Creating a GRAPH Copy the following table in to the Working worksheet. Highlight all the cells in the table (from 812 to CLC 5). Select the Insert tab at the top of Excel. Select Column chart and then under 2-D Column choose the first chart (called a Clustered Column).

See Figure 3. Figure 3: Inserting a graph graph, save your work. Choose the most relevant results you have on the Working worksheet and copy them to the Results worksheet. Note when copying you should do Paste values for any numerical or tabular result and do Paste as Picture for charts (see Figure 4 in the next page). This will 'untie' your final results from the data set and intermediate calculations.

This is important as any subsequent changes to your workings will not affect your final results. Page 4 Figure 4: Copy and pasting results Page 5