Subject: Means of graphical presentation of statistical data (business graphics). Presentation of the results of the calculation tasks by means of business graphics
Number of hours: 2
Target
The report must contain:
1. Title of work
2. The purpose of the work
4. Conclusion on work (
View document content
"PR16 Means of graphical presentation of statistical data (business graphics)."
Guidelines to practical lesson No. 16
Subject:Tools for graphical presentation of statistical data(business graphics). Presentation of the results of the calculation tasks by means of business graphics
Number of hours: 2
Target: consolidation of practical skills and abilities in working with technologies of work in MS EXCEL:
To form ideas about the possibilities of business graphics and its use;
Learn business graphics systems;
Explore the power of Excel to create business graphics
Task: Familiarize yourself with the theoretical provisions on this topic, complete the tasks of the practical lesson, formulate a conclusion.
The report must contain:
1. Title of work
2. The purpose of the work
3. Results of task 1, 2, 3
4. Conclusion on work ( it is necessary to indicate the types of work performed, the goals achieved, what skills and abilities were acquired in the course of its implementation)
Methodical instructions for implementation:
1 . Brief theoretical information
The term business graphics refers to graphs and charts that visually represent the dynamics of the development of a particular production, industry and any other numerical data.
Objects for which illustrative materials are created using business graphics: planned indicators, reporting documentation, statistical summaries. Business graphics software is included in spreadsheets.
The possibilities of business graphics pursue a single goal: to improve the perception of information by a person, to make it more visual and expressive.
The main purpose of business graphics is the prompt presentation of information in graphical form in the process of analyzing a task when solving it using a spreadsheet. The main criterion is the speed of preparation and display of graphic images corresponding to rapidly changing numerical information.
In the modern world, there is a large amount of information that is difficult to immediately assimilate to an ordinary person. And if you need to process, analyze some data, then the task becomes doubly difficult. Nowadays, with the help of computer technology, it is possible to store and process any information.
Business graphics is a field of computer graphics. It is designed to build histograms, graphs, charts, diagrams based on reports, various documentation. Provides a more concise and expressive presentation of data, more complex tasks, improved perception of data, increased work efficiency.
To display graphic information special software, which is called graphics programs or graphics packages.
The use of business graphics is varied and diverse. For each type of task, different programs. Some are needed for online processing of digital data, others for drawing drawings. High Quality. With the help of special application tools, annual reports of firms, statistical reference books are compiled, and analytical data is processed.
Properly drawn diagrams or graphs turn boring numbers into visual images and help users navigate the “sea” of information and make the right decision. Business graphics allow you to conveniently compare various data, identify patterns and development trends. You can also use various graphic programs solve problems in the field information technologies, architecture and engineering. At present, business graphics have firmly entered our lives, now it is impossible to present any summary documents or presentations without tables, diagrams, charts, and various images.
computer graphics used by administrative and technical staff, students, as well as ordinary users personal computers.
With a rich library Excel charts you can make charts and graphs different types: histograms, pie charts, column charts, graphs, etc., they can be provided with titles and explanations, you can set the color and type of hatching in charts, print them on paper, changing the size and location on the sheet, and insert charts in the right place on the sheet.
2.Task
Task number 1. Create a table for the sale of stocks of a brokerage firm. Perform assignment calculations.
TASK 2. Creation and processing of a database in Excel. Linking Workbook Sheets
1. Create a table in a new file according to the model (see Fig. 1). Signs?, indicated in some cells, mean that these indicators need to be calculated.
3 .Format the table.
4 .Follow the steps below:
3. On sheet 4, create a table shown in Fig. 4.
3.1. To add a Sheet, click right click mouse on the Sheet shortcut and from the context menu select the command Insert. In the tab Are common choose Sheet and press Ok.
3.2. In cell B2, enter the formula for the total calculation of products sold for January, February and March, namely
SHEET1!G4+SHEET2!G4+SHEET3!G4, where the sign! means a link between sheets.
To optimize formula input, do the following:
1. Place the cursor in cell B2 and enter the = sign.
2. Click on the label Sheet1, click on cell G4.
3.Press the + key
4. Click on the label Sheet 2, cell G4, press +.
5.Click on the label Sheet 3, cell G4 and press Enter
6.Copy the entered formula to the rest of the cells.
3.3. In cell C2, enter the formula for calculating the total revenue for the 1st quarter (i.e., the months of January, February, March)
SHEET1!H4 + SHEET2!H4+SHEET3!H4
3.4. Rename sheet 4 to Sales results (goods).
4.On Sheet5 create a table, pictured on fig5.
. 
4.1. Independently enter formulas for calculating the final sale and final revenue for January, February and March.
4.2. Rename Sheet5 to Sales results (months).
4.3. Build charts:
Product Name and Sold
Product name and revenue
3. Control questions
1. What is meant by the term business graphics?
2. What is business graphics for?
3. Who uses business graphics?
4. Literature
1. https://superbotanik.net/referati/referati-po-informatike/referat-delovaya-grafika
Criteria for evaluating completed practical tasks
The first task is worth 2 points (maximum), and the second - 3 points (maximum)
In total, performed practical work can be assessed from 0 to 100%, and then the assessment of the student's knowledge will be as follows:
"excellent" - 86 - 100%,
"good" - 66 - 85%,
"satisfactory" - 45 - 65%,
"unsatisfactory" - less than 45%.
The purpose of the course: the formation of ICT competence.
Lesson topic: Business graphics tools for visual presentation of data using charts various types.
The purpose of the lesson provides for the relationship of educational, training and developmental components.
The purpose of the lesson: Formation of competence related to work with automated information systems (MS Excel application).
Lesson objectives:
- Educational (axeological component of competence)
- to form a situation of success in the learning process as a mechanism for restoring motivation for education and to form the need for self-education;
- to promote the development of the ability to argue one's beliefs;
- to form a culture of mental work and the ability to plan one's work, to carry it out rationally;
- to promote the development of perseverance and purposefulness, creative activity and independence, the ability to argue one's convictions.
- Educational (cognitive component of competence)
- introduce the basic technologies for converting information (from numerical to graphical) using business graphics software application MS Excel;
- Practical and operational application of knowledge (operational component of competence)
- develop skills practical application knowledge to specific situations: building diagrams based on the monitoring.
- develop the skills of analysis and comparison of quantities,
Teaching methods:
method for solving a specific situation (case method):
The essence of the method is quite simple: for the organization of training, descriptions of specific situations are used (from the English “case” – happening). Students are offered to comprehend a real life situation, the description of which at the same time reflects not only some practical problem, but also actualizes a certain set of knowledge that needs to be learned when solving this problem. At the same time, the problem itself does not have unambiguous solutions.
verbal:
conversation, a characteristic feature of the conversation is the presence of control questions that are asked by the teacher according to a pre-planned scenario, leading to the goal set by the teacher,
briefing, characterized by clarity of presentation, does not allow free interpretation. The main didactic function is the assimilation standard ways actions;
visual:
computer demonstration, has dynamism and visibility;
practical:
practical work on a PC and in workbooks contribute to the formation of skills computer use, ordering students' knowledge on the topics of the course, the formation of experience in creative activity.
Forms of study:
frontal form, used in the implementation of verbal, visual and practical methods, as well as in the process of control;
group form, used in a group whose members have different levels of training and motivation;
the “student and computer” form – the student masters knowledge at his own pace, he chooses an individual route for studying educational material.
Knowledge control
- monitoring the work on the PC;
- front poll,
- testing;
- independent practical work,
- self-control.
The use of health-saving technologies in the classroom:
- change of activities;
- compliance with the air-thermal regime;
- performing erg exercises;
- rationing of time when working with computer equipment.
Equipment:
- computer class,
- multimedia projector,
- screen,
- software (MS Excel application),
- tests,
- instruction cards for differentiating tasks
- presentations for the lesson Annex 1 And Annex 2)
Type of lesson: lesson explanation of new material and systematization of knowledge. Its logic corresponds to the structure of the lesson of this type.
Lesson Plan (Timekeeping)
- Organization of the lesson - 1-2 minutes
- Introductory speech (message of the goals and objectives of the lesson) - 2 min
- Updating of basic knowledge and skills
- Frontal survey: testing with the help of a presentation (Question-answer, self-test - 14 min
- Explanation of new material using PC - 25-30 min
- Fizminutka - 2 min
- Formation and application of knowledge, skills and abilities
- Practical work 1–3 (various levels of difficulty) – 30 min
During the classes
I. Organizational moment.
- I would like to start our today's lesson with the words of V.G. Belinsky: "A man is afraid only of what he does not know, knowledge conquers all fear."
– Announcement of the topic and objectives of the lesson.
II. Actualization of basic knowledge th.A) Frontal survey students on the topic “Spreadsheets” using a presentation ( Slide) (Annex 2)
1. In a spreadsheet, the main element of a worksheet is:
cell;
line;
column;
formula.
2. In a spreadsheet, a cell is called:
horizontal line;
vertical column;
cursor - a frame on the screen;
intersection of row and column.
3. In a spreadsheet, it is not possible to delete:
line;
column;
cell content;
cell name.
4. Select the correct row designation in the spreadsheet:
18D;
K13;
34;
AB.
5. Select the correct column designation in the spreadsheet:
D.F.;
F12;
AB;
113.
AB.
6. Select the correct cell address in the spreadsheet:
11D;
F12;
AB3;
B1A.
7. A spreadsheet cell cannot contain:
number;
text;
sheet;
formula.
8. If the sequence of characters ###### is displayed in a spreadsheet cell, this means that:
the formula is written with an error;
the formula contains a reference to an empty cell;
there is a circular reference in the formula;
the column is not wide enough.
9. The content of the current (selected) cell is displayed:
in the name field;
in the status bar;
in the formula bar;
in the "View" item of the text menu.
10. How are the numbers in spreadsheet cells usually (that is, default) aligned?
in the center;
in the center of the selection;
along the right edge;
along the left edge.
11. How many cells does the range D4:E5 contain in the spreadsheet:
4;
8;
9;
10.
12. Cell D7 contains the formula: (С3+C5)/D6. How will it change when this E8 formula is transferred?
(C3+C5)/D6;
(C3+C5)/E6;
(D4+D6)/E7;
(С4+C6)/D7.
13. Given a spreadsheet. The formula is entered into cell D2: (A2*B1+C1) What value will appear in cell D2?
| A | B | C | D | |
| 1 | 5 | 2 | 4 | |
| 2 | 10 | 1 | 6 |
14. Cell B1 contains the formula =2*A1. What will the formula look like when cell B1 is copied into cell C1?
2*B1
=2*A2
=3*A2
b) Written survey
- Sign the sheets, choose one answer. (see Attachment)
III. Presentation of new material
– Virtually all modern spreadsheet processors have built-in business graphics.
Spreadsheets allow you to visualize data placed on a worksheet in the form of a chart or graph that visually displays the relationships between data.
For each dataset, it is important to choose the right type of chart to create.
– Diagram- this is a means of visual graphic representation of information, designed to compare several values or several values of one value.
– To plot a chart using the GPU, you should:
1) make a table with a data set;
2) select the required range of data on which the chart will be built;
b) choose the most suitable type charts: Insert menu - Chart next step by step
- Today in the lesson we will look at the most common types of charts.
1. Pie Chart (Slide) (Annex 1)
a) serves to display values from the whole; b) serves to compare several values at one point.
For example, 1) The share of the price of components in a computer,
2) Dunno stationery seller (on my own)
2. A bar chart (Histogram) (Slide) is used to compare several values at several points. The height of the columns is determined by the values of the compared values. Each column is tied to some reference point.
For example, 1) Dunno sells newspapers. The reference point corresponds to the day of the week. (Slide)
2) Now consider a more complex problem, in which it is required to compare several values several times. (Slide)
Let's build a bar chart that will display data on three sellers at once. As before, the height of the column will symbolize the number of newspapers. As before, we will have 7 reference points - one for each day of the week.
Copy the data table to a new sheet. We add data. We build a diagram. Determine the difference from the previous diagram. (Now at each anchor point there will be not one column, but three - one for each seller. All columns of one seller will be painted in the same way, you need a LEGEND)
3. Line chart (Slide)serves to track the change of several quantities when moving from one point to another. Chart type - Schedule or dotted. Chart type Schedule choose when to display data changes over time.
- For example (Slide),
1) Build a chart showing the dynamics of prices over the years for different models computers.
2)Construct a chart showing the change in the number of newspapers sold during the week.(Slide)
CONCLUSION: Spreadsheets excel allow you to build diagrams of various types. The construction of diagrams is carried out using a special subroutine called Master diagrams.
Fizkultminutka, gymnastics for the eyes - 2 min.
IV. Consolidation. Practical work.Purpose: to form the ability to perform tasks: to build diagrams of a certain type on a data set.
Efficiency: the formation of all types of competencies depending on the selected tasks.
We will carry out practical work using the method of analyzing a specific situation. This method is often called case - method.
(Slide) Case - technology (case study - "analysis of specific situations")
"Motherland" this method is the School of Business at Harvard University.
Method case - technologies allows students to be interested in studying the subject, contributes to the active assimilation of knowledge and skills in collecting and processing information that characterizes various situations; teaches to look for non-trivial approaches, since it does not have the only correct solution, promotes creative problem solving and the formation of the ability to analyze the situation and make decisions.
– You had a task in the time frame – One week – Monitor temperature regime on college premises. (Cards - instructions: Appendix 2 )
Exercise 1 : Build a diagram that reflects the temperature monitoring in the college premises.
Prepare your materials. Let's analyze the algorithm for performing this task:
1)Create a data table.(Which columns should be created in the table? How many rows? How to head the columns?)
2) Insert Chart. (Which type of chart should be chosen in this case?)
(Slide with chart image)
The students receive the task on the instructional cards. They need to build charts based on the received data and analyze them.
Task 2: Construct a diagram reflecting the monitoring of the temperature regime in the offices in comparison with the SanPiN temperature standards (SanPiN - sanitary norms and rules approved by the chief sanitary doctor of the Russian Federation).
On the same data set, build two charts of different types.
Task 3: Using the data given in the table, construct a diagram that characterizes the relationship between non-metric units of length. What type of chart should you choose?
Values, mm |
|
| Weaving | |
| Arshin | |
| Quarter | |
| Vershok | |
| Foot | |
| Inch | |
| Line |
V. Summary of the lesson
What new concepts did you learn in class today? ( Slide)
– In the lesson, we got acquainted with the elements of business graphics, learned how to choose the type of chart based on a data set.
- Let's summarize the lesson: Take each envelope, there are three cards, choose what is closer to you and put the card in the box. Galya and Natasha (Counting Commission) will enter the results of your choice into the table and we will see the level of assimilation of the new topic in %. ( Slide)
VI. Homework
Grading a lesson.
- The lesson is over. Thank you for your work.
Class 3 . Visualization of ezine data.
1. Charting the current progress of students .............................................. 1
............................................................................ 1
........................................................................ 2
......................................................... 7
2. Selecting a range of cells in the journal for visual representation of the final progress of students .............................................................. ................................................. ................................... 8
3. Using long-term performance data to draw conclusions about trends in performance over time .............................................................. 9
4. Practical tasks for the lesson ............................................... ............................. eleven
5. Questions for understanding the lesson material .............................................. ........ 12
1. Building a diagram of the current progress of students.
As you have already seen on the material of the last lesson, working with electronic journal allows you to monitor the learning process and quickly summarize the results. However, no matter how interesting dynamic table(journal) progress report for presentation or expressive reporting document colorful charts are in the best possible way representing numbers and demonstrating the relationship between them. The Excel spreadsheet editor will allow you to quickly and efficiently solve various tasks related to the visual presentation of your data.
¨ Open the electronic journal you created earlier .
The procedure for creating charts in Excel is elementary. Let's use the "Diagram Wizard" program built into the editor.
1.1. Select a range of cells.
¨ First you need to select the range of cells that you intend to present in the form of a chart or graph.
¨ 
I propose to highlight the cells that display the current performance of your students (see Fig. 1).
Rice. 1. Selecting a range of cells to build a chart or graph.
¨ This is how we indicate to the Chart Wizard the data source for plotting.
1.2. Working with the Chart Wizard
¨ Then find the button with the image of a stylized chart on the "Standard" toolbar and click on it.
Rice. 2. Chart Wizard button.
¨ The Chart Wizard starts in the dialog box, with which we begin to work. We have to take four steps to the final result (see Fig. 3).
Rice. 3. View of the dialog box of the Chart Wizard.
¨ In the first step, we choose the type of chart. Our goals of displaying the development of the process over time (in our case, the current performance) are well met by the Graph. We choose from many options.
¨ I draw your attention to the fact that when choosing one or another type, it is necessary to pay attention to the comments in the lower right part of the window, as they describe the selected chart.
¨ At the same stage, you can use the "View result" button. By pressing the specified button, in the Sample window in the right part of the dialog box (after clicking the View button, the View window changes to the Sample window), you can see how the data marked for visual presentation will look when using one or another type of chart (see Fig. 4).
Rice. 4. View of the dialog box of the Chart Wizard after clicking the "Preview" button.
¨ 
After clicking on the "Next" button, we proceed to the second step of working with the Chart Wizard, which defines the data source for plotting. But we have already done this by selecting the range of current progress cells before starting the Wizard. Therefore, the "Range" window turned out to be already filled in with us (see Fig. 5).
Rice. 5. View of the dialog box of the Chart Wizard in the second step.
¨ Here it is necessary to check the correct choice of the rows of our chart: whether the rows are located in rows or in columns. Note that since we are interested in the performance of each student for the study period, it is necessary to change the selection to "Lines". Otherwise, the graphs will display the data in an inverted format for the usual presentation of such data.
¨ In addition, you should pay attention to the fact that cells with table column headings (Last name, first name and dates of lessons) were also perceived by the Chart Wizard as elements that must be taken into account when building graphs.
¨ 
In order to exclude those series or rows that should not be displayed on the charts, you can activate the "Series" tab of the Chart Wizard Dialog Box for step 2 to delete unnecessary data.
Rice. 6. The "Delete" button to exclude unnecessary data from the construction.
¨ After deleting unnecessary rows, it is necessary to fill in the “X-Axis Labels” window. To do this, we check that the cursor is in the specified window, and select the range of cells with the dates of the lessons in the main table (log) (see Fig. 7).
Rice. 7. Filling in the "X axis labels" window.
¨ 
The selected range of cells will be automatically entered in the specified window, and the dates of the classes will appear under the X-axis divisions.
Rice. 8. View of the Chart Wizard window at step 2 after filling in the “X-Axis Labels” window.
¨ 
Now you can click the "Next" button and proceed to the third step of the Chart Wizard.
Rice. 9. View of the "Chart Wizard" dialog box in step 3.
¨ At this step, you are invited to fill in the windows with the names of the diagram, X and Y axes. Here you can also work with various window tabs to get the desired type of diagram. To enter the name and labels of the axes, click in the appropriate field (a text cursor will appear there) and type the required text on the keyboard. To customize the legend ( symbols) you need to click on the tab with the same name and adjust the position of the legend by selecting the desired position with the mouse in the left part of the window. In the process of setting up, in the right part of the window, you can already look at the prototype of the graph. If something does not suit you, you can return to the previous steps by clicking on the "Back" button.
Rice. 9. View of the Chart Wizard dialog box in step 3.
¨ At the 4th, final step, you must specify how to place the chart: on a separate sheet or on the sheet where the journal itself is located.
Rice. 10. View of the dialog box of the Chart Wizard in step 4.
¨ After selection, click the "Finish" button.
In Fig. 11 below, you can see the result of the Chart Wizard. The master fulfilled all our wishes, but perhaps the result does not fully satisfy you.
Rice. 11. View of the finished diagram after the completion of the Diagram Wizard.
1.3. Changing the appearance of chart objects.
Now let's talk about how you can change the parameters of the finished chart.
¨ Having a ready-made diagram, you can easily change those parameters that do not suit you. To do this, select an area of the diagram with a mouse click.
¨ Now pay attention to the change in the horizontal menu: there, instead of the Data element, the Chart element appeared, in the drop-down menu of which you can find commands that will allow you to change the type of chart if you wish, add or delete source data, change the chart parameters and its placement.
¨ 
In addition, by selecting individual chart elements, you can change their format. On fig. 12 shows the diagram after changing the formats of some of its objects.
Rice. 12. View of the diagram after changing the formats of some objects.
I do not urge you to exactly follow the solutions shown in the figure above. I need this variegation in order to demonstrate the effects of changing the formats of objects. I look forward to more sophisticated solutions from you.
2. Selecting a range of journal cells for a visual representation of the final progress of students.
Now let's try to build a diagram corresponding to the final learning outcomes. In this case, we will have to use non-contiguous data ranges for plotting (a column with the names of students and a column with their final marks).
In order to select non-contiguous ranges of cells, we will have to select using keys ctrl .
¨ First select the range of cells that contain the last names and first names of students, then click keys at ctrl and select the column with the final results (see Fig. 13).
Rice. 13. View of the table after selecting non-adjacent columns.
¨ 
As a result of the actions already known to you, we will get a diagram of the rating of students in this subject at the end of the quarter (see Fig. 14).
Rice. 14. Chart built using data in nonadjacent columns.
NB! I draw your attention to the fact that our Diagrams are still changing synchronously with the changes made to the journal.
¨ Check this by making changes to your log data and going to the graph.
¨ To fix the diagram in the state in which it is in this moment, refusing to update it further in accordance with changes in the initial data, it is necessary:
q select chart,
q highlight a series of data on a chart,
q click in the formula bar and press the keyF9,
q repeat the previous two steps for all the data series presented in the chart.
¨ As a result of these actions, cell addresses in the formula bar are converted to absolute numeric values.
3. Using long-term performance data to draw conclusions about trends in performance over time
¨ When working with charts, you probably noticed the Add trend line command in the pop-up menu of the Chart element or in context menu when you click on a chart data series. This command allows you to predict the behavior of a selected value based on input data.
¨ In the case when it comes to accepting management decisions, a visual representation of such data will help you to accept it and at the same time be very convincing if the case requires a collective discussion of this decision.
¨ I bring to your attention a table with data on the average grade of class performance in the subject for several reporting periods.
Fig.15. A table that includes historical values based on history data.
¨ If we want to continue the table using forecasting capabilities, then in order to save the original data after recalculation, we need to copy them to the next column, which we will change.
¨ 
After that, you need to select the initial data and continue selecting cells for the period for which we want to build a forecast.
Rice. 16. Selecting a range of cells to build a forecast.
¨ Now in the menu item Edit we find the command Fill è Progression. In the Progression window, activate the option Automatic detection step, and the result will not keep you waiting.
¨ 
You can go the other way. Based on the experimental data of the table, we will construct a diagram of the evaluation behavior and already in finished chart Let's add a trend line.
Rice. 17. Trendline window when a linear trendline is selected.
¨ When working with the tab of this Parameters window, you must specify the range for which the forecast is carried out. Here you can also activate the option to show the equation on the diagram, etc.
Rice. 18. Tab Parameters of the Trendline window.
¨ As a result, we will get a fairly convincing visual material. If the studied value has the desired trend on the graph, then work in this direction is proceeding successfully. If the received trend does not coincide with your expectations, then something should be changed in the established rhythm of work.
4. Practical tasks for the lesson
Exercise 1 .
Ø Using the information received, build a graph of the current progress of students.
Ø Present the graph on the current sheet, i.e. on the sheet of the journal with the progress table.
Ø Change, using "handlers", (active points on the border of the selection zone) the size of the diagram area so that the graphs for each student are clearly readable.
Task 2 .
Ø Change the fonts and their sizes in the chart title and axis labels.
Ø Change the background of the chart area to a background of a different color with a gradient fill.
Ø Change the position of the Legend and its background.
Ø Apply other colors to display the current progress graphs of those students whose data on the chart are shown too pale, in your opinion, by the colors applied automatically by the Chart Wizard.
Ø Change the type and thickness of the graph lines used to draw.
Ø Try changing the look and color of the markers on the charts.
Ø Place the resulting diagram on a separate sheet.
Task 3 .
Ø Build a diagram of the final learning outcomes of your students.
Ø Fix the chart view using the available option to convert cell addresses to absolute values.
Ø Try to plot a slice of knowledge on one of the days of class, the date of which is in the middle of the learning period.
Ø Try to build charts showing the intensity of the survey and the distribution of grades in the lessons.
Ø For greater clarity, use the ability to change the formats of chart objects.
Ø Place the resulting diagrams on separate sheets.
Task 4 .
Ø Build a forecast of the behavior of the knowledge slice graph for the future period, using the addition of a trend line.
Ø Use the ability to fill the table with an arithmetic progression with automatic step selection and, based on the data, plot the behavior of the predicted value.
Ø Answer the question: is there a difference in the obtained diagrams?
5. Questions for understanding the material of the lesson
1. The look of a pie chart always attracts attention. What type of chart is suitable for graphical representation of what data?
2. To predict the behavior of what quantities can you use the possibilities of the trend?
How to build a Gantt chart in Excel, automatically check payment requests, patch holes in tables downloaded from 1C? Such questions were asked by financial directors during a survey conducted by the editors of the magazine. Some of these tasks are solved using the built-in tools of the program, others - due to simple combinations of typical functions.
Based on the results of a survey conducted by the editors of the magazine, a list of the most common problems that CFOs face when working in Excel was compiled, and for them the most optimal solutions. Mastering the proposed methods and techniques will take no more than five minutes, and the time savings when compiling reports in Excel will be significant in the future.
Uploading data from the accounting system
Often a source of data for a variety of management reports is the information downloaded from the accounting system. A common drawback of such imported tables is that there are empty cells where some analytical attribute should be specified. If multiple rows have the same dimension value, it is often only shown on the top row. This hinders the construction pivot tables based on the uploaded data, does not allow the use of filters and formulas for analytics - in a word, a whole bunch of problems arise.
Of course, with a small number of such empty cells, analytical features can be copied manually. If the number of rows in the table exceeds several hundred, this method is not suitable. And when there is little data, monotonous manual operations are the source of most errors. Therefore, it is better to refuse manual entry and act according to the following scheme:
- select a column with empty cells (range A1:A12);
- leave in the selection only cells without values. To do this, press the F5 key, in the dialog box that opens, click the "Select" button, then select the "Empty cells" condition and click "OK";
- in the first cell from this range (A2), enter the “equal” sign and press the “Up” button on the keyboard. The result is a formula that refers to the previous cell. But after its appearance, you must press not Enter, but the key combination CTRL + Enter. This will copy the formula to all selected cells;
- get rid of the formulas and leave only the analytics values in the column. Select the range again (A1:A12), copy it and paste it in the same place, but already with the same values (right-click on the selection - " Special insert” – select the option “Insert values”).
Protection against manual input errors
Those financial directors who constantly have to prepare plans or reports in the context of counterparties know firsthand how much trouble the lack of uniformity of data entered in different tables. For example, it happens like this. In the register of buyers, the company is reflected as LLC "Romashka", in the report on debtors - "Romashka", in the payment calendar - Romashka Ltd. It would seem a trifle, and so it is clear what kind of organization we are talking about, but when you try to sort by companies, nothing good will come of building a pivot table. Excel has a simple and convenient tool for this case - checking the input data with a drop-down list.
So, suppose that it is extremely important for us to check the correctness of entering the names of goods in a sales report.
We start by creating a list of the correct product names on any sheet - a sample that Excel will use to validate the input data. Next, the created range must be given a name for later use as a data source for the drop-down list. To do this, select our list (all cells containing the correct names of products), press the key combination CTRL + F3 and the "Create" button and enter the name of the range without spaces, for example, "Products".
Now we return to the sales report and select the area of the cells where the names of the goods will be entered. Open the tab "Data" - "Data Validation". In the appeared window "Checking input values" on the tab "Parameters" set the data type. In our case - "List".
After that, in the "Source" line that appears, it is enough to enter a link to an exemplary list of items - "=Products". After that, it remains to click the "OK" button.
Going to one of the cells in the report where the names of the goods will be entered, you will see that an arrow will appear next to it. Now you can enter the nomenclature manually (Excel will issue a warning if an error was made) or select from the drop-down list.
By the way, with the help of "Check" you can limit the size of the entered numbers ("Integer" and "Real") and the length of the string ("Text Length"), set date ranges ("Date") or create your own restriction by entering the necessary formula (" Another").
Visual cash flow statement
A good illustration of a DDS report is a waterfall variance chart (see Figure 1). It characterizes the dynamics of net cash flow. The company's opening balance for January was 100 million rubles. Over the month, net cash flow turned out to be negative - 10 million rubles, and in February - another -30 million rubles.
picture 1. Building a waterfall chart of deviations
Got it? Such diagrams are very convenient, but are not among the standard ones. Here's how to fill this gap:
- First, we prepare the initial data - an additional table containing four columns (see Fig. 1): date (month), positive and negative net cash flows, as well as an auxiliary column - the cash balance at the end of the month (the first value in it is the incoming balance );
- select the table (range A1:D13) and build a stacked histogram based on it (on the "Insert" tab, select the "Histogram" item, then "Stacked histogram");
- right-click on the bottom row of chart data and make it invisible. To do this, select the Format Data Series command from the context menu (in Fig. 2). In the window that opens, in the "Fill" category, select "No fill", in the "Color of lines" category - "No lines". It remains to click "OK" - the diagram is ready.
figure 2. Choosing an invisible border and a transparent fill
Current exchange rate in Excel
Very often, in calculations performed using Excel, you need to use the current rates of various currencies. Let's take an example from practice. The company specializes in the trade of imported goods. The price list includes the price in dollars and rubles. The selling price in rubles is calculated in Excel daily at the rate of the Central Bank, increased by 2 percent. The calculation itself is not difficult, provided that the current dollar exchange rate is entered into the table daily by a specialist “by hand”. Alas, in the company in question, once the course was not changed to the current one on time. As a result, the customer was billed with the wrong price and the bill was paid. The company lost money. This would not have happened if Excel had been trusted to update the course. To do this is simple:
- click on the "Data" tab in the "Get External Data" group the "From Web" button;
- in the request window that appears (it resembles a regular Internet browser), enter the address of the site from which we plan to take information (for example, yandex.ru or any other portal that publishes up-to-date information about the exchange rate) and click "Start". Please note that in Excel and on the website, the separators of the integer and fractional parts of the number (dot or comma) must be the same;
- as soon as the page loads, black and yellow arrows will appear on it. They mark the data areas suitable for import into Excel (see Fig. 3). It remains to click on the arrow next to the data of interest and click the "Import" button in the lower right corner of the window. The program will ask where to put the new data and load it into the right cells in a few seconds;
- to set the frequency of updating the exchange rate, right-click on any cell from the inserted range. Then, in the context menu, select the "Data range properties" command and specify the most appropriate option - update each time the file is opened or every few minutes (the number of minutes is indicated).
figure 3. Setting automatic update exchange rates
Gantt Chart in Excel
The most popular tool for planning and controlling the timing of work on various projects is the Gantt chart. But using specialized solutions, such as MS Project, is not always convenient. It is better to build a Gantt chart in Excel. The easiest way is to draw it using conditional formatting.
First, you will need to create a worksheet with the names of the work, the timing of their execution (start, finish, duration), the amount of delay by stage in days and percentages (see Fig. 4).
figure 4. Gantt Chart
Things to keep in mind when preparing this table:
- horizontal dates (in Fig. 4 from cell G1 to the right along the line) depend on the timing of the project itself. For short-term projects, for example, this is one or two days, for long-term projects - a week, a month or more. For universality, you can specify the timeline step in a separate cell, and then refer to it when marking dates;
- the beginning of the first stage (cell B2 in Fig. 4) is set manually;
- the delay value per stage determines how long after the completion of the previous stage, the next one begins. It can be either positive (pause between stages) or negative (the next stage is performed simultaneously with the previous one). Technically, this is implemented using formulas such as shown in Figure 5.
figure 5. Source table for Gantt chart
Now we need to set up the conditional formatting rules. This function will help to mark the cells with color, thereby depicting the stages of the project and their implementation. The first thing to do is to select the range of the table that is directly intended for displaying the Gantt chart (in the example, its upper left corner is cell G2). Then select the "Conditional Formatting" command on the "Home" tab, click on the "Create Rule" item, then "Use a formula to determine the cells to be formatted." It will be necessary to formulate only two rules - to fill, for example, in blue the interval where the work has already been completed, in blue - it is still being done. These conditions are specified using formulas like:
AND(G$1>=$B2;G$1
AND(G$1>=$B2;G$1
where AND is a logical function that checks all the criteria specified in the arguments (listed in brackets separated by a semicolon) and requires each of them to be met. For example, the condition G$1>=$B2 is nothing more than checking if the current cell date (G1) is later than the work start date (B2), and G$1
It is worth paying attention to the order in which you enter the conditions, because Excel checks them and applies the selected formatting in the order in which they are entered.
Grouping data in Excel
Suppose you have a summary of orders received from customers for the previous year. The table contains more than 5.5 thousand records (rows), the main columns are: “Order number”, “Date”, “Product name”, “Article”, “Quantity”, “Order amount in rubles, excluding VAT”. The task is to urgently group all orders by months and quarters, calculate intermediate totals for them.
The simplest and at the same time the most inefficient solution is to sort all transactions by date, then add an empty line after the last day of each month in which to calculate the total by autosum.
It will take a lot of time to complete all these steps. And you can do it in two minutes if you use Pivot Tables.
The procedure will be like this:
- select all the cells of the original table. After that, on the "Insert" tab, click the "Pivot Table" button. In the dialog box that appears, click the "OK" button. Immediately, a pivot table layout will appear on a new sheet, as well as a list of its fields (columns of the order sheet in our case);
- with the mouse, drag the "Date" field from the list of fields to the "Line names" area, the "Order amount in rubles excluding VAT" field - to the "Values" area;
- we set the “correct” names for two columns of the pivot table - “Date” and “Order amount in rubles excluding VAT”, respectively. The layout has already taken the form of a normal, familiar table, there is not much left;
- put the cursor on the first value of the "Date" column, go to the "Data" menu tab and click the "Group" button. In the dialog box that appears, select the grouping condition "Months" and "Quarters", click the "OK" button. The program will automatically create a data structure broken down by quarters and months, and will also calculate the totals (see Fig. 6).
figure 6. Grouping data by month and quarter
Checking the application for payment for compliance with the budget
The standard SUMIF function can work wonders. For example, with its help, before agreeing on the next request for payment, you can easily and quickly check whether this will lead to overspending on a particular cash flow budget item.
Let's assume that there is a cash flow budget drawn up for a month. It contains a separate block of items, payments for which are made on the basis of incoming applications from departments (payment for goods to suppliers, rent, transportation costs). There are also columns whose names speak for themselves: “Plan for the month”, “Fact for the current date”, “Amount of payments declared but not executed on the current date”, “Expected deviations from the budget when all applications are completed”.
In addition, there is a register of payments, formed from applications for payment received from the heads of functional units. The analyst registry has the following: the date of application, account details, the unit that initiated the payment, and the amount. Most importantly, for each application for payment, the budget item is indicated, within which the payment will be made and the status of the application - “Paid” or not (in the latter case, the column is not filled in).
And now about how to organize automatic verification of payment requests based on these tables:
- add one more column to the register of payments. It is intended solely for official purposes - to solve technical problems. Enter the following formula into its cells:
=F14&" "&H14,
Where F14 is a link to a cell that indicates which budget item the payment is planned for, H14 is a link to a cell with the application status (paid or not), and the & sign is a function that combines text values. A space in quotation marks (" ") is included in the formula so that the text does not stick together. If cell F14 indicates "Payment for goods", H14 - "Paid", then the above formula will give the following result: "Payment for goods paid";
- we check whether the names of budget items in the register of applications and in the cash flow budget match. They must be completely identical;
- in the budget of funds for each item of expenditure, we calculate the actual expenditure of funds for the current date. For example, to determine how much money the company spent on paying for goods, the following formula is entered into the cell at the intersection of the line "Payment for goods" and the column "Fact on the current date":
=SUMIF("Register of payment requests"!I14:I57;"Payment for goods paid";"Register of payment requests"!G14:G39),
Where "Register of applications for payment"!I14:I57 is a link to all cells of the technical column in the register of applications. By sorting through the data from this column, the function will either add up the sums of orders, or ignore them. “Payment for goods paid” - this is the criterion that the program will be guided by when deciding whether to take into account this or that payment or not. The value in the cells of the technical column matches this phrase - add, no - skip. "Register of requests for payment"!G14:G39 - a link to all cells containing amounts for requests for payment. Similarly, formulas are introduced for all items, payment for which is carried out on the basis of applications from the heads of departments (see Fig. 7);
- we add to the cash flow budget data on those applications that have not yet been paid. In other words, you need to fill in the column "Amount of payments declared, but not executed as of the current date." The logic will be the same as in the previous case. The difference is that instead of paid applications, it will be necessary to force Excel to select those for which the money has not yet gone. The formula looks like this:
=SUMIF("Register of payment requests"!I14:I57;"Payment for goods";"Register of payment requests"!G14:G39).
Please note that in the selection criteria for applications (“Payment for goods”), there must be a space after the name of the budget item. Remember, when we created the technical column in the ticket register, we added a space character? He must be taken into account. Otherwise, nothing will work.
Now everything is ready. The budget contains data on planned payments for the month, actually made (paid applications) and upcoming (declared, but not executed payments). This information is more than enough to anticipate budget overruns in advance. By the way, as applications are added to the registry (or their status changes), thanks to the “SUMIS” function, the data will instantly change.
Automatically formatted tables and reports
When it is necessary to add rows and columns to the table (enter additional income and expense items, new product names, etc.), each time you have to repeat the entry of formulas, make sure that cell references do not float, correct drop-down lists, redefine the ranges of the original data for pivot tables. An elegant solution would be to create a dynamic named range that will automatically adjust to accommodate newly added rows and columns.
important place in modern statistical analysis socio-economic phenomena and processes takes a graphical method. Not a single statistical study can do without graphs - they make it possible to identify patterns in the development of a phenomenon and its structure with the least amount of time, as well as visualize the relationship of indicators. A graphic image is often more visual and understandable than many pages of text. The arsenal of graphs used in statistics is extensive. Moreover, with the emergence of new software tools it is constantly increasing: 2D charts are being replaced by volumetric, matrix, categorized charts and pictographs.
A graph is a schematic representation of statistical information using various geometric images, which can be lines, points, planar or volumetric figures (circles, rectangles, etc.), symbols with many elements (stars, rays, polygons, "Chernov faces" , "Japanese candles", "boxes with a mustache", etc.).
Any statistical chart contains a graphic image and auxiliary elements. A graphical image is understood as a set of lines, figures, points or symbols selected for displaying specific statistical information, having a certain image format. Auxiliary elements of the graph are, firstly, the graph field (the space on which the geometric image is located, while the length and width of the graph field, as a rule, have a certain relationship), and secondly, coordinate system and scale landmarks (Cartesian, polar coordinates, contour lines or grids with a scale scale applied to them), and, thirdly, chart explication, which is the necessary explanatory text attached to the chart: its name, scale signatures, semantic content applied symbols and signs (chart legend).
Statistical graphs can be classified according to the following criteria:
- analytical purpose;
- construction method;
- geometric symbols.
According to the analytical purpose, there are comparison graphs, structures, dynamics, images of variational series, graphs of the relationship of indicators.
According to the method of constructing graphics, they are divided into diagrams and statistical maps.
According to the symbols of the geometric image used, graphics are dotted, linear, figured (planar or volumetric) and pictographic.
To compare indicators of the same name relating to different time periods, objects or territories, line charts and different kinds diagrams: bar, strip, curly; as well as pictographs.
In a linear graph, along the abscissa axis, time periods, objects or territories are marked, and along the ordinate axis - the corresponding values of the indicator under consideration. For example, according to Table. 4.10 we will build a linear graph of changes in the proportion of unprofitable organizations for the period 2002-2006. for the economy as a whole (Figure 4.1).
| Branch of the economy | Year | ||||
|---|---|---|---|---|---|
| 2002 | 2003 | 2004 | 2005 | 2006 | |
| Total in the economy, including: | 53,2 | 40,8 | 39,8 | 37,9 | 43,5 |
| industry | 48,8 | 39,1 | 39,7 | 39,3 | 45,1 |
| Agriculture | 84,4 | 52,7 | 50,7 | 46,3 | 55,6 |
| construction | 40,6 | 37,7 | 37,2 | 35,4 | 38,6 |
| transport | 53,4 | 47,9 | 44,1 | 40,9 | 45,6 |
| connection | 44,3 | 28,4 | 26,1 | 25,4 | 35,1 |
| trade and catering | 45,3 | 32,7 | 31,4 | 27,7 | 31,2 |
Rice. 4.1.
The bar chart has the same analytical meaning as the line chart. When it is built on the X-axis, there are elements to be compared, which can be time periods, territories, or objects. They are at the same distance from each other. Then rectangles (columns) are drawn: the side, which is the width, is the same for all compared elements and is located on the X axis, the height of the rectangles is plotted along the Y axis in proportion to the value of the compared indicator. Thus, the Y-axis must have a certain scale, necessarily starting from zero. Thus, using the data in Table 4.10, we will build a bar chart of changes in the share of unprofitable enterprises and organizations throughout the economy as a whole (Fig. 4.2).
Bar chart rectangles can also be located close to each other - the distance between them is determined arbitrarily, only the height of the rectangles has a scale.
Rice. 4.2.
Bar charts can simultaneously show the change in several indicators. For example, let's depict the dynamics of the share of unprofitable enterprises and organizations by sectors of the economy for several time periods (Fig. 4.3).
Rice. 4.3.
For four industries, we will construct a line graph (Fig. 4.4).
Rice. 4.4.
A strip (bar) chart is built according to the same rules as a bar chart, but the rectangles depicting the size of the indicator are not located vertically, but horizontally. This type charts are convenient to use in cases where the compared indicators can take negative values. For example, the children's clothing store "Sashenka" during the year not only had a profit (+), but also incurred losses (Fig. 4.5).
Rice. 4.5.
Various geometric figures can also be used to obtain comparison diagrams. Suppose that the number of concluded personal insurance contracts concluded by an insurance company was 23 thousand in 2003, and 64 thousand in 2004. Let us represent these data graphically, for which we will choose a square as a curly sign. To find the sides of the squares, you need to extract the square roots from the values of the indicators: Let's select the image scale, for example, let's take 1 cm equal to 3 thousand. Then the side of the first square will be equal to (4.8: 3) 1.6 cm; the second (8: 3) 2.7 cm. So, we get the following comparison diagram (Fig. 4.6).
Rice. 4.6.
Circles are often used instead of squares. Then the displayed values should be proportional to the area of the circle. The visibility of this type of diagrams is the greater, the more the compared indicators differ from each other. Indeed, if the differences are small, then such a graph loses its meaning.
In dynamic comparisons, especially if the data is given by months of the year and there are so-called seasonal fluctuations in them, radial charts are used. To do this, a circle is drawn with such a radius that when a scale scale is applied to it, the upper value of the scale corresponds to the largest value of the indicator. Then the whole circle is divided into 12 parts (if we consider monthly data) and numbers or names of months are put down near each radius. After that, the values of the indicators of the corresponding months are plotted on them in the accepted scale, and the obtained points are connected by segments - a closed broken line is formed. An example of constructing a radial diagram is shown in fig. 4.7.
Rice. 4.7.
Rectangular or pie charts are used to depict the structure of a phenomenon.
Let's demonstrate the construction of a circular pie chart on the data of Table. 4.11.
In order to build a pie chart, you need to determine the angles of the sectors: 100% corresponds to 360°, then 1% is equal to 3.6°. Let's recalculate our data:
- dwellings: 12.8 * 3.6 \u003d 46 °;
- buildings (except residential) and structures: 41.9 * 3.6 \u003d 151 °;
- machines, equipment, vehicles: 38.9 * 3.6 = 140°;
- other types of fixed assets: 6.4 * 3.6 = 23°.
Let's draw a circle of arbitrary radius and divide it into four corresponding sectors (Fig. 4.8).
Rice. 4.8.
You can also graphically depict the structure of a phenomenon using strip (strip) diagrams. In this case, a rectangle of arbitrary length and width is drawn. The value of its length is taken as 100%. Then the rectangle is divided into parts corresponding to the values of the shares of those components that make up the phenomenon. So, according to Table. 4.10 we get the bar graph shown in fig. 4.9.
Rice. 4.9.
For the simultaneous display of three quantities, one of which is the product of the other two, a special graph is used, called the Varzar sign. Let us explain the procedure for its construction using a conditional example.
The Varzar sign has the form of a rectangle, the length and width of which correspond to two multipliers of the product, and the area corresponds to the value of the product, etc. third magnitude. So, in table. 4.12 The "Capitalization" indicator is calculated as the product of the market value of a share by the number of shares of this type:
We will take the base of the rectangles as the quantity indicator, and the height as the price. Then the area of the resulting rectangles will represent the capitalization. When constructing the signs of Varzar, it should be remembered that the base and height of the rectangles are plotted in their scale independently of each other (Fig. 4.10).
Rice. 4.10.
A special place in the graphical analysis of financial information is occupied by exchange statistical charts.
To analyze the data of the stock, commodity and futures markets, bar stock charts are most often used (Table 4.13).
| Trading date | Opening price | Maximum price of the day | Low price of the day | Closing price | Trading volume |
|---|---|---|---|---|---|
| 26 | 14,3 | 14,9 | 14,3 | 14,7 | 102 548 |
| 27 | 14,7 | 15,2 | 14,6 | 14,9 | 112 054 |
| 28 | 14,9 | 15,5 | 14,5 | 15,3 | 136 250 |
| 29 | 15,3 | 16,1 | 14,9 | 15,1 | 108 914 |
| 30 | 15,1 | 15,8 | 14,7 | 15,6 | 103 145 |
According to Table. 4.13 let's build a bar stock chart (Fig. 4.11).
A vertical line (column) is built on a bar stock chart for each day: the beginning of the bar corresponds to the value of the minimum price per share during the day, the top - to the maximum price, the horizontal line on the column - the price at the close of trading.
Rice. 4.11.
For the simultaneous display of the prices of opening and closing trades, as well as the minimum and maximum values of the price, a chart is used, often called "boxes with a mustache" in the literature. For the data in Table. 4.13 it looks as shown in fig. 4.12.
Rice. 4.12."Moustache Boxes"
Here, in contrast to the graph shown in Fig. 4.11, each column also has a "box" (hence the name - "boxes with a mustache"). The base of the white "box" corresponds to the opening price of trades, the height - to the closing price; the black color of the "box" means that the closing price was lower than the opening price of trading - in this case, they are reversed on the chart.
The bar graph can be supplemented with trading volume indicator charts. For the data in Table. 4.13 we will receive the graphic image presented on fig. 4.13.
Rice. 4.13.
Since the chart is complemented by charts showing trading volumes, it has two vertical scales: on the left is a scale for trading volume, on the right - for stock quotes.
The box-and-whisker chart can also be supplemented with trading volume charts (Figure 4.14).
Rice. 4.14. Stock chart "boxes with a mustache" with a chart of trading volumes
In modern statistical packages application programs for the graphical presentation of statistical information, a special type of graphs - pictograms - is proposed.
Pictographs are compiled for each observation, they have the form of graphic objects (certain symbols) with many elements. The indicator values correspond to the properties or sizes of the pictographic elements. With a change in the values of indicators when moving from one unit of observation to another appearance pictogram changes. Thus, it becomes possible to visually classify observations into homogeneous groups.
Suppose that there is a set of 10 industrial enterprises characterized by the following indicators (Table 4.14).
| Company number | Profitability, % | Share of workers in industrial production personnel, % | Equipment shift ratio | Share of losses from marriage, % | Return on assets for 1 rub. funds | Average annual number of industrial production personnel, pers. | Average annual cost of fixed production assets, mln. rub. | Turnover of normalized working capital, days | Turnover of non-standard working capital, days | Non-production expenses, million rubles |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 13,28 | 0,80 | 1,14 | 0,27 | 1,07 | 1 257 | 50,79 | 80,12 | 22,46 | 18,20 |
| 2 | 22,31 | 0,80 | 1,85 | 0,38 | 2,45 | 1 687 | 58,12 | 80,12 | 22,37 | 38,45 |
| 3 | 15,27 | 0,78 | 1,14 | 0,26 | 1,14 | 1 588 | 44,20 | 80,45 | 21,74 | 22,13 |
| 4 | 12,99 | 0,79 | 1,33 | 0,28 | 1,05 | 1 696 | 44,57 | 68,17 | 20,11 | 24,56 |
| 5 | 25,78 | 0,78 | 1,74 | 0,29 | 2,12 | 1 804 | 51,43 | 70,82 | 20,37 | 46,75 |
| 6 | 28,47 | 0,79 | 1,90 | 0,30 | 2,09 | 1 512 | 53,96 | 73,47 | 21,38 | 38,16 |
| 7 | 12,97 | 0,80 | 1,16 | 0,35 | 1,03 | 1 499 | 57,58 | 76,12 | 21,52 | 24,58 |
| 8 | 23,47 | 0,81 | 1,86 | 0,32 | 2,11 | 1 403 | 65,34 | 78,77 | 23,58 | 41,78 |
| 9 | 10,47 | 0,81 | 1,17 | 0,33 | 0,87 | 1 451 | 59,34 | 81,42 | 22,47 | 22,79 |
| 10 | 13,58 | 0,82 | 1,23 | 0,32 | 0,97 | 1 327 | 57,83 | 84,07 | 23,17 | 22,47 |
Let's analyze the available information graphically using pictographs.
Loading...