Softwiro
Free Excel template

Paper factory in Excel (free): materials, rolls, scrap and cost

Know what a roll and a kilo really cost, where scrap goes, and who owes you money.

6 min read

You run a small or mid-sized paper factory making tissue rolls, kraft rolls and cartons, and each week you want to know how many kilos came out sellable and how many were lost as scrap, what a unit and a kilo really cost after materials, labour and energy, whether materials will last, and who owes you money. The free paper factory sheet answers that from tables you type yourself. It also suits anyone searching for a paper cost calculation in Excel or a tissue factory sheet.

The problem it solves

In a paper mill the price of pulp and recycled paper moves from one delivery to the next, every batch comes out with different scrap, and some batches fail inspection. If you cost a roll from one pulp price written on an old note, it looks cheaper than it is and your margin leaks without you knowing where. The sheet links each unit's recipe to every batch record so all of this turns into numbers.

How the sheet works, step by step

  1. In Settings type today's date, the payment term, the overhead percentage, the scrap limit, the lowest acceptable execution, the reorder multiplier and the first week of the summary.
  2. In Materials type raw and packaging materials with opening price, opening stock, minimum level and supplier.
  3. In Products type each product with its unit, unit weight in kg and selling price.
  4. In Recipes type what one unit needs of each material, for example 0.085 kg of pulp, one cardboard core and a quarter of a bag per roll.
  5. In Machines type each machine's capacity in kg per hour and its labour and energy cost per hour.
  6. In Receipts log every delivery: stock rises and the weighted average cost updates by itself.
  7. In Production log type each batch: machine, product, planned, actual, scrap, run hours and the QC result. Materials are deducted and cost is worked out for you.
  8. In Sales log each invoice with what the customer paid, then read Customers and Summary.

What each sheet shows

  • Materials: balance and its value at average cost, status (in stock, reorder, negative) and the suggested order with its cost.
  • Products: cost per unit and per kg by component, scrap, margin, units sold and finished stock.
  • Production log: for each batch the execution rate, scrap, sellable units, each cost item and an alert.
  • Sales and Customers: the balance on each invoice, the overdue ones and each customer's credit limit.
  • Summary: production, cost, sales and stock indicators with four charts.

Walk-through on the sample numbers

The figures below are sample data in the sheet, not a real factory. The example has 8 materials, 5 products, 4 machines, 31 batches and 18 invoices, with a 4% scrap limit and overhead at 12% of labour and energy:

  • First batch (toilet rolls on Machine 1): planned 12,000, actual 11,800, scrap 300 (2.5%), so 11,500 sellable rolls weighing 1,150 kg. Materials 45,694.32, labour 1,440, energy 1,120 and overhead 307.20 give a total of 48,561.52, or 4.22 per roll and 42.23 per kg.
  • Toilet rolls overall: 4.23 per roll against a 6.5 selling price, a profit of 2.27 per roll (34.9%). The kilo costs 42.29: 39.78 materials, 1.26 labour, 0.98 energy and 0.27 overhead, so materials are the real battle.
  • Total output: 48,555 kg produced, scrap 1,510.5 kg (3.1%) and 43,849.5 kg sellable. Total cost 1,168,818.73, an average of 26.66 per sellable kg.
  • Log alerts: 5 batches with scrap above 4% (the worst is the 19 October kraft batch at 7.3%), one table-napkin batch rejected at inspection with 5.3% scrap whose cost of 44,255.56 is carried by the passed batches, one carton batch awaiting inspection, and another that hit only 88.5% of plan.
  • Scrap trend: 4.3% in the week of 12 October, down to 2.2% in the week of 9 November.
  • Stock: two materials need ordering: pulp at 441.7 kg is under its 4,000 minimum so the sheet suggests 7,559 kg, and cardboard cores at 2,927 are under 15,000 so it suggests 27,073. The order costs 275,523.80, and materials in store are worth 268,659.06.
  • Sales: 905,940 invoiced, 617,525 collected, 288,415 owed, of which 37,400 is overdue (27,400 from Delta Distribution and 10,000 from Al-Reef Packaging). Al-Ahram Printing is over its credit limit (34,000 against 20,000). Estimated gross profit is 386,735.55.

Why it matters

  • Weighted average cost: batch costs follow the latest delivery price instead of an old one.
  • Scrap tied to the batch: you see which machine and product lose the most.
  • Automatic deduction: materials come off stock from the production log itself.
  • Production linked to sales: finished stock and gross profit come from the two logs together.
  • Works in Excel 2016 and later, Google Sheets and LibreOffice, with room for 40 materials, 15 products, 400 batches and 400 invoices.

Common mistakes

  • Recipe written per batch instead of per unit: the recipe here is for one unit, so a batch quantity inflates use and cost.
  • Leaving the QC result blank: a batch with no result does not enter finished stock.
  • Forgetting a delivery: a material balance shows negative and the reorder need balloons.
  • Guessing run hours: labour and energy are charged on them, so take them from the machine counter or the shift supervisor.
  • Typing a payment on a different invoice: record it on the same invoice so the overdue figure stays right.

The sheet is for records and calculations only. Quality specifications and safety or environmental requirements are taken as the competent authority requires. If you only trade goods, see the general shop inventory sheet, and for another factory with lines and packing see the biscuit factory sheet.

Who it suits and when it is not enough

It suits a small or mid-sized factory where the production officer types the log after each shift. When machines and shifts multiply, or you want material planning, order scheduling and automatic OEE, MESwiro breaks planned production orders into their materials, spreads orders across machines and computes OEE.

Next step

Open the template page and try the example, or download the blank copy. Type the recipe of your best-selling product and the price of your last pulp delivery, then log one batch and check its unit cost and scrap.

Free cheat sheet for this field

Terms, abbreviations, tables and diagrams over several pages, in Arabic and English. Download it as a PDF or images, including a phone-size version.

Cheat sheet: paper factory (1/3)Cheat sheet: paper factory (2/3)Cheat sheet: paper factory (3/3)
0 downloads · 3 pages

Questions this raises

How are unit and kilo costs calculated?

Batch cost = materials (recipe cost per unit × actual quantity) + labour and energy (run hours × the machine's hourly cost) + overhead (a percentage of labour and energy). Unit cost = batch cost ÷ sellable units, and cost per kg = batch cost ÷ sellable kilos.

How are materials deducted from stock?

Material use = its quantity in the unit recipe × the batch's actual quantity, scrap included because it used materials. Balance = opening stock + receipts - use, and "Reorder" shows when it falls below the minimum.

What happens to a batch that fails inspection?

Its cost stays in the log but its sellable quantity is zero, so the passed batches carry its cost. A batch not yet inspected shows "Pending QC" and stays out of finished stock.

How do I see which customers are late paying?

An overdue invoice has a balance and has passed its due date (invoice date + the payment term in Settings) as of today's date. The Customers sheet totals the overdue amount per customer and flags anyone over their credit limit.

Does the sheet set quality specifications or environmental requirements?

No. It is for records and calculations only; quality, safety and environmental requirements are taken as the competent authority requires.

Have a system to build?

Softwiro designs, builds, and operates production systems. Describe what you need and we will come back with a scope rather than a slogan.

Start a project