Table 16 – Data on the volume of sales

JOB 2.

Create a table on the model of Table 17.

Enter in cell A1 table header. Fill in the table source data

2. Select cells A2:F2 format set word wrap, centered horizontally and vertically (Format-Cells, select the Alignment tab)

Enter a name for the table columns to fill the columns A, B mode use AutoFill

4. Cells C16, D16 calculate the results of using the button Autosum

5. Enter in cell E3 formula to calculate %: = D3/C3 and fill the cells below the formula

6. Format the cell range E3:E14, presenting the data in percentage format (button %) with two fractional digits (Increase Decimal button)

7. In the cell range F3:F14, calculate for each month's share of the annual release: = D3/$D$16

8. Copy formula, format % range format with two digits of the fractional part

9. In cell D16, D17, D18, D19, enter the formula: = SUM (D3:D14), =MAX (D3:D14), = MIN(D3:D14), =AVERAGE (D3:D14)

10. Last copy the formula in cell range E17:E19

Set the format of integers.

Table 17 – Production figures

Table Format «Indicators of production»

1. Arrange the table heading «Indicators of production» in the middle of the table, change the font size 12, font – bold.

2. Circle table frames

3. Perform conditional formatting to a range E3: E14 to isolate those cells that have a value less than 100% as follows:

• select the range E3:E14, choose Format-Conditional Formatting (Условное форматирование)

• In the Conditional Formatting dialog box, select the field in the second «less» in the third field, enter 100%

• Click Format, Font tab, select the font red color and bold

4. Values in column F align to the center

Graphical representation of data tables

1. Construct a histogram to compare the values of planned and actual output by months later:

• Enter the chart title and axis titles

• Place a chart on an existing worksheet

2. Construct a pie chart to display the values of actual output by months as a percentage of the annual issue:

• Select the blocks B3:B14 and D3:D14 (while holding CTRL)

• call the Chart Wizard, select the type of circular, exploded cut circular form

• connect the data labels, select the switch and share category

• Place a chart on an existing worksheet

3. Set the following data formats signature: add the percentage format to two fractional digits and change the font size

4. Construct a graph showing the process of changing the values of the actual release by month:

• Highlight the blocks B3:B14 and D3:D14 (while holding down CTRL)

• Call the Chart Wizard, select the type of Graph view with markers (second row, first appearance)

• Define the chart title and the name actually released for axes Months Number

• Place a chart on a separate sheet.

JOB 3.

Stock returns. Prepare a report of the «Pyramid» in a table (Table 18):

1. In cell A1 enter the table name, and then enter the raw data into a table

2. Fifth column of the table contains the formula calculation: for the current year, the net profit is determined by the formula (Share price in the current year - last year Price + profit last year)/Price in the past year. For this in cell E4, input = (B4-B3+C3)/B3. Copy the formula to other cells.

3. Format the cell range E3:E9, presenting the data in percentage format (button %) with two fractional digits (Increase Decimal button)

4. Complete the table summary rows to calculate the average, minimum and maximum values for each of the indicators over 6 years.

5. Construct a histogram (flat or three-dimensional) for the columns Profit on shares and Dividends

6. Construct a pie chart for the indicator Dividends

7. Change the font size of all elements constructed diagrams

8. Reposition the chart «on the existing sheet» to «separate»

9. Sheet name «Task_3»

10. Change the names of the sheets, on which the chart

Table 18 – Share price and profit on them in JSC «Pyramid»

JOB 4.

Fill in the cell calculations and draw diagrams in figures in the following tables (Table 19 and Table 20). To calculate the values in the column «Total» use AutoSum, and the column "Deviation" is the difference between the actual and planned performance. Remember that charting cannot use the original values and the amount of time.

Table 19 – Report

Table 20 – Analysis of supply

Note: When plotting values for this table note that the column «Contract Number» are supporting information, and for plotting can not be used.

Date: 2015-12-24; view: 2131