Management of futon covers production, made in an Excel book that allows to take a complete control of the production, stock and sale.
To carry out this management, the following stages have been taken into account:
- Entry of cloths, in rolls of several linear meters.
- Making the covers process, from the previous cloths.
- Sale of the covers in physical or virtual store (online web).
- Cloths stock control and covers made.
- Summary statistics.
- Table of equivalences, to calculate the linear meters, according to measurements.
- Print sheet, monthly order summary.
- Diagrams per linear meter.
- Calculation for custom-made covers.
For each stage, we create a sheet, within the same Excel book, by making formulas that interact between these sheets. Below we explain in more detail, each sheet.
Entry of cloths:
The raw material are rolls of cloth that are purchased with an extension of several linear meters and are delivered to a manufacturer. The data required are: Color or printing of the cloth, linear meters ordered, date of order, date of receipt, linear meters received and the manufacturer to whom the roll of cloth is delivered. This sheet only records the entry of material.
This sheet records the orders for covers from clothing manufacturers, and the receipt of these orders. Then, there are two part parts:
- To register the order that is made to the maker: we take note of the covers to make, which confectioner will make them, the linear meters that will consume to make the covers and the cost of the order.
- To register the reception of the covers: we take note of the date the covers are received, in which warehouse they are deposited and what their selling price will be. In addition, as the reception lines of the covers, match those of your orders, we add a cell at the end of the table, to mark the end of that order.
In principle, incoming orders are planned for standardized measures, but it is possible to enter special measures. In this case, you must also add the linear meters that correspond to this special measurement.
This sheet details the sales made in the store. Each order, consists of the code of each cover, its color or print, its size, quantity, price and date of sale. It is also possible to enter sales for special measures.
The sale that takes place on the web, is treated by means of a data import and is integrated in the stock control.
Control of stocks:
The company has a website, through which it carries out a part of the sale of the covers and therefore, it also keeps a control of stocks. This data is extracted and entered on a separate sheet, within this book, to coordinate the total stocks and check quantities.
The control of stocks, it implies to know the quantity of material that have the confectioners. Also the articles and units deposited in the warehouses, and in the physical store, contrasting with the stock introduced in the web.
The control of stocks, also shows alerts, on the situation of every article, with semaphores of three colors. An intermediate quantity and the minimum quantity are controlled, to launch alerts, with green, yellow and red colors.
Statistics and management summary:
In this sheet, several tables are presented, which calculate monthly, annually or between dates:
- Orders in linear meters per confectioner, with alerts, to know if they run out of rolls of cloth.
- Covers stock between dates.
- Covers sales between dates.
Data needed to establish:
- Conversion tables of the measures of the covers, in their corresponding linear meters.
- Intermediate quantity to activate the stock alert, with yellow light.
- Minimum quantity to activate the stock alert with red light.
- Minimum quantity to activate the stock alert for linear meters of cloth.
- Cost prices by size.
- Sales prices by size.
- Values for the program’s drop-down fields.
Printing of the orders generated each month:
For this sheet, a pivot table was created that collects all the orders and groups them by month, adding up the units according to origin (warehouses, store) and showing the total quantity of covers sold.
Diagrams per linear meter:
This sheet contains the specifications for the measurements of each cover, with all the detail broken down, for its manufacture, including the losses obtained and the use given to these losses. It includes a graphic scheme for the pattern, marking and cutting of the cloth, in each size.
Calculation of the Linear Meter, for special measurements:
It is a calculation template for any size of cover, with a generic scheme, to establish the pattern, marking and cutting of the cloth.
Application made with Excel. To use this application you need to have the MS Excel program. The application is made in Spanish language. It can be translated and adapted. Note: The data inserted in the images are fictitious.