Home Random Page



Table 16 Data on the volume of sales


Month Sales (in million tenge) Deviation
1 quarter 4 quarter (+,-) %

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


  Months Roadmap fact released Percent Complete Plan Achieved% of actually released for the year
  Total for the year        
Maximum for the year        
Minimum for the year        
In an average year        

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


Year Price Income Gains Net income from sales,%
10,20 0,57 0,31 14,12%
11,81 0,65 0,32  
9,78 0,81 0,34  
6,86 0,89 0,36  
9,01 0,79 0,37  
10,75 0,99 0,41  
9,57 1,09 0,47  


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


Name Balance at the beginning of the month, L Parish, L Fuel, L Total, L
Diesel fuel  
Diesel oil  


Table 20 Analysis of supply


Month Contract number Loan amount ( ) Actually delivered ( ) Deviation (+,-)


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: 97

<== previous page | next page ==>
Laboratory work 4. | Laboratory work 5.
doclecture.net - lectures - 2014-2017 year. (0.007 sec.)