Example of calculation and payroll. Example of calculation and payroll Create a payroll table
Structure of the test
The test consists of two parts: theoretical and practical.
The test task must be completed on a computer using Microsoft Office applications.
The first theoretical question must be completed in Microsoft Office PowerPoint. Minimum number of slides – 8 pcs. The slides must contain: a bulleted or numbered list, a table, a diagram, inserted pictures and customized animation. There must be a hyperlink to a text file on your topic (or to a test) and a hyperlink to return to the presentation from the text file. Print the slides using the “Sample Handout” mode and format them as an Appendix
The second theoretical question is carried out in Microsoft Office Word with a volume of 8 - 10 sheets on sheets of A4 paper. The text should be printed in Times New Roman font size 12 - 14 pt; page parameters – margins: top and bottom 2, left and right 2; line spacing is 1.5.
At the end of the work there is a list of used literature, a signature and date of completion.
A completed test assignment will be sent for review. If the test assignment is not passed, the student revises it in accordance with the comments. In this case, the original text, review and comments of the teacher in the margins must be completely preserved.
Structure of the test:
1. Title page with your photograph (Appendix A)
3.Test work
4.Applications
5. References (at least 5 titles)
6. Storage medium (disk) with presentation files, spreadsheet. Disc sign
In addition to the printed version, the test work must be presented in electronic form on a CD. Sign the disk.
The test is sent to the Academy by mail for registration at the Department of Informatics one month before the session
Before the start of the session, it is MANDATORY to find out whether the test is passed or not.
Practical part.
The practical task is carried out using the MS Excel software application.
1.Create a table “Payroll Sheet” (sample on page 3), entering arbitrary initial data (10 lines), the last line with your own last name and salary.
2.In a cell outside the table (preferably at the bottom), enter the number of working days in the month for which payroll is calculated.
3.Enter the calculation formulas in the columns of the first row.
a. “Accrued for time worked” - “Salary * Worked, days / number of working days in a month.”
In the formula of the “Accrued” column, use absolute addressing for the cell with the number of working days in the month
b. Bonus - 20% of “Accrued for time worked”
c. Ural coefficient -15% of (“Accrued for time worked” + Bonus)
d. Total accrued - this is the amount (Accrued, for time worked", Bonus and Ural coefficient)
e.Income tax – 13% of “Total accrued”
f. Trade union - 1% “Total accrued”
g.Advance payment no more than 50% of the “Total Accrued” amount
4.Copy the formulas to all rows of the table.
5. Determine the amounts for the table columns (except for the Worked, days column) using the summation function and specifying the range of cells.
6.Calculate the average salary using the “Total accrued” column
7.Calculate the maximum salary
8.Format the table header and numeric data. Create table borders.
9. Sort the table data by the full name column
10. Construct two charts: one (histogram or graph) - based on the data in the Full Name and For Issue columns on a SEPARATE sheet,
the second (circular) - according to the columns Full Name and Prize.
Title, axes names and data labels are required.
11.Copy the table to the second sheet and configure the program so that instead of values in the cells there are calculation formulas
12. Rename sheets to Tables and Formulas
13.Preview the created table.
14.Print two tables: one with values, and the second in formula display mode.
15.Print out the diagram.
Payroll sheet
| No. | FULL NAME. | Salary, rub. | Worked, days | Accrued for time worked, rub. | Premium, rub | Ural. coefficient, rub. | Total accrued, rub. | Income tax, rub | Trade union contribution, rub. | Total withheld, rub | Advance, rub | For issue, rub |
| Ivanov I.I. | 7600,00 | |||||||||||
| Petrov P.P. | 6500,00 | |||||||||||
| … | … | |||||||||||
| Total: | ||||||||||||
| Number of working days | ||||||||||||
| Maximum salary | ||||||||||||
| Average salary (Total accrued) |
Appendix A
Federal State Educational Institution
higher professional education
Perm State Agricultural Academy
named after academician D.N. Pryanishnikova
Department of Computer Science
Test
in the discipline "Informatics"
Question #1: Hardware. External memory
Question No. 2: Computer system software. Purpose and functions of the operating system.
Performed):
student(s) _2_ course of correspondence department
specialty: “Land cadastre”
group Zk-21 a
Semenova Svetlana Sergeevna
Checked
Art. teacher Zhavoronkova I.V.
Perm 201__
Appendix B
Sample presentation in MS Power Point
Articles to read:
how to... make a payroll in Excel
Open the Microsoft Excel spreadsheet editor and open the “Salary” file created in practical work 3. Copy the contents of the “Salary November” sheet to a new sheet of the e-book.
Give the copied sheet the name “Salary December”. Correct the month name in the table title. Change the Premium value to 46%, Additional payments to 8%. Make sure that the program has recalculated the formulas.
Rice. 4.1. Changed data
Based on the data in the “December Salary” table, construct a histogram of employee income. (Fig. 4.2.)
Rice. 4.2. Salary histogram for December
Before calculating the final data for the quarter, sort by last name in alphabetical order (ascending) in the salary calculation table for October (select a fragment of the table from rows 5 to 18 without the “Total” line, select the Data / Sorting menu, sort by - Column B)
Copy the contents of the December Salary sheet to a new sheet. Give the copied sheet the title “Quarterly Summary.” Change the name of the table to “4th Quarter Payroll Sheet.”
Edit the “Quarterly results” sheet according to the example in Fig. 4.3. To do this, delete the salary, bonus and additional payments columns in the main table, as well as line 4 with the numerical values of %Premium and %Retention and line 19 “Total”. Also delete the lines with the calculation of the maximum, minimum and average income under the main table. Insert a blank third line.
Insert a new Division column between the Last Name and Total Accrued columns. Fill in the “Division” column with data according to the sample (Fig. 4.3).
Rice. 4.3. Table Total for the quarter
Calculate quarterly accruals, deductions and the amount to be issued as the amount of accruals for each month (monthly data is located on different sheets of the e-book, so the sheet address will be added to the cell address).
To insert an address or range of cells from another sheet into a formula, you should click on the tab of this sheet while entering the formula and select the desired cells on it. The address you paste will contain the name of that sheet.
In cell D5 for calculating quarterly accruals “Total accrued” the formula looks like: = "Salary December"!F5 +"Salary November"!F5 + "Salary October"!E5. Similarly, make a quarterly calculation of the “Deductions” and “For issue” columns.
To calculate quarterly payroll for all employees, copy the formulas down columns D, E, and F.
To calculate subtotals, sort by department, and within departments - by last name. (Fig. 4.4)
Rice. 4.4. Table after sorting
Calculate subtotals by department using the summation formula. To do this, select the entire table and run the command Data/Results. In the dialog box that appears, set the parameters as shown in Fig. 4.5.
Rice. 4.5. Dialog box Subtotals
The resulting table will look like this (Fig. 4.6.).
Rice. 4.6. Totals table
PRACTICAL WORK No. 5
Task 17.4. Create a “Payroll calculation” table. Construct a histogram and pie chart based on the calculation results.
Select the data to construct a chart while pressing the key.
The initial data are presented in Fig. 17.14.
Rice. 17.14. Input data for task 17.4
Calculation formulas:
Bonus - Salary X 0,2;
Total accrued = Salary+ Prize;
Income tax= Total accrued X 0,13;
Total to be issued = Total accrued- Income tax.
Practical work 18
Topic: USING FUNCTIONS IN MS EXCEL CALCULATIONS
Purpose of the lesson. Studying information technology for organizing calculations using built-in functions in MS Excel tables.
Task 18.1. Create a table of retail price dynamics and calculate average values.
The initial data are presented in Fig. 18.1.
Operating procedure
1. Launch the Microsoft Excel spreadsheet editor (for a standard installation of MS Office, run Start/Programs/Microsoft Excel).
2. Open the “Calculations” file created in Practical Works 16...17 (File/Open).
Rice. 18.1. Input data for task 18.1
3. Rename the shortcut Sheet 5, giving it the name “Price Dynamics”.
4. On the “Price Dynamics” sheet, create a table similar to the one shown in Fig. 18.1.
5. Calculate the price change in column “E” using the formula
Price change = Price as of 06/01/2003/Price as of 04/01/2003.
Don't forget to set the percentage format for the numbers in column "E" ( Format/Cells/Number/Percentage).
6. Calculate column averages using the Function Wizard fx. Function AVERAGE located in the “Statistical” section. To calculate the average value function, place the cursor in the corresponding cell for calculating the average value (B14), launch the function wizard (using the button Inserting a function f x or team Insert/Function) and in the first step of the wizard select the function AVERAGE(category Statistical/AVERAGE)(Fig. 18.2).
After pressing the button OK A window will open to select the data range for calculating the specified function. As the first number, select the group of cells with data for calculating the average value B6:B13 and click the button OK(Fig. 18.3). The average value of the data in column “B” will appear in cell B14.
Similarly, calculate the average values in other columns.
7. In cell A2, set the function TODAY, displaying the current date set on the computer (Insert/Function/Date and Time/Today).
8. Perform current file save (File/Save).
Rice. 18.2. Selecting the average function AVERAGE
Rice. 18.3. Selecting the data range to calculate the mean function
Task 18.2. Create a table of changes in the number of working days of employees and calculate the average values. Construct a graph using the table data.
The initial data are presented in Fig. 18.4.
Operating procedure
1. On the next free sheet of the e-book “Calculations”, create a table according to the task. Merge selected cells using the toolbar button Combine and place in the center or menu command ( Format/Cells/Tab Alignment/Display– Merging cells).
Rice. 18.4. Input data for task 18.2
Brief information. Changing the direction of text in cells is done by rotating the text 90° in the zone Orientation window Cell Format, called by the command Format/ Cells/ Alignment tab/ Orientation– rotate the inscription by 90° (Fig. 18.5).
Rice. 18.5. Rotate the text 90°
Rice. 18.6. Setting graph axis scale parameters
2. Calculate average values for rows and columns using the function AVERAGE
3. Construct a graph of changes in the number of working days by year and country. Set the “X” axis labels when building a graph on the second screen of the Chart Wizard (tab Row, region "X" axis labels).
4. After plotting the graph, format the vertical axis by setting the minimum value to 1500, maximum value to 2500, and division value to 100 (Fig. 18.6). To format an axis, double-click on it and on the tab Scale dialog box Axis format set the appropriate axis parameters.
5. Perform current saving of the “Calculations” file (File/Save).
Task 1. Create payroll tables for two months on different sheets of the e-book, make calculations, format, sort and protect data
The initial data is presented in Fig. 1, the results of the work in Fig. 6.
Operating procedure.
1. Open the Microsoft Excel spreadsheet editor and create a new e-book.
2. Create a salary calculation table on Sheet 1 according to the sample (Fig. 1).
Select separate cells for %Premium (D4) and %Retention (F4).
Enter the initial data - Personnel number, full name and salary; % Premium = 27%, % Retention = 13%
Perform calculations in all columns of the table.
When calculating the Prize, the formula is used Bonus = Salary *%Bonus,
in cell D5 type the formula =$D$4 * C5(cell D4 used as absolute addressing).
Copy the typed formula down the autocomplete column.
Brief information. For ease of use and to develop skills in working with the absolute type of addressing, it is recommended that when designing constants, color the cell in a color different from the color of the calculation table. Then, when entering formulas into a calculation cell, the colored cell with the constant will remind you that you should set absolute addressing (by typing the $ symbols in the address or by pressing the key).
Fig.1. Initial data for Task 1.
Formula for calculating “Total Accrued”:
Total accrued = Salary + Bonus
When calculating "Retention" the formula is used:
Deductions = Total Accrued * %Retentions,
in cell F5 type the formula = $F$4 * E5
Formula for calculating the “To be issued” column:
To be issued = Total accrued – Withholding
3. Calculate the totals for the columns, as well as the maximum, minimum and average income according to the data in the “For issue” column ( Insert/Function/Category Statistical).
4. Rename the Sheet 1 tab, giving it the name “Salary October”. To do this, double-click on the shortcut and enter a new name. You can use the Rename command in the context menu of the right-click shortcut.
The results of the work are presented in Fig. 2.
Brief information. Each Excel workbook can contain up to 255 worksheets. This allows you to create clear, clearly structured documents using multiple sheets, rather than storing large, sequential sets of data on a single sheet.
Rice. 2. Final view of the payroll table for October
5. Copy the contents of the “Salary October” sheet to a new sheet ( Edit/Move/Copy Sheet). You can use the command Move/Copy shortcut context menu. Don't forget to check the box to copy. Create a copy(Fig. 3).
Fig.3. Copying an e-book sheet
Brief information. You can move and copy sheets by dragging their labels (to copy, hold down the key).
6. Give the copied sheet the name “Salary November.” Correct the month name in the table title. Change the Premium value to 32%. Make sure that the program has recalculated the formulas.
7. Between the columns “Premium” and “Total accrued”, insert a new column “Additional payment” (select column E “Total accrued” and run the command Insert/Columns);
calculate the value of the surcharge using the formula Additional payment = Salary * %Additional payments. Take the value of the surcharge equal to 5%.
8. Change the formula for calculating the values of the “Total Accrued” column:
Total accrued = Salary + Bonus + Additional payment.
Copy the formula down the column.
9. Perform conditional formatting of the values in the “For issue” column. Set the output format for values between 7000 and 10000 - green font color , less than or equal to 7000 – red font color , greater than or equal to 10000 – blue font color (Format/Conditional Formatting) (Fig. 4).
Fig.4. Conditional data formatting
10. Sort by last name in alphabetical order in ascending order (select a fragment of the table from 5 to 18 lines without the "Total" line, select menu Data/Sorting, Sort by - Column B) (Fig. 5).
Fig.5. Sorting data
11. Add the comments to cell D3 “The bonus is proportional to the salary” ( Insert/Note), and a red dot will appear in the upper right corner of the cell, indicating the presence of a note.
The final view of the wage calculation table for November is shown in Fig. 6.
Fig.6. Final view of the salary calculation table for November
12. Save the created e-book under the name Salary .
Excel 4. RELATED TABLES. CALCULATION OF SUBTOTAL IN MS EXCEL TABLES
Purpose of the lesson. Studying the technology of linking sheets of an e-book. Calculation of subtotals. Structuring tables.
Tools. PC IBM PC, MS Excel program.
Literature.
1. Information technologies in professional activities: textbook / Elena Viktorovna Mikheeva. – M.: Educational and Publishing Center “Academy”, 2004.
2. Workshop on information technologies in professional activities: textbook-workshop / Elena Viktorovna Mikheeva. – M.: Educational and Publishing Center “Academy”, 2004.
Microsoft ExcelPractical work No. 1.
Construct a pie chart showing the percentage of items sold.
Save the work in your own folder under the name Item Accounting.
Practical work No. 2.
Create a table for paying wages for the company's employees.
Tax amountPersonal income tax
To payoff
1
Molotkov A.P.
18000
1400
2
Petrov A.M.
9000
1400
3
Valeeva S. Kh.
7925
4
Garayev A.N.
40635
2800
5
Eremin N.N.
39690
1400
6
Kuptsova E.V.
19015
2800
Total
Count the empty columns using the formulas.
Taxable income = Income received – Tax deductions.
Tax amount = Taxable income * 0.13.
Payable = Income received - Amount of personal income tax.Save your work in its own folder under the name Calculation.
Practical work No. 3.
Create a salary table for company employees.
RUB 5,072.37RUB 3,000.00
Below, create a table to calculate the wages of the company's employees.
The worker's salary depends on the category, use the logical IF function. The monthly premium is calculated in the same way. Income tax is calculated according to the formula:PN=(salary+bonus)*0.13. Salary according to the formula:Salary = salary + bonus - PN.Format the table as shown.
Sort table 2 in alphabetical order.
Changes have occurred at the enterprise, enter these changes into the table:
monthly bonuses, regardless of status and category, are paid to everyone in the amount of 3,000 rubles;
the worker's salary increased by 850 rubles;
Makeev retired;
Ivanov rose through the ranks and became an engineer, Korolev became a boss, but Burin was reduced to a worker for violating discipline.
Create a workbook consisting of three worksheets.
Call the first sheet RESULTS. It must contain a report on the financial results of the enterprise for the month.
Label the second sheet REVENUE. Build a table of Sales Revenue for the current month. Count empty columns using formulas. Label the third sheet EXPENSES. Enter the company's expenses for the current month into it. Complete the first sheet using the links to the appropriate sheets.Save your work in your own folder called Results.
Practical work № 5 .
On the first sheet, draw a graph of the functiony = 1+ cos(2* x), on the interval (4.94; -5.06) with a step of 0.4.
Name this worksheet Cosine.
On the second sheet, plot the functiony = a+ sin(k* x), on the interval (6.14; -6.26) with a step of 0.4, wherek=2, a=0.
Experiment by randomly changing the values of the variablesk And a. Track the change in the graph of the function.
Name the second sheet Sine.
Save your work as Trigonometry.
Create a table for accounting of goods, count the empty columns using formulas.
Find the maximum and minimum employee salaries using the MIN(MAX) function.
Using conditional formatting, highlight the cells in red for those employees whose salary is EQUAL to the MAXIMUM.
Save the work in its own folder under the name Salary.
Practical work No. 4.
Loading...
