Once the tables and the graphs are done cut and paste into WC and in a brief paragraph interpret your results. Objectives: On completion of this Practical you should be familiar and hopefully be competent with the facilities of EXCEL to summarise and display data in tables, charts using the descriptive statistics functions of EXCEL. Use the business report format in presenting your work. 3. Frequency Table Construction and Calculating Descriptive Statistics. In this second part of the prac you are provided with raw data the number of days it has taken to audit firms. Please note this not related to what you have done above. Hence in your rep present what you are required to do below under a separate title 1. Data: Refer to Excel Spreadsheet - Prac 1 Data 2. Presenting data in tables and graphs. Table 3 presents data on number of days required to comple year-end audits for a sample of 20 clients of a small pub accounting firm. Based on the data provided find: In the first part of the Prac we are analysing the number of orders received from online customers in Capetown. a) The number of firms audited. Use =count (highlight block). b) The maximum number of days used in auditing a firm. =max (bloc c) The minimum number of days used in auditing a firm. =min (block d) The average number of days used in auditing a firm. =average (blo e) The median number of days =median (block) f) The variance and standard deviation =var (block), =stdev (block) In Table 1, provided in the data set, add a third column with a column title Percentage. Calçulate the percentage of online orders for the southern suburbs by creating a formula in the first row of your new column (the formula: start with = then highlight the cell containing 156 divided by (/) highlight the cell that contains 600 multiply (*) by 100 then enter. To fix 600 as a divisor edit the formula by entering the $ sign in-front of the row number in your formula). Generate the other percentages by simply copying the formula down the column Organise the above in a small table titled Descriptive Statistics: Au time ("Long Way") Now display the percentage share of reorders by suburbs using (a) a column chart and (b) a pie chart. Place the graphs in the working space provided below the table. space insert more rows. (Note: Excel can do the above in one go when you invoke descriptive statistics option. To better familiarise yourself w the software first do the above step by step (the long way) a then using the_descriptive statistics option (From the ribb choose: Data/Data Analysis and then choose Descript Statistics from the menu and follow the dialog box). Once table is generated, "clean it up" and give it a title: Descripti Statistics: Audit Time (“Automatic" Method) If you need more [Some help for those not familiar with Excel: Step 1. Highlight the column of suburbs (do not include the title of the column). While pressing the Ctrl key highlight the column of Percentages (again do not include the column title). Step 2 From the ribbon select insert Step3. Under Insert from the chart options select column, then from the menu that drops select the first chart. Step4. (At this stage you should see your column chart with tiles and axis labels). Delete the legend you don't need it for this graph. To type in the chart tifle while under insert select Layout, now choose under the Labels ribbon Chart Title, from the menu that drops choose Above Chart. Then an appropriate title in the title box. Step5: To label your axis again from the Labels ribbon choose Axis Titles. From the menu that drops choose Primary Horizontal Axis then Title Below Axis to label your X or horizontal axis. Type Suburbs as your axis tile. Follow a similar procedure to låbel your Y axis. Type percentages as your Y axis label. g) Organise the data in a frequency table with five classes, 10 14; 15 - 19; 20 - 24; 25 - 29; 30 - 34. Start by typing in the abo classes in a column. Note: So that EXCEL will recognise wl you are typing is text and not a mathematical operation, start w an apostrophe (') and type the number. Step 1. Select (highlight) cells D2:D6 (i.e. The cells where y want the frequencies to appear. The cell addressees could different in your case) Step 2. Type the following formula without moving your curs once you have highlighted your cells: =FRÉQUENCY (A2:A21,{14,19,24,29,34}) Step 3. Press CTRL+SHIFT+ENTER and the array formula w be 'entered into each of the cells D2:D6. (Note: A2:A21 is column which contains your data. The numbers in the braces are the upper boundaries of your classes. Hence in D2 Excel v put the number of firms audited in 14 days or less, in D3 it w show the number of firms audited in 19 days or less but have tak more than 14 days etc.) Now you are ready to play with scale, colour, 3D etc. to make your chart attractive. For the Pie chart start as above. Then at Step3 choose pie chart. From the chart layout options choose the first. Type your title and you are done. Again experiment with scale and the other fancy options in EXCEL to make your graph look "nice" Table 2 in the data set provided, presents a further breakdown of the data shown in Table 1. Copy Table 2 in its entirety in the space you _provided. Delete àll the cell contents in the copied table. In the copied table show the data above in %. (Generate an appropriate formula in the first cell anchor the cell that contains 600 with dollar signs ($) before the column letter and before the row number (Remember everything is divided by 600). Now use the "magic" of copy for the entire table). Cut and paste your results in an appropriately constructed tab Your frequency table should have an appropriate title, and columns should also be appropriately titled. 3.2 Histogram: Present your results in the Frequency Table above in a Histogra Follow the steps outlined above for the construction of a bar cha Once you finish do the following to convert your "bar" chart to Histoğram (in a Histogram the bas must contiguous). Step 1. Right click on any rectangle (bar) in the column chart produce a list of options Step 2. Select the Format Data Series option Step 3. When the Format Data Series dialog box appears: Reduce the gap width to zero. Check that your Histogram is properly scaled, has an appropria title and the axes are labelled. Show the information in the table you just constructed in: (a) a stacked column chart and (b) a multiple bar chart (also known as clustered column chart) [Follow the steps given above; in step 1 you need to highlight the column titles so that your legend would reflect your two variables) 3.3. Interpretation Again cut and paste your tables and Histogram into word and in a brief paragraph tell the "story".