Automation of calculations

Work purposes: To learn to use spreadsheets for automation of calculations; to fix the acquired skills on filling, formatting and the printing of tables.

Problem definition. The manager of hospital has to make the staff list, i.e. define, how many the employees, at what positions and with what salary he has to employ. The general monthly wages fund makes $10 000. We will construct model of the solution of this task:

Basic data. For normal work of hospital it is necessary: 5-7 nurses; 8-10 nurses; 10-12 doctors; 1 pharmacy manager; 3 managers of offices; 1 chief physician; 1 supply manager; 1 manager of hospital.

At some positions the number of people can change. For example, knowing that it is difficult to find nurses, the head can make the decision to reduce number of nurses to increase a salary of the remained. So, the manager accepts for himself the following model of a task: the salary of the nurse is taken as a basis, and to all other employees are charged on the basis of this salary: in so many time or is one so many more. Speaking to the mathematical language, each salary is linear function from a salary of the nurse: A*C+B, where C – a salary of the nurse (the minimum salary); A and B coefficients which for each position are defined individually. Let’s consider:

Having set number of employees at each position, it is possible to work out the equation:

N₁*(A₁*C+B₁)+N₂*(A₂*C+B₂)+…+N₈*(A₈*C+B₈)=10000, где 10000 – fund of salary, N₁ – number of cleaners; N₂ – number of nurses etc., A₁…A₈, B₁…B₈ – coefficients for each position. In this equation to us A1 coefficients … for A8 and B1 … B8 are known, and for C and N1 … N8 aren't known.

It is clear, that won't be possible to solve such equation known methods, and only the right decision isn't present. It is necessary to solve the equation by selection. Having taken originally any acceptable values of unknown, we will count the sum. This sum has to be equal to a wages fund, i.e. 10 000. If the wages fund is exceeded, it is possible to lower a salary of the nurse or to refuse services of any worker etc. Manually difficultly to do such work, nevertheless, we use for this purpose possibilities of MS Excel:

1. Create the table. Take away for each position one line and bring the name of positions in a column C.

1.2. Columns A and B use for introduction of coefficients A and B corresponding to each position.

1.3. In a cell of H3 bring value of a salary of the nurse and establish for it a format 0,00 – two signs after a comma.

2. In a column D calculate a salary for each position: the salary is calculated on a formula A*C+B. In our table coefficients of A and B are in columns A and B, and C – the salary of the nurse is specified in H3 cell.

Note. The column D has to be filled with formulas with use of an absolute link to H3 cell. Change of contents of this cell has to lead to change of contents of all column D. In this task it is convenient to use one more way of absolute addressing – the called cell.

2.1.To cell of H3 appropriate the name "Минимум" (or as it is pleasant to you).

2.2. In a cell of D3 bring a formula =a3*минимум+v3. Though this formula is equivalent = $H$ A3* 3 + B3, the name appropriated to a cell, facilitates understanding of a formula.

2.3. Copy a formula from D3 cell in D4:D10. When copying the cell address with a salary of the nurse remained constant (absolute), and the addresses A3 and B3 are recustomized (they relative).

4. In a column F calculate a salary of all employees of this position: in a cell of F3 bring a formula = D3*E3 (salary*number of employees). Copy a formula from F3 cell in F4:F10. Establish for data in columns D and F a format 0,00 – two signs after a comma.

5. Define a total monthly wages fund. Summarize a column F, using the button of autosummation of S. Move value of the sum of a cell of F12 and make to it the signature "Total monthly wages fund”

Now start drawing up the staff list: you make changes to a salary of the nurse or change number of employees in E3:E5 cells until the received total monthly wages fund, won't be equal $10000 (in a cell of F12 it is necessary to receive »10000 value).

6. Save the table in the personal folder

7. Use function of automation of calculations – Selection of parameter for drawing up the staff list.

Parameter selection – convenient means of MS Excel for the analysis "What - if". Thus values for cells parameters change so that the number became equal in a target cell to a preset value. Choose Service menu command, parameter Selection.

Specify in the field to Establish the address of a target cell of $F$12 in a cell.

Specify in the field Value – 10000.

Specify in the field Changing a cell the cell address with a salary of the nurse of $H$3 and press the button <ok>. Process of selection of parameter will begin. If to press the button <ok>, values of cells in the table will be changed according to the found solution.</ok></ok>

8. Make some versions of the staff list with use of the Parameter Selection function and issue them in the form of the table

8.1. Change number of employees at positions of the cleaner, the nurse and the doctor.

8.2. Pick up a salary of the nurse in new conditions.

8.3. Make the table of several versions of the staff list.

9. In MS Excel there is an automatic means of preservation of options of search of the decision by means of the Dispatcher of scenarios who is in the Service menu, the Scenario. Use it, having understood in principle its work independently.

10. Analyse the received options, choose and issue one of them.

10.1. Leave columns C, D, E, F. It is impossible to remove columns A, B, H as in the table on them there are links, but they can be hidden. Columns A, B, H need to be allocated, and then to use point of the main Format, Column, Hide menu or to cause the context-dependent menu and to select the Hide item.

10.2. Entitle the table "Staff List of Hospital" and a subtitle "The manager of hospital Akhmetov I.S."

10.3. Remove the table of versions of the staff list.

10.4. The table issue, using autoformatting: allocate all table, including headings; select Format menu item, Avtoformat; choose a format satisfying you.

11. Prepare the table for printing, having used press preview:

• Choose a landscape arrangement. Clean a grid.

• Specify in the top headline a surname, and in bottom – date and time.

12. Independently execute the option of a task which is defined by computer number:

• To make the staff list of high school.

• To make the staff list of a student's hostel.

• To make the staff list of elementary school.

• To make the staff list of a student's dining room.

• To make the staff list of gas station.

• To make the staff list of the entertaining center.

• To make the staff list of restaurant.

• To make the staff list of shopping center.

• To make the staff list of a hairdressing salon.

• To make the staff list of construction crew.

Independent work of the student № 1. Calculation of utilities

Provide that you are the owner of 10 apartments which are rent. You need to keep account on all utilities (the electric power, water, gas, phone, etc.) on each apartment. By results of indexes of counters (or by quantity living) at the end of the month to draw up to tenants the score on payment of the provided services. To issue calculations in a plate look and consider the different area of apartments. By results of activities to count total income from delivery of apartments in rent.

Note: Arrange the table using all the knowledge acquired on laboratory work

Independent work of the student № 2.Salary calculation.

1. Develop the table for calculation of a salary of staff of your enterprise for delivery of apartments in rent. The number of employees of the enterprise shall be at least 10 people. In the table to provide existence of the next columns is mandatory: position, salary, it is fulfilled days, charge (an award, etc.) and holdings (penalties, etc.). Charges and holdings can be detailed, for example, it is necessary to include contributions to a pension fund and income tax in holdings. For charges for waste days to calculate a day rate. To calculate how many the workers will receive all on hands after all extra charges and holdings.

2. To make calculation of a salary with instruction of total information for all enterprise (Total, Minimum, Maximum, Average) according to each column containing numerical values.

Using data on the income from the previous job (independent work №1), find profit on activities of the enterprise for delivery of apartments in employment. If you have no income, change rates in independent work №1 or salary of staff

Note: Arrange the table using all the knowledge acquired in all labs

Date: 2015-12-24; view: 1573