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

• 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

5. Table formatting. Perform the following formatting operations

6. 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).

7. Save the document in your team folder.

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

№ Indicators 2020 2021 2022 Deviation (+; -)

2021

to 2020 2022

to 2021.

1 Income from sales of products 259068 269952 411671

2 Cost of sales 198146 189156 300287

Gross profit margin 60922 80796 111384

Expenses of the period 54521 53892 85863

Operating profit 6401 26904 25521

Profit from non-core activities 7726 98960 26021

Net income 14127 36800 47713

Task 2. Organization of calculations. Functions. Diagrams.

Complete the following table.

A B C D E F G

1 Statement of income and expenses of the enterprise

2 Index

July August September October November December

3 Gross Revenue 150 678 169 958 198 820 243 097 288 092 300 219

4 Value Added Tax

5 Proceeds from sales

6 Production costs

7 Including: Salary 600 600 600 600 600 600

8 Rent 2 500 2 500 2 500 2 500 2 500 2 500

9 Equipment rental 1 500 1 500 1 500 1 500 1 500 1 500

10 Other expenses 86 264 116 396 152 567 162 554 166 365 181 710

11 Gross profit margin

12 Taxable tax

13 Enterprise Income Tax

14 Net income of the enterprise

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 command Format - Cells, select a tab Number

√ Choose a number format "(all formats)",in the Type enter # ## 0''tg'' field

8. Create a bar chart for the data Gross Revenue, Production Costs,