Moscow Institute

international economic relations

Speciality

"Accounting, analysis and audit"

Course work

by discipline

"Management analysis in industries"

Topic: "Analysis of the dynamics and implementation of the retail turnover plan"

Work completed

correspondence student

Rusanova Tatyana Nikolaevna

Checked ___________________

(academic degree, academic title, surname, initials)

Grade _________________

Moscow, 2008

Introduction

Trade as an independent branch of the country's economy is a set of organizations and enterprises involved in the sale and purchase of consumer goods. In practice, it is represented by two types - wholesale and retail trade. Retail trade plays a crucial role in economic activity, since it is here that the process of circulation of goods ends, and they pass into the sphere of personal consumption. So, retail trade is the sale of goods directly to the population for personal consumption.

The main quantitative indicator that allows you to evaluate the volume of work of a retail enterprise is retail turnover, which, in fact, is the sum of sales of goods to buyers for personal consumption. Currently, the results of the organization's activities are also evaluated by the amount of profit, profitability. At the same time, the turnover indicator is the basis for calculating all estimated indicators (gross income, profit, cost level, profitability, etc.). The results of economic activity of trade enterprises largely depend on its dynamics. The growth of turnover entails an increase in gross income, and therefore, the profit of the enterprise. To ensure the growth of the company's turnover, it is necessary, among other things, to properly organize economic control and analysis.

The main objectives of the analysis of the activity of trade enterprises are to control the dynamics of the implementation of the plan of trade, the study of the factors that influenced its volume and structure, the identification of reserves for further expansion of trade. At the same time, it must be remembered that the analysis of the sale of goods should be linked to the analysis of commodity supply, inventory and turnover of goods, since all these factors have an impact on the volume and structure of trade.

Sources for analyzing the turnover of a trading enterprise are a business plan (which provides for indicators of the sale of goods, inventory and receipt of goods), as well as accounting and statistical reporting. In such documentary forms as No. 1-enterprise "Basic information about the activities of the enterprise" (annual) and No. 5-z "Information on the costs of production and sale of products" (quarterly) contains information on the volume and composition of the turnover, on the balance of goods on beginning and end of the reporting period.

The purpose of this work is to give an idea of ​​how the analysis of turnover in a retail trade organization is practically carried out and, accordingly, how the resulting analytical indicators are used for its further successful development.

1. Tasks and objectives of the analysis of retail turnover

Retail turnover includes the sale of goods to the population for cash and on credit, as well as the sale of goods to collective consumers (enterprises, organizations, institutions) in the form of small wholesale.

TO tasks of analysis of retail turnover include: control over the implementation of the plan of retail turnover in terms of total volume and product groups; assessment of the implementation of the plan for enterprises and organizations included in this system; study of the rhythm of the implementation of the plan of retail turnover both in the system as a whole and in organizations and enterprises; studying the dynamics of retail turnover over a number of years; identification of changes that have occurred in the volume and structure of trade, commodity stocks and the receipt of goods; determination of the degree of coverage of purchasing funds of the population served by a given trade organization or enterprise; revealing and studying the reasons contributing to the fulfillment or, on the contrary, non-fulfillment of the turnover plan; study of the quality of public service; identifying internal reserves and developing measures to eliminate the identified shortcomings, further increase trade and improve public services.

For the analysis of retail turnover, the following are used: data on the socio-economic indicators of the area where organizations and enterprises operate (number and structure of the population, their monetary and purchasing funds, etc.); plans drawn up by the organizations and enterprises themselves; accounting and operational reporting data; primary documents, commodity-money reports and accounting registers reflecting the movement of goods; inventory lists of goods; acts of documentary audits, surveys.

The analysis is carried out by calculating various indicators, which can be grouped according to the following criteria: cost and natural, quantitative and qualitative, volumetric and specific.

Especially widely used in economic analysis cost indicators. The most important such indicator is the volume of trade turnover of enterprises and organizations.

In terms of value, the sale of goods in assortment, by forms and methods of trade, by groups of buyers, and so on, is also determined. Cost indicators are main when evaluating the implementation of the plan of retail turnover.

To characterize the retail turnover of certain aspects of the activities of trading enterprises, deepening the analysis are applied natural indicators. Recently, many accounting programs have appeared that allow you to keep records, both in sums and in physical terms, which greatly facilitated the use of these indicators in the analysis.

Quantitative indicators are used to characterize certain aspects of the activity of a trade enterprise and organization. These include: the volume of retail turnover of individual stores, trade enterprises and organizations; balances of goods at the beginning and end of the period; receipt of goods.

Important in the analysis are qualitative indicators, work of trade enterprises. These include: the percentage of implementation of the plan of retail turnover; indicators of its rhythm and dynamics; application of progressive forms and methods of serving the population; coverage of monetary incomes and purchasing funds of the population by retail trade, and so on.

Volumetric and specific indicators are widely used in the analysis of retail turnover. TO volume indicators refers to retail turnover in the system as a whole, and to specific- the level of turnover of individual enterprises and organizations in the total volume of turnover, turnover per seller, per square meter, turnover per capita, etc.

It is advisable to carry out in a certain logical sequence. So, first, the main indicator is analyzed - the total amount of sales of goods in the retail network. Further, the state of trade in individual groups of goods, the composition of turnover, the degree of satisfaction of demand for specific goods are studied. Then, an analysis of stocks, the speed of circulation of working capital and the receipt of goods is carried out. The final stage is the mutual linking of the results of the study of all indicators of trade turnover on the basis of a comprehensive analysis.

A specific analysis scheme is developed at each trading enterprise, taking into account the goals set and the availability of the necessary information.

In practice, control and analysis of sales of products is carried out for each month, quarter, half year, year. Actual data are compared with planned and previous periods. At the same time, such indicators are calculated as: the percentage of the plan; absolute deviations from the plan; growth (decrease) rates for the analyzed period; the absolute value of one percent increase (decrease) in sales.

To study retail trade turnover indicators, models of analytical tables are developed that contain the necessary planned and actual data for a number of periods, various normative and calculated indicators, and relative values ​​of an analytical nature. With the use of computer technology, all these materials are gradually accumulated and calculated in an automated mode, which speeds up the process of preparing analytical tables and making operational management decisions to eliminate the shortcomings identified in the analysis process, to use reserves to improve trade and increase its efficiency.

Analytical materials are also used in the preparation of planned calculations for the coming period. Given the fact that by the time the plans (forecasts) are developed, there are still no actual data for the current period (quarter, year), it becomes necessary to determine the expected (probable) amount of turnover for the full current period (goal, quarter). At the same time, analysis data for the past period and prospects for the development of trade in the coming period should be taken into account. The planned rates of its development in the planned period depend to a certain extent on the validity of the estimate of the expected turnover.

2. Components of the analysis of retail turnover

So, the analysis of the turnover of a retail trade enterprise begins with a study of the total volume of turnover, during which it is necessary to establish the degree of fulfillment of the plan for the sale of goods, determine the dynamics of turnover, and check its implementation for individual quarters and months. For example, let's take data on the dynamics and implementation of the turnover plan for a particular retail enterprise - the Econom grocery store over the past two years - and based on the table below, we will formulate the appropriate conclusions.

Dynamics and implementation of the retail turnover plan

The table shows that the retail turnover plan for the reporting year was fulfilled, although the percentage of overfulfillment is very small - only 0.11%; compared to the previous year, the volume of retail trade increased by 11.39%. If we look at the data on a quarterly basis, we will see that the implementation of the plan was observed only in the 1st and 4th quarters, and in the 2nd and 3rd quarters the plan was not fulfilled. Quarterly indicators of trade turnover growth differ significantly compared to the previous year. To find out the reasons for this, it is necessary to supplement this analysis with a study of the turnover by months.

The demand of the population for consumer goods is formed under the influence of many factors, in particular seasonal characteristics. For example, the sale of ice cream and soft drinks increases significantly during the summer months. Purchases of individual goods are increasing before the holidays, in the process of preparing for the new school year, and so on. Therefore, a deep analysis of intra-annual fluctuations in the turnover for the sale of goods by months and quarters, and in the process of operational analysis - by weeks is of great importance for identifying reserves for improving the quality of trade services.

Irregularity of turnover in the sale of goods by periods of the year can be explained by many reasons of an objective and subjective nature. Objective factors include cash incomes and demand of the population, which are formed for individual months, the number of days in a particular month. Subjective factors are poor study of trade conditions and seasonal characteristics of demand, irregularity in the supply of goods, lack of service, etc.

In particular, based on the foregoing, it can be assumed that the non-fulfillment of the turnover plan in the II and III quarters in the store we are considering could be due to the fact that it was at that time that a similar self-service grocery store opened nearby and some of the customers went there; in addition, in previous years (as observation shows) in the third quarter, there was a drop in revenue - since potential buyers at this time save money first for vacations, then for preparing children for school. The fourth quarter always “wins” due to the approaching New Year.

An important section of the analysis is the study composition of retail trade in terms of its types, organizational forms, methods of trade and assortment.

Retail turnover by type It is subdivided into the sale of goods to the population and the sale of goods to enterprises (organizations, institutions) in the form of small wholesale. Usually, in percentage terms, the share of sales of goods to the population in retail turnover is on average 95% (respectively, about 5% is sold in small wholesale), but in some trade organizations, small wholesale sales can significantly exceed the average.

Retail turnover analysis organizational forms means the study of equity participation in its implementation of various stationary enterprises (shops, tents, etc.). This subsection of the analysis can be carried out only on the basis of accounting and operational accounting, since there is no such information in the current reporting.

Retail turnover analysis by trading methods implies its study with a division into the sale of goods for cash and on credit, the sale of packaged and bulk goods, the sale in the usual way and through automatic machines, the sale of samples, etc. Thus, the data on sales on credit contained in the current accounting data and primary documentation make it possible to analyze the movement of a consumer loan, monitor its coverage and identify overdue debts. And, for example, the analysis of the turnover of goods with its division into the sale of packaged and non-packaged goods, despite the great importance of such an analysis, is practically not carried out due to the lack of the necessary planning and reporting data. Often, the analysis of the share of packaged goods in the store's turnover can be carried out only on the basis of sample data, for which the commodity documentation associated with the receipt of piece goods and inventory materials for packaged goods are processed.

Retail turnover analysis by commodity composition is directly related to meeting the diverse needs of customers. Fulfillment of the turnover plan for the assortment indicates the ability of sales workers to take into account and satisfy consumer demand. The commodity structure of retail trade turnover is analyzed on the basis of accounting data containing information on the sale of goods at retail in the context of the range.

Implementation of the plan for product groups

Name of commodity groups	Plan		Report		% plan completion
	in thousand rubles	in % of the total	in thousand rubles	in % of the total
Wine products	1763,5	10,0	2171,6	12,30	123,1
Food	15165	86,0	14717	83,36	97,0
Cigarettes	529,10	3,0	617,9	3,49	116,8
Related manufactured goods	176	1,0	150,05	0,85	85,3
Total	17635	100,00	17655	100,00	100,11

Based on the table above, we can conclude that, although the overall plan for retail turnover is 100.11% fulfilled (especially good in this regard, things are going on with wine and vodka products and cigarettes), but in food products, which is what store, the plan is not fulfilled; the same applies to manufactured goods (although they occupy a very small place in the structure of the turnover of this store). Therefore, it is necessary to analyze the reasons for this situation, identify weaknesses in order to draw the attention of the management to them.

Using the current reporting, it is possible to study the structure of trade turnover only in the context of enlarged commodity groups (the following aggregated groups of goods can be distinguished as the macrostructure of turnover: food, alcoholic beverages and non-food products).

One of the important analytical indicators in the study of retail turnover is - dynamics of sales of the main groups of goods in actual and comparable prices. To do this, it is necessary to have information about the retail price index for individual product groups, since commodity prices will change differently. Statistical observation covers about 100 commodity groups, which makes it difficult to study the turnover for each group of goods. When choosing for the analysis of the main groups, as a rule, they are guided by the following points:

The study is conducted on commodity groups that determine the development of trade, that is, those that occupy a dominant role in turnover (about 80%);

Particular attention is paid to the analysis of the sale of socially significant and so-called essential goods (bread, flour, cereals, sugar, tea, salt, fabrics, detergents, etc.).

For example, the turnover for the sale of poultry meat for the organization for the previous year amounted to 369 thousand rubles, for the reporting year - 446 thousand rubles. The price index for the analyzed period for this group of goods is 1.17. Then the turnover in comparable prices for the poultry meat group is 381 thousand rubles. (446 thousand rubles / 1, 17).

The dynamics of turnover in actual prices for this group of goods is 120.9% (446 thousand rubles * 100% / 369 thousand rubles), and in comparable prices - 103.25% (381 thousand rubles * 100% / 369 thousand rubles). This means that the amount of turnover in the Poultry Meat group increased by 20.9%, while its sales volume increased by only 3.25%. Consequently, to a large extent, the rise in commodity prices could be the reason for the growth in trade turnover.

Of analytical and practical interest is the study of retail prices for goods sold by various trading enterprises. Such an analysis allows you to identify the competitiveness of this organization, make adjustments to its pricing strategy aimed at increasing its market share of individual products. Price changes are one of the factors determining the structure of trade turnover. For example, as a result of an increase in retail prices, there is a decrease in the financial situation of the population and an increase in the share of food products in the retail trade turnover.

For the most complete satisfaction of customer requests, as well as for the correct formation of industry orders, it is also necessary to analyze the intra-group assortment (analysis for individual goods). The study of the latter is carried out, first of all, for those groups for which the implementation plan is not being fulfilled, or there are complaints from buyers about a lack, poor assortment, and poor quality of goods. Analysis of the intra-group assortment is carried out on the basis of inventory data and primary documents. The use of modern cash registers and computer technology for accounting for the receipt, sale and stocks of goods greatly facilitates the study of goods by item.

When the money income of the population changes, the amount of purchases of individual consumer goods changes. To assess such changes, the indicator is used turnover elasticity, which represents its relative change under the influence of changes in the monetary income of the population, prices, volume, structure of the product offer and other factors. Turnover elasticity coefficient on the sale of individual goods depending on changes in the monetary income of the population shows how the amount of turnover changes with a change in cash income by 1%. The indicator is calculated as the ratio of the growth rate of turnover for the sale of certain groups of goods to the growth rate of monetary income of the population for the analyzed period. In practice, trade organizations do not always have the necessary information about the monetary incomes of the population they serve. In the absence of such information, indicators of the total volume of retail trade turnover can be used for calculation.

The positive value of this coefficient indicates that there is a direct relationship between the factor attribute and the turnover for the sale of goods. A negative value, therefore, reflects the existing feedback between the studied indicators. An elasticity coefficient of more than one means that the turnover for the sale of this product is very elastic; with weak elasticity, the coefficient of demand or turnover for the sale of this product is less than one.

So, the coefficient of elasticity is positive and more than one for those goods for which the growth rate of sales is higher than the rate of development of the total volume of trade. The share of such groups of goods in turnover is increasing. The elasticity coefficient is positive and less than one in the case when the growth rate of sales of certain goods is lower than the growth rate of the entire turnover. The share of these groups of goods in the total turnover is decreasing. And, finally, the coefficient of elasticity takes a negative value for those goods, the sale of which is declining.

3. Factors affecting the volume of retail trade

Factors that affect the volume of retail trade can be divided into three groups:

Factors related to commodity funds;

Factors related to the number of employees and their productivity;

Factors related to the use of fixed assets.

Let's take a closer look at each of these groups.

3.1. Factors related to the use of commodity funds

Revenue from the sale of goods in the retail trade network is the main indicator of trading activity, which is closely linked to other indicators of trade turnover that affect its volume through changes in the values ​​of stocks of goods at the beginning and end of the reporting period, receipt of goods and other disposal of goods. The relationship between these quantities is expressed commodity balance formula:

Znp + P \u003d R + PV + Zkp, where

Znp, Zkp are stocks of goods at the beginning and end of the reporting period;

P - receipt of goods;

R - retail turnover;

PV - other disposal of goods.

Commodity resources are reflected on the left side of the balance sheet, and the need of a trade enterprise for goods is reflected on the right side. All these indicators (sales, stocks, receipts) are analyzed in a comprehensive manner, since they are closely related to each other. For example, an increase in inventory can lead to a decrease in the amount of goods sold. An increase in receipts may lead to an increase in sales or an increase in inventory, which is differently evaluated in terms of the competitiveness of the enterprise and the effectiveness of trading activities.

When compiling a commodity balance, the so-called other disposal of goods, which reduces the resources that can be sold, is necessarily taken into account. For example, for some goods during their transportation, storage and sale, a natural loss is revealed; or in the process of trading, it becomes necessary to reduce prices for individual goods if the demand for them decreases for various reasons (fashion has changed, supply exceeds demand, competitors sell goods at a lower price, etc.).

Based on the balance of goods, we can deduce the following the formula for the amount of retail turnover: R \u003d Znp + P - PV - Zkp .

A change in each component of this formula entails a change in the value of retail turnover. In order to determine the impact of any of the terms on the amount of sales of goods, you can use the chain substitution method or the method of absolute differences between planned and actual indicators.

Based on the initial data and the trade balance formula, we determine the impact on the value of retail sales of changes in the initial stocks of goods, the receipt of goods, their other disposal, and final stocks.

Commodity balance of the organization for the reporting year, thousand rubles.

Components of the trade balance	Plan	Report
Stocks of goods at the beginning of the year (Znp) Goods receipt (P)
Total income with the remainder (Znp + P) Sale of goods at retail and small wholesale (R) Other disposal of goods (RO)
Total consumption (P + PV) Inventories of goods at the end of the year (Zkp)
Total in consumption with the balance (R + PV + Zkp)	18780	18875

Influence of the terms of the trade balance on the volume of retail trade turnover, thousand rubles.

Factors of influence on the change in the volume of retail trade	Plan	Report	Deviation (+, -)
Starting stock of goods (W 1 – Z 0 )	995	1225	+230
Receipt of goods (P 1 - P 0 )	12500	12735	+235
Other disposal of goods (RO 1 – PV 0 )	45	70	- 25
Ending stock of goods (W 1 – Z 0 )	1100	1150	- 50
Retail turnover	17635	17655	+20

The data in the tables allow us to conclude that the increase in retail turnover at this trading enterprise occurred, firstly, due to the available excess stocks of goods at the beginning of the period (by 230 thousand rubles); secondly, due to an increase in the receipt of goods in comparison with the plan by 235 thousand rubles. Other disposals exceeded the planned one by 25 thousand rubles, as well as by 50 thousand rubles. the final stock of goods increased - these factors negatively affected the turnover of the store.

An analysis of the dependence of the volume of retail trade on commodity funds provides only initial data, but does not explain the reasons that caused changes in stock balances, the receipt and disposal of goods. To find out these reasons it is necessary to study each of these elements in more detail.

Goods receipt analysis

Fulfillment of the plan for the receipt of goods (commodity supply) is the most important factor in the implementation of the retail trade turnover plan and the guarantee of an uninterrupted supply of buyers with the necessary goods.

Commodity resources of trade enterprises, as a rule, are formed due to the receipt of goods under concluded agreements, as well as receipts from local resources (for example, goods produced by local industry; produced by the production departments of the trade enterprises themselves, etc. - while the formation of trade turnover for account of locally produced products is often one of the decisive factors in reducing the retail prices of goods and increasing their competitiveness in the consumer market).

The main share of the commodity supply of retail trade belongs to goods that come under direct contracts with industrial enterprises or wholesale organizations.

The first step in the analysis of the receipt of goods is to check the fulfillment of the plans for the supply of goods both in general and for individual product groups, then the analysis of the receipt of goods by sources and suppliers is already carried out.

When analyzing the implementation of the supply plan, special attention should be paid to the fulfillment by suppliers of supply contracts, the timeliness of shipment of goods, compliance with the quality and assortment of goods. In this case, you can use operational accounting data on the fulfillment of contracts for the supply of products, claims made against suppliers. Accounting data (Form No. 2 “Profit and Loss Statement”) contains information on the amounts of fines received and paid for violation of quality and short supply of goods, these data should also be taken into account in the analysis process.

Analyzing the implementation of the plan for the receipt of goods from local resources, it is necessary to pay attention to how the plan for the release of goods by the production units of the trade organization itself was carried out, as well as what measures were taken to attract local resources into the trade turnover of the organization.

For the normal functioning of a trading enterprise and, accordingly, the fulfillment of its plans for turnover, an uninterrupted supply of goods is necessary, which ensures a uniform process of their circulation, does not allow the formation of excess stocks for some goods and a shortage of others, and helps to satisfy consumer demand. The rhythm of the delivery of goods, their timely entry into the retail network can be checked by analyzing the receipt of goods by months and quarters using operational data. At the same time, a sample of commodity funds is studied, violations in the uniformity of the receipt of goods and the causes of these violations are revealed. If a trade organization is a large economic structure, then the analysis of the receipt of goods must necessarily include data on the implementation of the plan for the supply of goods for all its divisions.

Analysis of inventory and turnover of goods

To ensure the implementation of the plan for the turnover and uninterrupted trade, a trade organization must have a constant stock of goods in a certain assortment and size. Commodity stocks are subdivided into stocks of normal and seasonal storage, as well as special-purpose stocks.

Commodity reserves normal storage serve to ensure uninterrupted trade, therefore, their constant and rhythmic replenishment is necessary. Violation of the terms of delivery of goods leads to interruptions in trade, dissatisfaction with consumer demand, or, on the contrary, to overstocking.

Stocks of seasonal storage- these are either seasonal products, the demand for which is relatively constant throughout the year (for example, vegetables, fruits), or goods, the production of which is constant, but the demand for them changes seasonally (for example, rubber shoes, bicycles).

Special purpose reserves, as a rule, are formed in those cases when, due to various circumstances (for example, the difficulty of importation), trade organizations are forced to form special stocks.

Control over the state and movement of commodity stocks must be systematically carried out in all departments of the trade organization, paying attention not only to their total amount and movement by groups, but also to the state of stocks for individual goods, with the clarification of the reasons for the formation of surpluses or lack of necessary stocks.

Analyzing commodity stocks, it is required to determine their compliance with the volume of trade, to study the structure of stocks and the speed of their turnover. Commodity stocks are planned and accounted for in absolute amounts and in days of turnover. The main indicator in the analysis - inventory in days, which is calculated by dividing the amount of the balance of goods by the one-day turnover (one-day turnover is equal to the turnover divided by the number of days in the analyzed period). This indicator is calculated for the main groups of goods, with special attention to those that occupy a significant place (the largest share) in the total amount of commodity stocks, as well as for goods of seasonal demand.

In the process of inventory analysis, it is necessary to determine the deviation of actual stocks from established standards and initial balances in the amount and in days of turnover, for which a comparison is made of actual and planned indicators in general and for product groups separately.

Such an indicator as commodity turnover, also affects the amount of inventory. The formula for calculating this indicator is as follows: O \u003d W * D / R, where O - turnover in days; Z - average stock of goods; D is the number of days of the analyzed period; P - sales of goods for the reporting period.

To determine the influence of the structure of turnover and the level of turnover of individual product groups on the amount of stocks of goods, you must first calculate the following calculated indicators :

a) the total commodity balance at actual volume trade and maintaining the planned structure and turnover individual product groups;

b) then the commodity balance is calculated at actual volume and actual structure trade with maintaining planned norms turnover for each product group. This average balance will correspond to the turnover in days (c), calculated as follows: the resulting average commodity balance (b) is multiplied by the number of days of the reporting period and divided by the actual turnover. Next, you need to compare the planned and actual turnover of goods according to the available data and analyze due to what factors it changed (due to slowing down or accelerating its level - for which we compare the actual turnover with the calculated indicator (c); or due to a shift in the structure of turnover - we compare the planned turnover with the calculated indicator (c) Also, based on the calculated data on commodity stocks, it is possible to determine the impact on their change of such factors as: an increase or decrease in the volume of turnover; shifts in its structure and level of turnover.

Further, the inventory check is carried out from the point of view of individual assortment groups: it is established whether the delivery of goods is organized correctly, whether there were cases of overstocking or, on the contrary, the absence of the required minimum stock of goods of a certain assortment (in this case, it is important to take prompt measures to replenish stocks - search suppliers, conclusion of supply contracts), slow-moving goods are identified. The condition and level of commodity stocks are a kind of barometer of the state of trade and the degree of balance between supply and demand.

To analyze the composition of commodity stocks at a trading enterprise, inventory records, markdown acts, write-offs of goods and other documents can be used. At the same time, stocks of seasonal storage, early delivery and special purpose should be considered separately, since they have special sources of coverage.

3.2. Factors related to the number of employees and their productivity

A very important indicator for analysis in trade is the average number of employees of a trade organization. Comparing data on the actual number of employees and composition of personnel in the reporting period with planned indicators and data for the previous period, it is necessary to establish the degree of fulfillment / non-fulfillment of the plan for turnover and the change in the number and composition of employees compared to the previous period. It is also necessary to determine how the number of sellers and the ratio in the composition of the staff of the trade organization between them and other employees has changed. A qualitative characteristic of the work of a trade organization is also labor productivity, significant growth reserves of which are the improvement of labor organization, the introduction of progressive forms of trade (for example, automation).

Labor productivity in trade is defined as the sum of the turnover per worker. The indicators characterizing labor productivity are the average turnover per one sales employee and the average turnover per one seller (at the same time, in order to eliminate the impact of price increases on them and ensure comparability of indicators with planned and previous periods, the price index is used in the calculation). To analyze the indicators in this section, the data of the business plan, forms No. 1-enterprise and No. 1-t of statistical reporting are used.

Dynamics of the implementation of the plan of indicators for labor

Indicators	Last year	Reporting year		Deviation (+,-) from last year		Deviation from the plan
		Plan	Fact	sum	%	sum	%
The volume of retail trade turnover, thousand rubles.	15850	17635	17655	+1805	+11,39	+20	+0,11
Average headcount of all employees, pers.	22	22	21	- 1	- 4,55	- 1	- 4,55
Including:
Sellers, pers.	12	12	11	- 1	- 8,33	- 1	- 8,33
The share of salespeople in the total number of employees, %	54,55	54,55	52,38	- 2,17	X	- 2,17	X
Average annual turnover per trade employee, rub.	720455	801591	840714	+ 120259	+16,69	+39123	+4,88
Average annual turnover per seller, rub.	1320833	1469583	1605000	+ 284167	+21,51	+135417	+9,21

These tables clearly show that, despite the fact that the number of sales employees decreased by 4.55% compared to the planned indicators (including salespeople by 8.33%), the trade organization exceeded the plan for turnover of 20 thousand rubles . (+0.11% to the plan). This result was obtained due to the growth of labor productivity. The average annual turnover per sales employee increased in absolute terms by 39,123 thousand rubles. (4.88%); and for one seller - by 135417 thousand rubles. (9.21%). It can be assumed that if the staff structure had not deteriorated (ie, the share of sellers had not decreased in comparison with the plan), then the store could have achieved much better results in terms of retail turnover growth.

After the dynamics of labor indicators has been clarified, it is also necessary to study the impact on the volume of trade turnover of other labor factors, for example, the availability of labor, the structure of the organization's personnel, and the growth of labor productivity. The dependence of the volume of retail turnover on labor factors can be expressed by the formula:

Q = R * r / R * Q / r ,

where Q is the volume of retail trade turnover;

R is the number of all trade workers;

r is the number of sellers. In order to show the economic meaning of each factor, we transform the formula into the following form:

Q = R * K r * W r ,

where K r - share of sellers;

W r - average annual turnover per seller. Using the method of chain substitutions, we present the algorithm for calculating the influence of labor factors on turnover as follows:

R 0 * K r 0 * W r 0 → R 1 * K r 0 * W r 0 → R 1 * K r 1 * W r 0 → R 1 * K r 1 * W r 1.

22* 0,5455 * 1320833→ 21* 0,5455 * 1320833→ 21* 0,5238 * 1320833 → 21* 0,5238 * 1605000 = 15850→ 15131→ 14529 → 17655.

As a result, the influence of each factor on the volume of trade is as follows:

1) The impact of a decrease in the number of employees: 15131 - 15850 = - 719 thousand rubles.

2) The impact of the deterioration in the structure of personnel (decrease in the share of sellers): 14529 - 15131 \u003d - 602 thousand rubles.

3) The impact of increasing the productivity of sellers: 17655 - 14529 = + 3126 thousand rubles.

Deviation balance - 719 - 602 + 3126 = + 1805 thousand rubles.

The calculated data only confirm the conclusions made above about the impact of labor productivity growth on the indicators of an increase in the volume of trade turnover of a given trade enterprise.

3.3. Factors related to the use of fixed assets

In addition to other factors we have considered, the growth of retail turnover, and, consequently, the improvement of public services directly depends on the equipment of the organization of trade in fixed assets and the efficiency of their use.

The structure of the fixed assets of a retail trade enterprise is quite specific and in the traditional version was determined by the peculiarities of the technological process in this area, the widespread use of human labor, and a low level of mechanization and automation. However, in the last decade, networks of various supermarkets with a complex structure of shopping centers have been actively developing, which requires large capital investments to create modern shopping and warehouse complexes, automated accounting and inventory management systems, modern systems for servicing the population with various goods, etc. In order to characterize the efficiency of the use of fixed assets, such indicators are used as: capital productivity, capital intensity, profitability of fixed assets. When calculating these indicators, the cost of both own and leased funds is taken into account.

return on assets in trade it is defined as the ratio of turnover to the average annual value of fixed assets at original or replacement cost. capital intensity is the inverse of return on assets. Profitability of fixed assets- this is the ratio of the amount of profit before tax or net income to the average annual value of fixed assets. All of the above indicators characterize the quality of the use of fixed assets and show: the amount of turnover per unit of funds invested in fixed assets - return on assets; the cost of fixed assets per unit of turnover - capital intensity; the amount of profit per unit cost of fixed assets - profitability.

In addition to the above, in retail trade, other indicators can be used to assess the efficiency of the use of fixed assets: load per 1 m² of retail space (the ratio of turnover to the total area of ​​​​the trading floor); the ratio of the installation area occupied by the equipment to the area of ​​the trading floor (the ratio of the area of ​​display of goods to the area of ​​the trading floor), etc.

For analysis in this context, you can use the data of the business plan, form No. 2 “Profit and Loss Statement”, form No. 1-enterprise “Basic information about the activities of the enterprise”, form No. 11 “Information on the presence and movement of fixed assets (funds) and other non-financial assets, as well as accounting.

It is possible to achieve an increase in trade turnover, both due to the growth of retail space, and with their more rational use. The increase in retail space is expressed by the number of square meters or the number of added jobs. Therefore, the influence of this factor on the growth of retail turnover can be found by knowing the number of newly created jobs and the sum of the average turnover. To do this, the following formula can be applied, linking several indicators:

T = RM * D * t * W hour, where

T - the volume of retail trade;

RM - the number of jobs;

D - the number of working days;

t is the length of the working day;

W hour - average hourly turnover per one workplace.

Using the method of differences in absolute values, we present the procedure for calculating the influence of the above factors on the volume of trade in this way:

∆T = ∆RM * D 0 * t 0 * W 0 hour + ∆D * RM 1 * t 0 * W 0 hour + ∆ t * RM 1 * D 1 * W 0 hour + ∆ W hour * RM 1 * D 1 * t 1.

Using this algorithm, it is possible to determine the impact on the growth or decrease in the volume of trade of each of the factors separately: the impact of changes in the number of jobs ∆RM ; number of working days ∆D ; the duration of the working day ∆t and, finally, the impact of changes in the average hourly turnover per one workplace ∆W hour.

Conclusion

The turnover of retail enterprises is one of the main indicators of the economic and social development of the country and its individual regions, since it reflects the process of exchanging goods for money and represents the final stage in the movement of goods. Retail turnover reflects the processes taking place in the volume and structure of consumption of material goods.

The development of retail trade turnover, its volume and structure in the aggregate is influenced by many factors related both to the demand of the population and the product offer, and to the organization of trade processes. In a market economy, commercial trade organizations are primarily interested in making a profit, which directly depends on an increase in the amount of proceeds from the sale of goods. Revenue is closely related to such indicators of trade turnover as stocks of goods at the beginning and end of the year, other disposal of goods, receipt of goods. All these indicators are in balance, depend on each other and are analyzed in a complex way.

In the process of analysis, an assessment is made of the influence of various factors - the material and technical base, market conditions, the speed of circulation, the rhythm of purchases of goods, the number and composition of employees - on the development of turnover. A specific analysis scheme is developed by each trading organization independently, taking into account the goals set and the availability of the necessary information.

Based on the results of the analysis, managerial decisions are made aimed at improving the structure of trade turnover, the assortment composition, improving the sources of supply of goods, strengthening control over the rhythm of the importation of goods, taking measures for the operational maneuvering of resources, more rational use of labor resources and retail space, etc.

Literature:

1. Basovsky L.E. Theory of economic analysis. M., 2006.

2. Gilyarovskaya L.T. Complex economic analysis of economic activity. M., 2006.

3. Grebnev A.I. The economics of a commercial enterprise. M., 2004.

4. Kovalev V.V., Volkova O.N. Analysis of the financial and economic activities of the enterprise. M., 2007.

5. Korotkova T.L. Commercial activity. Under the editorship / d.e.s. Moiseeva N.K. M., 2007.

6. Makarieva V.N., Andreeva L.V. Analysis of the financial and economic activities of the organization. M., 2007.

7. Markaryan E.A., Markaryan S.E., Gerasimenko G.P. Management analysis in industries. M.-Rostov-on-Don, 2007.

8. Savitskaya G.V. Economic analysis. M., 2005.

9. Fridman A.M. Economics of Trade and Catering Enterprises of the Consumer Society. M., 2008.

The essence of turnover planning is to determine its volume in value terms for certain periods of time in the future. Merchandise planning serves as a tool to identify the future profits of the enterprise, allows you to improve control over the placement and use of all its resources.

In order for the turnover plan to contain sufficiently accurate and realistic indicators, certain principles should be taken into account when developing it. First, planning should be based on accounting and analysis of the results of previous activities. Secondly, it is necessary to correctly assess any trends affecting the implementation of the process of selling goods and arising in the period under consideration. The period under consideration can cover a time interval from several months to several years. At the same time, the turnover of the current period is taken as the basis for calculating planned indicators.

When developing a turnover plan, it is necessary to identify and highlight all the specific factors that may influence the formation of future market trends.

The choice and consideration of factors must be carried out on the basis of an analysis of their reliability and reliability. Factors that are not based on accurate data and do not have reliable evidence of their reliability should not be taken into account in planning. This approach allows you to monitor emerging opportunities and adapt to changes in the market. The turnover plan should be a reliable management tool for the enterprise and be based on reliable information. The sales plan must be realistic. This is a management tool, the basis for planning and developing the product policy of the enterprise, the main indicators of economic activity.

Planning of commodity circulation can be carried out in accordance with two approaches. According to the first approach, the management of a trading enterprise determines the goals and objectives of planning and planned indicators of turnover. Then these indicators are distributed in a more detailed form according to the sales structure and structural subdivisions of the enterprise (branches, sections, departments). The second approach involves the reverse action. At the same time, the calculation of sales indicators is carried out according to the structure of turnover and structural divisions, and then these data are summarized in a single plan for the turnover of the enterprise.

In practice, it is advisable to use both of these approaches, since planning is an ongoing and continuous process in which it is necessary to constantly coordinate plans and adjust them during implementation.

Planning the volume of trade can be carried out on the basis of the use of quantitative methods, by identifying trends in sales data for past periods. You can use qualitative methods that take into account changes in the immediate environment of a trading enterprise (for example, the actions of competitors), as well as general economic trends (for example, an increase in the refinancing rate that affects the cost of loans, a depreciation of the ruble).

It is important to choose the right planning method, since the accuracy of the execution of the plan depends on it. The choice of planning method depends on: the purpose of developing the plan; initial information obtained in the process of analyzing the turnover, its accuracy and reliability; the period for which the plan is calculated; the experience of the enterprise in the field of planning; costs associated with the development of the plan; availability of software and computer technology.

In order to obtain a more reliable turnover plan, it is advisable to use various calculation methods, which will ensure the multivariance of planned calculations. The presence of several variants of the plan and the analysis of the discrepancies between them make it possible to select and justify the most realistic and acceptable version of the plan that meets the optimality criterion. All calculations are carried out in comparable prices in force at the beginning of the planning period.

The planning of the turnover is carried out by various methods that have a different degree of formalization. Their use, however, has some limitations.

Economic and statistical methods are the most common methods of planning turnover. Their application depends on certain conditions.

If the enterprise has only a number of statistical data characterizing the change in turnover over time, then the planned indicators can be calculated by extrapolation. Extrapolation is carried out in various ways. The choice of extrapolation method depends on the characteristics of the time series.

The planned indicator is calculated by the formula:

Op \u003d On-1 * Ts,

Op - planned turnover;

About n-1 - turnover of the last period in a series of dynamics;

Тс is the average growth rate of trade.

The application of this method is possible when each member of the dynamic series is equal to the previous one, multiplied by the average rate. This method has its drawbacks. The trade turnover calculated with its help is very conditional, since it is based on the assumption that the conditions for the formation of trade turnover are unchanged. The plan will be erroneous if, for example, another store with a similar specialization opens in the place where the store is located. This change in conditions may cause a significant reduction in the planned sales volume.

To determine the volume of turnover using this method, it is necessary that the turnover for the analyzed periods be comparable, i.e. was expressed in the same prices and covered the same composition of the trading network. In addition, it is necessary to take into account both external factors that contribute to the increase in sales in previous periods, as well as possible conditions under which this level may fall.

Towards economic-statistical methods of general plan planning.

The resulting aligned series of moving averages allows us to determine the average change in the increase in turnover:

To calculate the moving amount of turnover, you need to select the period in which it will move. The longer the period covered by the moving amount, the smoother the forward selling trend will be. Therefore, the most preferred is the annual moving average.

The planned turnover volume is determined as follows:

where t? O is the average annual increase in turnover, thousand rubles:

t?O = ? 1+? 2+ …+? k,

О n - turnover of the n-th year;

About 1 - turnover of the first year;

N is the turnover number of the n-th year.

If the goal of turnover planning is to increase the efficiency of resource use, then planning is based on a comparison of resource efficiency indicators at a given enterprise and similar enterprises in the area, activities or competitors' indicators.

Calculations are carried out in a certain sequence.

At the first stage, the ratio (level) of the use of certain types of resources in similar but better enterprises is determined according to the formula:

Upr - the level of exceeding the efficiency of the use of the corresponding type of resource,%;

El - an indicator of the efficiency of the use of the corresponding type of resource (capital productivity, labor productivity, turnover per 1m 2 of the area of ​​​​the trading floor, etc.) achieved at a trading enterprise chosen as a standard;

Ed - an indicator of the effectiveness of the use of the corresponding type of resource, achieved at this enterprise in the preplanned period.

At the second stage, the priority type of resource is determined, according to which the enterprise plans to achieve the greatest efficiency of use.

This choice is made taking into account the results of a comparative analysis of the efficiency of the use of individual resources and the priority importance of some of them for the development of a given enterprise.

The method based on obtaining the required amount of profit involves carrying out planned calculations, which are based on a pre-calculated target amount of profit and a number of other financial indicators.

Planning the volume of sales of goods can be carried out by the method of technical and economic calculations, or normative. To use it, it is necessary to take into account the peculiarities of the work of a trading company, the specifics of the trade and technological process and the customer contingent served, the intensity of the customer flow, market fluctuations inherent in the area of ​​​​activity of this store. These features are taken into account by applying correction factors to the established group standard. Adjustment is made according to the following coefficients: for the average cost of the purchase, for the intensity of the flow of customers, for the completion of the purchase.

In real economic activity, situations often arise when it is important to know what effect a change in sales volume will have on profit and how much sales can cover the costs of purchase and sale. (The volume of sales is one of the most important factors in changing distribution costs, profits.)

For this purpose, the method of planning turnover based on the calculation of the break-even point is used. This technique is based on the principle of achieving such a volume of turnover in which the company is able to cover all costs without making a profit, but without loss.

To achieve a break-even trade turnover, sales revenues must cover the costs of purchasing goods, taxes paid to the budget, variable and fixed costs associated with the sale of goods. This volume of sales of goods can be determined by the following formula:

About - the volume of sales of goods, ensuring the achievement of the "break-even point";

UVD - the level of gross income from sales,%;

Hypost - the amount of fixed costs of circulation;

UIper - the level of variable costs of circulation of goods, %.

The next stage in the planning of turnover is the distribution of turnover by individual product groups, i.e. planning the structure of trade. The structure of trade turnover is reflected in the formation of income from the sale of goods, costs, and in meeting the needs of buyers. Therefore, this stage is important when planning the turnover.

The structure of turnover reflects the range of goods to be sold in the planning period. Due to the fact that the working conditions of trade enterprises have their own characteristics, in each of them the assortment of goods is different. When planning turnover, it is necessary to provide for its group and intra-group structures, while for each group of goods the amount and share of sales are determined (as a percentage of the total volume of turnover). In each period of time, the volume of sales will depend on the demand for goods, the season, the supply of goods, the state of inventory and other reasons.

When determining the structure of trade, data on changes in the share of each commodity group in the previous period are taken into account. The identified trends are the basis for choosing a method for planning the structure of the turnover.

For those goods, the share of which in the total sales volume for the analyzed period is characterized by stability, the planned indicator of the share can be calculated using the arithmetic average.

For product groups, the sales volume of which tends to fluctuate, you can use the method of analytical alignment of the share of the product group using a moving average.

When planning the structure of trade turnover, it is necessary to take into account the objective factor of switching demand for interchangeable goods, which can be determined using a sample survey, expert assessments, and the method of analogies.

The next stage of planning is the calculation of turnover by quarters and months.

The dynamics of quarterly and monthly sales volumes differs significantly from the dynamics of annual indicators. This difference is manifested, first of all, in the fact that the development of annual volumes of trade is more or less monotonous, while changes in quarterly and monthly volumes of trade of similar goods within a year are usually characterized by greater unevenness, sharply pronounced fluctuations.

When calculating turnover by quarters, the dynamics of the proportions of quarterly turnover of the corresponding periods is taken into account, taking into account changes in working conditions and the structure of turnover in the planned period.

Currently, there are many different methods by which the analysis and forecasting of quarterly and monthly volumes of trade are carried out. The simplest and most reliable method is the relative average method. With this method, the comparability of trade turnover only during the year is acceptable. For the calculation, it is necessary to use data on quarterly (monthly) turnover for at least three years. At the same time, it does not matter in what prices (comparable or actual) the calculations are carried out, since with this method the influence of the price index is leveled.

The objectives of wholesale trade planning are to determine the need for goods in the context of individual buyers, balanced linking them with sources of commodity resources, rational commodity circulation and uninterrupted supply of goods to buyers. A correctly calculated need for goods is the basis for purchasing goods from suppliers in the required quantity, assortment, on time, at certain prices. Errors in planning the need for goods can worsen the financial condition of the enterprise, lead to interruptions in trade, loss of customers.

When planning, economic-statistical, computational-analytical, economic-mathematical methods can be used.

>

base data background

Design, set the task for the automated receipt of the document "Statement of the implementation of the retail trade turnover plan for ____ month 20 __ for ___ district consumer union of ___ district", including the details: name of the district consumer union, name of the district, order number (or organization code), organization name, date , sales plan for the month, actual implementation of the plan, percentage of completion, deviations, total for the organization. Additional tasks: 1. Classify the details on the grounds and signs; 2. Description of the directory "List of goods by groups of goods"; 3. Build codes of District Consumer Unions (Organizations); 4. Describe the list of PD and possible changes in the document "Consignment note",

Attributes-attributes characterize the qualitative properties of the reflected entities. Requisites-bases - quantitative values ​​that characterize this entity.

Details classification

	Name of props	Props ID		Brief explanation
	Name of the district consumer union
	District name			Used to print text in documents
				Used to print text in documents
	Name of company			Used when sampling by organizations
				Used when selecting by date
	Monthly turnover plan		base	Used to print text in documents
	Actual implementation of the plan		base	Used to print text in documents
	Percent Complete		base
	Deviations		base	Used in arithmetic calculations
	Organization total		base	Used in arithmetic calculations

Directory "List of goods by groups of goods"

Directory "List of product groups"

Sample Fill

Raypotrebsoyuz codes

Consignment note - a contract for the carriage of goods by road. For international transportation, it is drawn up in 4 original copies. The first copy remains with the consignor, the second copy follows with the cargo, the third and fourth copies remain with the carrier.

A waybill is required when the parties hire a transport organization to transport goods. The seller, on the basis of the TTN, writes off the goods from the balance at the time of the transfer of ownership established by the supply (purchase and sale) agreement. The shipper writes off the goods from his warehouse, the recipient of the goods receives inventory items. The transport organization calculates the cost of its services. For the driver of the freight carrier, the bill of lading is the basis for the carriage of goods, which is necessary for presentation to employees of the internal affairs bodies.

Primary documents: supply agreement, invoice, act (report of the carrier) on the provision of services for the delivery of goods, powers of attorney.

When concluding a contract, the following information is added to the "Commodity Section" section: Name of the consignor (automatically entered under the contract), Name of the consignee (entered automatically according to the contract), Date of compilation, Name of the payer Payer (selected from the directory), Product name (selected from the directory) , Quantity, Price.

The following fields are automatically calculated: Amount (Quantity x Price), total payable (total for the Amount field).

Time of classes in computer training courses:

• 
  morning groups from 9 00 to 12 00;
• 
  day groups from 12 00 to 15 00, from 15 00 to 18 00;
• 
  evening groups from 1800 to 2100.

1. 
   Instruction number 9.

Practical work No. 9 a / b.

Topic: a) Lists and columns. b) Creation and editing of graphic images

Objective: To learn how to create numbered and bulleted lists and columns.

Task a). Lists and columns

1. 
   Type and format the text according to the model given in Appendix 4.
2. 
   Perform a spell check.
3. 
   Set the page margins in the text: top, bottom, left, right - 1 cm, page orientation - portrait.
4. 
   Perform font and paragraph formatting of text.
5. 
   Describe and arrange a multi-level list:

• 
  Level 1 - number format: "Article (numbered in Arabic numerals).";
  Font: Times New Roman, size - 14, bold;
  situation rooms - 0sm;
  the position of text - 2.5 cm.
• 
  Level 2 - number format "(numbering level 1) (numbered with Arabic numerals)..";
  Font: Times New Roman, size - 12, bold;
  position numbers - 1 cm;
  the position of text - 2.5 cm.
• 
  Level 3 - the marker format: "(*)";
  font: Symbol, size - 12, normal;
  marker position - 3cm;
  position of the text - 4cm.

1. 
   Make the page frame.
2. 
   Set the headers and footers:

• 
  Top - Last Name First Name;

Progress of the work

1. 
   read the job
2. 
   Perform tasks on the model of Annex 4.

Task b). Creating and editing graphics

1. 
   Create and execute "calendar schedule" of tables and various graphical objects.
2. 
   Objects can choose a variety of suitable meaning. You can add your items at random.
3. 
   In the "work performed" to make your personal data.
4. 
   The entire document is positioned on one sheet, as shown in sample 5 in the annex.

Progress of the work

1. 
   read the job
2. 
   Perform tasks on the model of the application 5.

report Form

1. 
   
2. 
   

Appendix 4.

THE FEDERAL LAW
On Basic Guarantees of rights of the child
IN RUSSIAN FEDERATION.

1. 
   GENERAL PROVISIONS.

1. 
   Definitions used in this Federal Law:

• 
  child - a person under the age of 18 years (of age);
• 
  children left without parental care.

1. 
   Relations regulated by this Federal Law.
2. 
   Legislation of the Russian Federation on the basic guarantees of child rights in the Russian Federation.
3. 
   Public policy objectives in the interests of children.
4. 
   The objectives of the state policy in the interests of children are:

• 
  children's rights provided by the Constitution and preventing discrimination against them;
• 
  the formation of the legal framework guaranteeing the rights of the child.

1. 
   State policy for children is based on the following principles:

• 
  legislative support for the rights of the child;
• 
  state support for families in order to ensure full education of children.

MAIN AREAS OF SUPPORT RIGHTS OF THE CHILD IN THE RUSSIAN FEDERATION.

RENTAL PROPERTY state or municipal institutions, are the object of social safety net for children.

1. 
   The conclusion of the tenancy agreement:
   • 
     the procedure for changing the destination of the property;

conditions for the recognition of the contract null and void.
P
Appendix 5.

Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
			1 visit dentist	2	3 The game golf	4
5 Exam In Russian	6 Exam Informatics	7	8 Massage	9 We buy food for a picnic	1 Let's go on a picnic 0	11
1 Exam mathematics 2	13	1 visit historical museum 4	1 Let's go to the zoo 5	16	1 football championship 7	18
1 3 DAYS AT SEA! 9	20	2 Passed in Literature 1	22	23	2 Exhibition live butterflies 4	25
2 Competition for the best author's song 6	2 Competition readers 7	28	2 Excursion to the museum BATTLE OF FAME 9	30

2.10 Instruction number 10.

№10 / B practical work.

Subject: Creation of computer publications (on specialty profile)

Objective: To summarize and consolidate the knowledge and skills of the program Microsoft Word application

Equipment: PC, Microsoft Word.2003 program

Exercise

In a text editor Microsoft Word.2003 using all its capabilities to create a document (publication) on the topic:

2. Publication equip graphic elements, photographs, drawings.

Progress of the work

1. 
   Read the job.
2. 
   Design and create a computer publishing program Microsoft Word

report Form

Task and its result (ready to publish) show the teacher.

2.11 Instruction number 11.

Practical work №11 / B.

Subject: Calculation in ET

Objective: Get practical skills in spreadsheet calculation.

Task a).

1. 
   Worksheet in the workbook "Practical Work № 11" headline "work a)."
2. 
   Create and place the table on the model:
3. 
   Using different ways of copying the information to neighboring cells, fill in the table.
4. 
   Custom create your own list and fill in the last column.

Task b)

1. 
   The next worksheet in the workbook "Practical work of 11" headline "work b."
2. 
   Typography, borders, cell shading format - modeled.
3. 
   The table manually fill in the following columns:
4. 
   "June";
5. 
   "July";
6. 
   "August".
7. 
   In the table to perform the calculations in the following columns and lines:
8. 
   "The amount of revenue in the districts" - using Autosum;
9. 
   "The amount of revenue by month" - using Autosum;
10. 
    Summary "The amount of revenue for districts" (selected cell at the intersection) - via Autosum.
11. 
    "Proportion of districts on the total revenues (%)" = "Amount revenue districts" / final "amount of revenue by county", in percentage format. "

Progress of the work

1. 
   read the job
2. 
   Perform tasks on a sample.

report Form

1. 
   Show the work of teachers.
2. 
   Respond to teacher's questions.

2.12 -2.14 Guide number 12.

Practical work №12 / B

Topic: Creating a specific ET. formatting ET

Objective: Secure the skills to create and edit spreadsheets, enter a formula in a table, save the table on disk

Equipment: PC, Microsoft Excel 2003 program

Progress:

Setting a) Make a price list for the model:

	Price list store "THE CLEVEREST"
		28.09.11
	dollar exchange rate	4.6 UAH.
Name of product	Price in USD	Price in USD.
Notebook in the box	$0,20	0.92 UAH.
Notebook in the ticker	$0,20	0.92 UAH.
Pencil case	$2,00	9,20 USD.
Pen	$0,50	2.30 UAH
Pencil	$0,20	0.92 UAH.
Ruler	$0,30	1.38 UAH
Elastic	$0,40	1.84 UAH

Progress of the work

1. 
   Select cell B1 and enter in it the heading of the table Price list of the store "SMARTEST"
2. 
   In cell C2, enter the TODAY function (Put the “=” sign  Press the button on the toolbar. In the CATEGORY field, select Date and Time. In the lower field, select the Today function).
3. 
   In cell B3, enter the words "Dollar rate", in C3 - the dollar rate for today.
4. 
   Apply the monetary format to cell C3 (Format  Cell Format  Number tab  Numeric format  Monetary  Designation can be chosen arbitrary).
5. 
   In cells A5:B5, enter the table column headings.
6. 
   Select them and apply a bold style and a larger font.
7. 
   Enter the data in cells A6:A12 and B6:B12.
8. 
   In cell C6, enter the formula: = B6*$C$3. ($ means that an absolute reference is used).
9. 
   Select cell C6 and drag the fill handle down to cell C13.
10. 
    Select the range of cells C6:C13 and apply the currency format to them.
11. 
    Select the header - cells B1:C1 and execute the Cell Format command, the Alignment tab and set the switch "Center on selection" (Horizontal alignment), "Wrap by words". Increase the title font.
12. 
    On the left side of the price list, insert a picture of your choice.
13. 
    Change the name SHEET1 on the Price list.

Task b) Calculate the statement of the implementation of the turnover plan for kiosk No. 5 in the form:

№	Month	Reporting year			Deviation from the plan
		plan	actually	performance, %
i	Mi	Pi	fi	Vi	Oi
1	January	$7,800.00	$8,500.00
2	February	$3,560.00	$2,700.00
3	March	$8,900.00	$7,800.00
4	April	$5,460.00	$4,590.00
5	May	$6,570.00	$7,650.00
6	June	$6,540.00	$5,670.00
7	July	$4,900.00	$5,430.00
8	August	$7,890.00	RUB 8,700.00
9	September	$6,540.00	RUB 6,500.00
10	October	$6,540.00	$6,570.00
11	November	$6,540.00	RUB 6,520.00
12	December	$8,900.00	$10,000.00

1. 
   Filling a column Mi can be done by dragging a marker.
2. 
   Column values Vi and Oi calculated by the formulas: Vi=Fi / Pi; Oi=Fi-Pi
3. 
   Rename SHEET2 v Vedomosti.
4. 
   Save the table in your folder under the name Practical work 1
5. 
   Show your work to the teacher.

report Form

Show your finished work to your teacher.

2.13-2.14 Instruction No. 13-14.

Practical work №13-14

Topic: Building diagrams in ET. Creation of an electronic document.

Objective: To learn how to insert diagrams and format them.

Equipment: PC, Microsoft Excel 2003 program

Exercise 1

1. 
   Create a table according to the example.
2. 
   In the table, calculate "total" by product name, "total" by month.
3. 
   Format the table according to the sample.
4. 
   Create a chart of coat sales in quarter 1.
5. 
   Create a chart for the sales of jackets and hats in the 1st quarter.
6. 
   Create a pie chart of the sales of all products in April.
7. 
   Create a graph for the sales of jackets in the 2nd quarter.
8. 
   Create a graph for the sales of all products in the 2nd quarter

		Sales volume of the Yunost store
			for the first half of 2010
No. p / p	Name of product	January	February	March	April	May	June	TOTAL
1.	Coat	25	30	30	20	20	30
2.	raincoats	320	400	400	410	410	400
3.	Jackets	150	200	200	450	450	200
4.	Hats	15	20	20	50	50	20
5.	Fur coats	800	600	600	50	50	600
6.	Hats	320	400	400	410	410	400
7.	Suits	15	20	20	50	50	20
	TOTAL

Progress of the work

1. 
   read the job
2. 
   Perform tasks on a sample.

report Form

1. 
   Show the work of teachers.
2. 
   Respond to teacher's questions.

2.15 Instruction No. 15.

Practical work No. 15 a/b

Subject: Creating a simple database. Sorting and filtering in the database Creating queries in the database

Target:. Acquisition of practical skills and abilities to work in the Microsoft Office Access program environment. Design database "College", define the main objects, their properties, create tables.

Equipment: personal computer, Microsoft Access program.

Exercise 1

Main objects:

Logical structure:

1 – 4 : Basic DB objects

5 – 7 : Objects with the help of which the main objects are connected with each other:

5 – 6 : Relationships between objects that are implemented using additional tables,

7 – 8 : Relationships between objects that are implemented using direct relationships between tables.

Table structure:

table	Field name	Data type	Note
teachers	№_teacher	Counter	key field
	Surname	Text
	Name	Text
	Surname	Text
	Cool_ guide	Logical
Cabinets	Name_Cab.	Text	key field
	№_teacher	Numerical	Lookup Wizard from the Teacher table.
Thing	item_number	Counter	key field
	Name	Text
students	№_Student	Counter	key field
	Surname	Text
	Name	Text
	Surname	Text
	Class	Text
	Date of Birth	Date Time
	The address	Text
	Telephone	Text
Teaches	№_teacher	Numerical	Lookup Wizard from the Teacher table
	item_number	Numerical
	Class	Text	Lookup Wizard from Table Class
Receives	student_number	Numerical	Lookup Wizard from the Students Table
	item_number	Numerical	Lookup Wizard from Table Subject
	Period	Text
	Grade	Numerical	Lookup wizard based on a fixed dataset

Book 2

Makarova M.E.

computer science teacher of the highest category,

senior teacher, Kiev

Practical and

test papers

© Copyright Makarova M.E.

Project "Informatics Lessons"

http://uchinfo.com.ua

Page

Practical work No. 1 Creating and editing spreadsheets, entering formulas into the table, saving the table on disk………………………………………. Practical work No. 2 Using the built-in functions and operations of ET………... Practical work No. 3 Using logic functions …………………………. Practical work No. 4 Building charts and graphs…………………………….. Practical work No. 5 Sorting and filtering data……………………………… Test work on the topic: “Spreadsheets. Entering, editing and formatting data. Standard Functions. Option 1……………………………………………………………………………………… Option 2…………………………………… …………………………………………………… Comprehensive practical work on the topic: “Creating tables in EXCEL”. Option 1……………………………………………………………………………………… Option 2…………………………………… …………………………………………………… Option 3…………………………………………………………………………… …………… Option 4…………………………………………………………………………………………

EXCEL

Practical work No. 1

Topic: Creating and editing spreadsheets, entering formulas into a table, saving a table to disk.

Target: Get practical skills in creating and editing spreadsheets, entering formulas into a table, saving a table on disk.

Progress:

1. Make a price list according to the model:

Stages of the task:

1. Highlight a cell IN 1 and enter a table title in it Price list of the store "HORN AND HOOVES"

2. To cell C2 enter function TODAY(Put a sign «=» ðê Press the button on the toolbar. In field CATEGORY select Date and time. Select a function in the bottom box Today).

3. To cell AT 3 enter the words " dollar exchange rate", v C3 the dollar exchange rate today.

4. To cell C3 apply the currency format ( Format ð Cell Format ek ð Number tabð Numeric format ð Monetary ð Designation you can choose any).

5. In cells A5:B5 enter the table column headings.

6. Select them and apply a bold style and a larger font.

7. In cells A6:A12 and Q6:Q12 enter data.

8. Into the cell C6 enter the formula: = B6*$C$3. ($ means that an absolute reference is used).

9. Select a cell C6 and drag the fill handle down to the cell C13.

10. Select a range of cells C6:C13 and apply the currency format to them.

11. Highlight Header - Cells B1:C1 and run the command Cell Format, tab alignment and set the radio button Center on Selection» (Horizontal Alignment), « Move by word". Increase the title font.

12. On the left side of the price list, insert a picture of your choice.

13. Change the title SHEET1 on the Price list.

2. Calculate the statement of the implementation of the kiosk number 5 turnover plan in the form:

№	Month	Reporting year	Deviation from the plan
		plan	actually	performance, %
i	Mi	Pi	fi	Vi	Oi
	January	$7,800.00	$8,500.00
	February	$3,560.00	$2,700.00
	March	$8,900.00	$7,800.00
	April	$5,460.00	$4,590.00
	May	$6,570.00	$7,650.00
	June	$6,540.00	$5,670.00
	July	$4,900.00	$5,430.00
	August	$7,890.00	RUB 8,700.00
	September	$6,540.00	RUB 6,500.00
	October	$6,540.00	$6,570.00
	November	$6,540.00	RUB 6,520.00
	December	$8,900.00	$10,000.00

1. Filling a column Mi can be done by dragging a marker.

2. Column values Vi and Oi calculated by the formulas: Vi=Fi / Pi; Oi=Fi-Pi

3. Rename SHEET2 v Vedomosti.

4. Save the table in your folder under the name Practical work 1

5. Show the work to the teacher.

EXCEL

Practical work No. 2

Topic: Using Built-in ET Functions and Operations

Target: get practical skills in working with MS Excel,

enter and edit standard ET functions

Progress:

Task number 1

1. Tab the function

on the interval with a step of 0.2.

2. Arrange the calculations in the form of a table, format it using autoformat and make a heading for the table.

3. Name the worksheet Function.

Task number 2

1. Go to a new worksheet and name it Age.

2. Create a list of 10 last names and initials.

3. Enter it into the table using autocomplete.

4. Enter dates of birth in the table.

5. In a column Age calculate the age of these people using the functions TODAY and YEAR

6. Format the table.

7. Make a header for the table " Age calculation»

№	Full name	Date of Birth	Age
	Ivanov I.I.
	Petrov P.P.
	Sidorov S.S.
…
	Myshkin M.M.

Task number 3

1. Open the file with Practical work 1, go to sheet Vedomosti.

2. In this table, add cells from the bottom according to the model and perform the corresponding calculations. (Use statistical functions MAX and AVERAGE)

№	Month	Reporting year	Deviation from the plan
		plan, r.	in fact, r.	performance, %
i	Mi	Pi	fi	Vi	Oi
	January	$7,800.00	$8,500.00
	February	$3,560.00	$2,700.00
	March	$8,900.00	$7,800.00
	April	$5,460.00	$4,590.00
	May	$6,570.00	$7,650.00
	June	$6,540.00	$5,670.00
	July	$4,900.00	$5,430.00
	August	$7,890.00	RUB 8,700.00
	September	$6,540.00	RUB 6,500.00
	October	$6,540.00	$6,570.00
	November	$6,540.00	RUB 6,520.00
	December	$8,900.00	$10,000.00
			Maximum
			The average

3. Show the work to the teacher.

EXCEL

Practical work No. 3

Task number 1

Task number 2

Working with the IF function

1. Copy the table from task No. 1 to Sheet2 and rename it to Tariff rates

2. Change the table header

3. Add a column Tariff rates and calculate them like this:

1- if the experience is less than 5 years, 2- if the experience is more than or equal to 5 years

Tariff rates for employees of the company "Horns and Hooves"

Full name	Position	Employment date	Experience	Tariff rates
Ivanov I.I.	Director	Jan 01, 2003
Petrov P.P.	Driver	Feb 02, 2002
Sidorov S.S.	Engineer	June 03, 2001
Koshkin K.K.	Ch. boo.	September 05, 2006
Myshkin M.M.	Security guard	August 01, 2008
Moshkin M.M.	Engineer	December 04, 2005
Sobakin S.S.	Technician	November 06, 2007
Losev L.L.	Psychologist	April 14, 2005
Gusev G.G.	Technician	July 25, 2004
Volkov V.V.	Supplier	May 02, 2001

Task number 3

academic performance

Full name	Mathematics	Informatics	Physics	The average
Ivanov I.I.
Petrov P.P.
Sidorov S.S.
Koshkin K.K.
Myshkin M.M.
Moshkin M.M.
Sobakin S.S.
Losev L.L.
Gusev G.G.
Volkov V.V.
Subject average

3. Calculate the averages for the performance of each student and for subjects.

4. Build a histogram of progress in subjects.

5. Build a pyramid chart based on the average performance of each student

6. Construct a pie chart on the average performance in subjects. Add percentages in data labels to this chart.

7. Beautifully arrange all the diagrams.

8.Show the work to the teacher.

EXCEL

Practical work No. 5

Option number 1

Task number 1

Task number 2

Learning youth.

Task number 3

Create a table and format it according to the sample.

• Column "Number of days of stay» is calculated using the DAY function and the values ​​in the Arrival Date and Departure Date columns
• Column "Price" calculated according to the condition: from 1 to 10 days - 100% of the cost, from 11 to 20 days - 80% of the cost, and more than 20 - 60% of the total cost of the room for this number of days.

Task number 4

Make a multiplication table

Multiplication table

…

Control work on the topic:

“Spreadsheets. Entering, editing and formatting data. Standard Functions.

Theoretical information:

1. Rules of safety and behavior in the COT.
2. Operations with cells and ranges.
3. Data types.
4. Cell formatting.
5. Function insertion.

Option number 2

Task number 1

Construct a table of function values ​​on the interval [-2, 2] with a step of 0.4:

Apply one of the autoformat types to the table.

Task number 2

Create a table and format it according to the sample.

Calculations in a column Reporting year in % to the previous one are performed according to the formula:

Task number 3

Create a table for calculating the optimal weight and format it according to the sample.

• Column "Optimal weight» is calculated by the formula:

Optimal weight \u003d Height- 100

• If the weight of a person is optimal, then in the column "Advice" opposite his name should appear the entry "Optimal weight". If the weight is less than optimal - “You need to recover by”, indicating in the next cell the number of missing kilograms. If the weight is more than optimal - “You need to lose weight by” indicating in the next cell the number of extra pounds.

How much do we weigh?

Task number 4

Make a multiplication table

Formulas and absolute references are used to fill the table.

Multiplication table

…

"Creating tables in EXCEL".

Option number 1

Exercise 1.

2. Format the table.

3. Build a comparative chart (histogram) by the levels of sales of different products in the regions and a pie chart by the average number of products.

Task 2

1. Create a table according to the sample. Perform the necessary calculations.

Total Cost = Total Mileage * Cost Rate

2. Format the table.

3. Construct a pie chart "Total car mileage" indicating the percentage of each and a bar chart "Costs of car repairs."

4. Use the Filter tool to identify vehicle brands with more than 40,000 km and vehicle brands with higher than average maintenance costs.

“Accounting for the cost of maintenance and current repairs of cars”

Task 3

2 Given a function:

Tabulate this function on the interval with a step of 0.2 and plot this function.

Task 4

2. The data in the Age column is calculated using the functions TODAY and YEAR

3. Sort the data in the table by age.

4. Build a comparative histogram by age and use employee positions as labels on the X axis.

5. Using the filter, display information only about military service employees (Gender - m, age from 18 to 45 years).

Full name	Position	Date of birth	Floor	Age
Arnoldov Taras Bulbovich	Director	01.12.45	m
Golubkov Lenya Mavrodievich	Driver	20.09.78	m
Mutton Sam Jonovich	Supplier	05.08.68	m
Simeonenko George Zhorikovich	Ch. boo.	04.11.84	m
Fisherman Karp Karpovich	Engineer	05.05.55	m
Grafchenko Dracul Drakulovich	Manager	03.06.68	m
Kara-Murza Lev Filippovich	Security guard	04.03.79	m
Sidorov Petr Ivanovich	Technician	20.10.85	m
	Secretary	30.05.80	well
Poppins Mary Jonovna	Psychologist	04.07.68	well

Comprehensive practical work on the topic:

"Creating tables in EXCEL".

Option number 2

Exercise 1

1. Create a table according to the sample. Perform the necessary calculations.

2. Format the table.

3. Build a comparative chart (histogram) for temperature in different months and a pie chart for average temperature in different regions.

Task 2

1. Create a table according to the sample. Perform the necessary calculations.

2. Format the table.

3. Use the Filter tool to determine which exam students did the worst and identify the names of students who have an average grade lower than the overall GPA.

4. Build a bar graph of average student performance and a pie chart of average grades in subjects.

Session results:

Task 3

Given a function:

Tabulate this function on the interval with a step of 0.2 and plot this function.

Task 4

1. Create a table and format it according to the sample.

2. Data in a column Price per block are calculated as 90% of the price for 10 units of goods.

3. Data in a column Number of blocks are calculated using the INTEGER function,

4. Data in a column Number of units calculated as difference

Quantity - Number of blocks

5. The cost is calculated:

Price per block* Number of blocks + Price per unit* Number of units

6. Sort the data in the table by purchase price.

7. Build a pie chart by the number of goods sold. Sign shares.

8. Using the filter, display information only about those products whose cost is above average.

List of wholesale and retail trade of the firm "Horns and hooves"

Name of product	Units	Unit price	Quantity	Price per block (ten) (90%)	Number of blocks	Number of units	Price
Sweets "Batonchik"	box
Cookies "Jubilee"	pack
Sweets "Squirrel"	box
Sweets "For tea"	box
Sweets "Cosmos"	box
Cookies "Oatmeal"	pack
Cookies "Ladies"	pack
Sweets "Evening"	box
Cookies "Lakomka"	pack
Cookies "Southern"	pack

Comprehensive practical work on the topic:

"Creating tables in EXCEL".

Option number 3

Exercise 1

1. Create a table according to the sample. Perform the necessary calculations.

2. Format the table.

3. Build a comparative chart (histogram) by sales levels in different months in the regions and a pie chart by the average number of sales in the regions.

Task 2

1. Create a table according to the sample. Perform the necessary calculations.

2. Format the table.

3. Build a pie chart by the amount of costs (line TOTAL) for wages and a bar chart of the cost of products.

4. Use the Filter tool to determine the department and product code that has the highest sum of all costs.

Task 3

Given a function:

Tabulate this function on the interval with a step of 0.2 and plot this function.

Task 4

1. Create a table and format it according to the sample.

2. Calculate the work experience using the data from the Admission date column and the standard functions TODAY and YEAR.

3. Calculate the tariff depending on the length of service in this way:

up to 5 years - 1 , from 5 to 10 years - 1.5 , more than 10 - 2 .

4. Build a comparative histogram for the length of service of employees.

5. Using the filter, display information only about those employees whose work experience is more than 10 years.

Information about the employees of the company "Horns and Hooves"

Full name	Position	employment date	Work experience	Rate
Arnoldov Taras Bulbovich	Director	12.01.04
Golubkov Lenya Mavrodievich	Driver	23.08.90
Mutton Sam Jonovich	Supplier	31.01.99
Simeonenko George Zhorikovich	Ch. boo.	04.02.05
Fisherman Karp Karpovich	Engineer	12.02.96
Grafchenko Dracul Drakulovich	Manager	10.04.95
Kara-Murza Lev Filippovich	Security guard	15.03.90
Sidorov Petr Ivanovich	Technician	20.08.85
Beautiful Vasilisa Ivanovna	Secretary	15.08.04
Poppins Mary Jonovna	Psychologist	12.01.06

Complex practical work