Processing numerical information, editing formulas and creating diagrams in spreadsheet editors

Laboratory work  №7

 Processing numerical information, editing formulas and creating diagrams in spreadsheet editors.

Goal of research:

• Familiarize yourself with the basic features of Excel.
• Learn the basic techniques of working with tables.
• Learn how to copy, move fragments of a document. Call and save files.
• Learn how to create a simple document in EXCEL using data entry, copying, formatting, and editing.

 Task 1

1. Start  EXCEL.
2. Use the mouse to change the width of the columns: A - 4; B - 40. Using the main menu command Format-Column-Width,  set the following column width: C:G - 12.
3. On the block of cells F 3: G3,  set the format of multi-line text (command Format-Cell-Alignment,  Wrap by words mode).
4. Input of initial data and formulas:

• In cell A1, enter the name of the  table.
• In cells A2, B2, C2, D 2, E 2, F 2, F3, G3,  enter the  names of the corresponding columns.
• Merge cells A2:A3, B2:B3, C2:C3, D 2:D 3, E 2:E 3, F 2:G  2 with the format button[pic 1]

• Enter the original information in the table cells
• Select the block of cells A 4:A5 and, using the fill handle, fill in the cells of the first column.
• In cell F4, enter the formula =D4-C4 and copy it to other cells down using the fill handle. Pay attention to the modification of the copied formulas.
• In cell G4, type the appropriate formula and copy it down the column

1. Table formatting.  Perform the following formatting operations
2. Edit the document.

• Center the table header relative to the entire table.
• centering graph names;
• Vertical alignment to the top edge of the information in the cell
• framing and highlighting;
• Change the font style of the title, column titles, and summary row.
• Between columns 5 and 6 of the ET, insert an additional column. Enter a column header – Total
• The cells in this column should contain summation formulas. To enter them, select the block of cells to be summarized, and then click  the AutoSum button ()∑ on the toolbar.
• Clear the contents of the block of cells A10:H10(  the Delete key  or the corresponding menu command)
• Cancel the operation (toolbar or main menu command).

1.  Save the document in your team folder.

Financial results of JSC "Akmola-Phoenix", thousand tenge

Task 2. Organization of calculations. Functions. Diagrams.

Complete the following table.

1. Production costs (B6) is calculated as the sum of cells B 7:B10 using the formula =SUM(B7:B10). In cells C6:G6, the original formula is copied.

1. Value Added Tax (B4) is defined as Gross Revenue  * 20%:  =B3*0.20
2. Sales revenue is defined as the difference between Gross revenue and VAT:    =B3-B4
3. Gross profit There is a difference between Sales revenue and Production costs:   =B5-B6
4. Taxable tax is the sum of Gross Profit and Salary:  =B11+B7
5. Enterprise Income Tax eat 22%  From Taxable Tax: =B12*0.22
6. Net income of the enterprise There is a difference between Gross profit and Enterprise income tax =B11-B13
7.  Assign a monetary style to numeric cells (put a tenge sign):

• select a range of cells B3:G14
• Use the commandFormat - Cells, select a tabNumber
• Choose a number format "(all formats)",in the Type enter # ## 0''tg'' field

1. Create a bar chart for the data Gross Revenue, Production Costs, Enterprise Net Income:

• Select A2:G 2 while holding down CTRL to select blocks of  cells  A 3:G 3, A 6:G 6, A14:G14
• Click Chart Wizard and select Bar Chart

1. Adda series of  data to the chart  Gross profit,  Enterprise income tax
2.  Delete a row Gross profit
3. Save the file to a personal folder

Task 3. Use absolute and relative references in formulas. LINKING WORKSHEETS.