{"id":153396,"date":"2025-05-27T16:08:16","date_gmt":"2025-05-27T13:08:16","guid":{"rendered":"https:\/\/azbuki.bg\/?p=153396"},"modified":"2025-05-30T12:17:12","modified_gmt":"2025-05-30T09:17:12","slug":"enhancing-cost-efficiency-in-manufacturing-through-3c-an-excel-based-solution","status":"publish","type":"post","link":"https:\/\/vocedu.azbuki.bg\/en\/international-scientific-and-technical-conference-automation-of-discrete-production-engineering-2025\/enhancing-cost-efficiency-in-manufacturing-through-3c-an-excel-based-solution\/","title":{"rendered":"Enhancing cost efficiency in manufacturing through 3C: an excel-based solution"},"content":{"rendered":"

Ram\u00eb Likaj<\/a>
\n<\/strong>ORCID iD: 0000-0002-8316-6334
\n<\/strong>FME University of Pristina, Kosovo<\/em><\/p>\n

G\u00ebzim Hoxha<\/a>
\n<\/strong>ORCID iD: 0000-0002-0486-7010
\n<\/strong>FME University of Pristina, Kosovo<\/em><\/p>\n

Albi Macula<\/a>
\n<\/strong>FME University of Pristina, Kosovo<\/em>
\n<\/em><\/p>\n

https:\/\/doi.org\/10.53656\/adpe-2025.01<\/a><\/p>\n

\"\"
\nPDF<\/a><\/p>\n

Pages 8-22<\/em><\/p>\n

Abstract. <\/strong>Manufacturing cost estimation is crucial for improving production efficiency and competitiveness. This paper describes a responsive and real Production Cost Program that is structured as an interactive form that incorporates data analysis for a variety of production processes such as milling, drilling, laser cutting, and water jet cutting. The system uses the supplied data to select the best processing methods, assuring accurate cost calculations. The user interface acts as a structured questionnaire, requiring users to enter essential parameters such as materials, machining procedures, and production volume. Once these inputs are entered, the system automatically computes the required cost factors in the background of the application by referencing pre-entered tabular data.<\/p>\n

This method reduces user effort while retaining high accuracy in cost estimation.
\nThe program that was built by using Microsoft Excel as a programming tool, leveraging its advanced formula-based calculations, macros, and Visual Basic for Applications (VBA) scripts to automate data processing. Its structured style enables dynamic cost analysis with real-time accuracy. Excel’s interaction with external databases extends functionality beyond standalone estimation. The program that we have created in this paper and its adaptability enables modification to industry requirements, streamline cost analysis and optimizing production planning. Excel’s automation and scalability increase resource allocation, waste reduction and adaptability to a wide range of manufacturing environments.<\/p>\n

Keywords:<\/em> Cost Estimation; Manufacturing Processes; Production Planning; Automated Calculation; Data-Driven Decision Making; Excel Programming; VBA; Manufacturing Optimization; Cost Analysis<\/p>\n

    \n
  1. Introduction<\/strong><\/li>\n<\/ol>\n

    Accurate cost assessment is vital for budgeting, pricing, and planning in manufacturing (Kalpakjian & Schmid, 2021). Manual estimation methods are often inconsistent and error-prone due to their labor-intensive nature (Groover, 2020)<\/em>. To overcome these issues, this study introduces 3C (Crafts Cost Calculator), an Excel-based tool that automates cost estimation.<\/p>\n

    3C uses a structured three-sheet system: the Form Sheet for input, the Archives Sheet for storing results, and the Database Sheet for backend calculations. By referencing predefined variables, such as machine rates and process types, 3C ensures accurate, real-time cost calculations. This research explores its structure, formulas, and advantages over manual methods.<\/p>\n

      \n
    1. Literature review <\/strong><\/li>\n<\/ol>\n

      2.1. Existing cost estimation methods in manufacturing<\/strong><\/p>\n

      Estimating manufacturing costs is essential for managing expenses and ensuring profitability (Maruf et al., 2024). Traditional methods often rely on fixed data and manual input, making them inflexible and error-prone. Automated tools like 3C address this by using dynamic variables and structured data to improve accuracy (Niazi et al., 2006).<\/p>\n

      Analogous estimation, based on past projects, is simple and fast but unsuitable for custom parts. Parametric estimation uses cost-driving variables effectively in standardized production, though it depends heavily on quality data (Shehab & Abdalla, 2001; Niazi et al., 2006).<\/p>\n

      The bottom-up method offers precision by calculating labor, materials, and machine time separately, but it is time-consuming (Rush & Roy, 2001). Spreadsheet-based tools offer flexibility but risk errors and inefficiency, especially in complex workflows (Shehab & Abdalla, 2001).<\/p>\n

      2.2. The role of automation in cost calculation<\/strong><\/p>\n

      Traditional cost estimation methods often require significant manual effort and rely on static data, limiting their adaptability. 3C improves this by automating calculations, using dynamic variables, and enhancing accuracy through structured data.<\/p>\n

      Automation offers consistent, real-time estimates while reducing errors and saving time. Engineers can focus on design instead of manual calculations (Kerremans et al., 2012). It also supports traceability and data-driven decisions by tracking past estimates and integrating live cost factors like labor and materials (Kaplan & Anderson, 2004).<\/p>\n

      With these features, 3C provides a standardized, scalable, and efficient approach to manufacturing cost estimation.<\/p>\n

      2.3. Comparison with other similar tools<\/strong><\/p>\n

      Manufacturing employs various cost estimation systems with differing levels of automation and complexity. 3C offers a practical solution between manual spreadsheets and high-cost commercial tools. Spreadsheet-based estimators rely heavily on manual data input, lack automation, and are prone to errors (Niazi et al., 2006). 3C improves this with dropdown selections, automated archiving, and predefined formulas. Commercial tools like Costimator and aPriori offer advanced features but are expensive, complex to set up, and require significant training. These are best for large-scale operations, whereas 3C allows quick, reliable estimation without needing specialized software skills.<\/p>\n

      ERP systems such as SAP ERP and Oracle Manufacturing Cloud track real-time costs but are often too costly and complex for small enterprises. 3C focuses only on component-level estimation, making it more accessible. While custom tools using Python, MATLAB, or VBA allow flexibility, they demand programming skills. 3C removes this need through a structured and automated Excel-based design.<\/p>\n

        \n
      1. Methodology<\/strong><\/li>\n<\/ol>\n

        3.1. 3C\u2019s Three-sheet system<\/a><\/strong><\/p>\n

        Form sheet<\/strong><\/p>\n

        The Form Sheet serves as the main interface for inputting components and processing data. Its user-friendly design relies on dropdown menus and real-time filtering, minimizing manual input and reducing data entry errors (Fig. 1, Fig. 2). The first user input is the component type, with options like Assembly, Prismatic, or Cylindrical. This choice dynamically filters relevant manufacturing methods and types.<\/p>\n

        Methods include general categories such as Cutting or Machining, while types refer to specific processes like Laser Cutting or Waterjet Cutting, each with distinct cost variables.<\/p>\n

        Based on the selected process, the form automatically assigns the correct input unit (centimeters for cutting, minutes for machining) ensuring data compatibility and accurate calculations (Fig. 3).<\/p>\n

        Material selection is also handled via dropdown, allowing users to choose from predefined material types. Since materials differ in cost and machining properties, this step is critical to overall accuracy. Additionally, labor time can be entered manually to reflect operator involvement.<\/p>\n

        The sheet includes an image upload feature for visual reference and documentation (Fig. 4), improving traceability and communication for future reference.<\/p>\n

        Three interactive buttons streamline user actions: calculate the component\u2019s cost, save the estimate to the Archive Sheet, or reset the form for a new entry.<\/p>\n

        \"\"<\/p>\n

        Figure 1. <\/strong>Form Sheet<\/pre>\n
        By integrating dropdowns, dynamic filtering, automatic unit assignment, and visual tools, the Form Sheet allows engineers to perform quick, accurate, and consistent cost estimates \u2013 eliminating many of the inefficiencies found in manual methods. For example, if the user selects Cutting,<\/em> the type selection may include options such as Laser Cutting, Waterjet Cutting, Plasma Cutting<\/em>, each of which has distinct cost factors. This structured approach ensures clarity and consistency in process selection.<\/p>\n
        \"\"<\/p>\n
        a)<\/strong><\/p>\n
        \"\"<\/p>\n
        b)<\/strong><\/p>\n
        Fig. 2. General Info: a) Project; b) Component<\/strong><\/pre>\n
        Once the method and type are selected, the form dynamically determines the variable unit<\/em> that must be provided for cost estimation. Each manufacturing process is associated with a specific unit that corresponds to its operation. For example, in Laser Cutting<\/em>, the required input is in centimeters (cm),<\/em> reflecting the material cut per unit length. In Machining<\/em>, the unit might be in minutes (min)<\/em> to represent spindle time, Fig.3. By automatically adjusting the required input unit, the form ensures that engineers provide the correct data for accurate cost estimation.<\/p>\n
        \"\"<\/p>\n
        Figure 3. <\/strong>Variable determination<\/pre>\n
        Several interactive buttons<\/em> facilitate seamless operation within the form. With a single click, users can:<\/p>\n
        \u2013 Calculate<\/em> the cost of the component based on the provided inputs.<\/p>\n
        \u2013 Save<\/em> the calculation to the archive sheet for future reference.<\/p>\n
        \u2013 Clear <\/em>the form, resetting all fields for a new cost estimation.<\/p>\n
        This user-friendly design minimizes manual effort while ensuring that all essential data is captured efficiently. By integrating dropdown selections, dynamic filtering, and interactive features, the form sheet enables engineers to perform cost estimations with accuracy and speed, significantly improving efficiency compared to traditional manual calculations.<\/p>\n
        \"\"<\/p>\n
        Figure 4. <\/strong>Uploading Image<\/p>\n
        Archives sheet<\/strong><\/p>\n
        The Archives Sheet acts as a structured database that automatically records each cost estimate when the save button is clicked. It eliminates manual entry, reducing errors and improving documentation efficiency. Each entry is stored as a new row in a tabular format, preserving all key information from the Form Sheet in a clear, traceable layout.<\/p>\n
        The following columns make up the table:<\/p>\n
        \u2013 Photo, as visual reference of the component, uploaded in the form.<\/p>\n
        \u2013 Project, in which is entered the name of the project for which the calculation was performed.<\/p>\n
        \u2013 Component name, the specific name of the part or assembly being evaluated.<\/p>\n
        \u2013 Date, the timestamp of when the calculation was performed.<\/p>\n
        \u2013 Engineer, the name of the person conducting the cost estimation.<\/p>\n
        \u2013 Manufacturing Processes, the selected methods for production, split into six categories (I, II, III, IV, V, VI) to accommodate multi-step manufacturing workflows.<\/p>\n
        \u2013 Price, the final calculated cost based on material, labor, and manufacturing processes.<\/p>\n
        This format ensures that engineers can quickly access, compare, and analyze past estimates. The six-category breakdown supports detailed analysis of complex manufacturing sequences. Visual references enhance clarity when revisiting archived data (Fig. 5). Automation ensures that each entry is updated accurately and consistently.<\/p>\n
        The archive not only provides historical cost tracking but also supports process improvement and forecasting. As part of the 3C system, the Archives Sheet plays a critical role in maintaining data integrity and seamless integration across the tool\u2019s components.<\/p>\n
        \"\"<\/p>\n
        Figure 5. <\/strong>Price calculation and saving on archives<\/pre>\n
        Database sheet<\/strong><\/p>\n
        The Database Sheet is the backbone of the 3C system, storing all reference data for dropdowns, pricing, and unit conversions. It ensures that cost estimations are accurate, standardized, and error-free by supplying structured data to the Form Sheet in real time.<\/p>\n
        It holds predefined tables for materials, manufacturing processes, units, component categories, and engineers. Materials are categorized by supply type (e.g., Sheet, Bar, Tube) and include prices per kilogram for common materials like Steel, Aluminum, and Carbon Fiber.<\/p>\n
        Manufacturing processes are listed with cost per relevant unit (e.g., per minute, per cm\u00b2, or per piece), covering operations such as welding, coating, and bonding.<\/p>\n
        To convert inputs into usable values, machine speed data is used for cost-per-length calculations in cutting processes:<\/p>\n
        \u2013 Waterjet Cutting \u2013 915 [cm\/hour];<\/p>\n
        \u2013 Laser Cutting \u2013 12,200 [cm\/hour];<\/p>\n
        \u2013 Plasma Cutting \u2013 72,000 [cm\/hour];<\/p>\n
        \u2013 EDM Cutting \u2013 5 [cm\/hour].<\/p>\n
        Cutting cost is calculated using:<\/p>\n
        \"\"<\/p>\n
        (1)<\/p>\n
        Labor costs are also standardized by storing rates in EUR\/hour or EUR\/min, ensuring consistent time-based cost estimates. By centralizing and automating data handling, the Database Sheet allows for efficient, accurate, and adaptable cost estimation across diverse manufacturing scenarios.<\/p>\n
        3.2. Cost calculation methods<\/strong><\/p>\n
        Material cost formula<\/strong><\/p>\n
        The material cost formula used in 3C calculates the total cost of the material required for a component based on its mass and the unit price per kilogram. The formula is:<\/p>\n
        \"\"<\/p>\n
        (2)<\/p>\n
        Where:<\/p>\n
        \u2013 Mass (kg) is the weight of the material used for the component, entered in the form sheet.<\/p>\n
        \u2013 Price per kg (EUR\/kg) is retrieved from the Material and Pricing Table in the database sheet.<\/p>\n
        This method guarantees consistency and accuracy in cost calculations by automatically calculating the material cost depending on user input.<\/p>\n
        Labor cost formula<\/strong><\/p>\n
        The labor cost formula in 3C calculates the cost of labor based on the time required for manufacturing and the predefined hourly or minute-based labor rate. The formula is:<\/p>\n
        \"\"<\/p>\n
        (3)<\/p>\n
        Where:<\/p>\n
        \u2013 Time is the duration of labor required for the process, entered in either hours or minutes depending on the manufacturing process.<\/p>\n
        \u2013 Labor Rate is retrieved from the Labor Cost Calculation Table in the database sheet and is stored as either EUR per hour or EUR per minute.<\/p>\n
        If the labor rate is stored in EUR per hour and time is entered in minutes, the system automatically applies a conversion formula to ensure accurate labor cost calculations. This process maintains precision in cost estimations for various tasks.<\/p>\n
        \"\"<\/p>\n
        (4)<\/p>\n
        Process cost formula<\/strong><\/p>\n
        The process cost formula in 3C is designed to calculate the cost of a selected manufacturing method based on the required input variable.<\/p>\n
        The cost depends on the type of process, which determines the appropriate unit: centimeters (cm), square meters (m\u00b2), or minutes (min).<\/p>\n
        The general formula for process cost is:<\/p>\n
        \"\"<\/p>\n
        (5)<\/p>\n
        where the variable input is the user-defined value based on the specific process (length, area, or time), and the cost per unit is the predefined cost stored in the manufacturing process table in the database sheet, expressed as EUR per cm\u00b2 or min.<\/p>\n
        For cutting processes such as laser cutting, plasma cutting, waterjet cutting, and EDM, the required input is length (cm) of material being cut. The cost per unit is stored as EUR per h and is calculated using:<\/p>\n
        \"\"<\/p>\n
        (6)<\/p>\n
        The final formula used in the form sheet is:<\/p>\n
        \"\"<\/p>\n
        (7)<\/p>\n
        For coating and finishing processes such as painting, powder coating, anodizing, and plating, the required input is surface area (cm\u00b2). The cost per unit is stored as EUR per m\u00b2 in the database. The final formula is:<\/p>\n
        \"\"<\/p>\n
        (8)<\/p>\n
        \"\"<\/p>\n
        (9)<\/p>\n
        For welding and machining processes such as MIG, TIG, spot welding, and grinding, the required input is time (min). The cost per unit is stored as EUR per min in the database. The final formula is:<\/p>\n
        \"\"<\/p>\n
        (10)<\/p>\n
         <\/p>\n
        Based on the chosen process, the 3C system dynamically chooses which formula to apply. Technology guarantees accuracy and adaptability for various industrial procedures by organizing the cost computations in this manner.<\/p>\n
        3.3. Automation and user interaction<\/strong><\/p>\n
        3C simplifies cost estimation through an automated, user-friendly interface. Dropdown menus, preset formulas, and auto-calculations reduce manual input and errors. Component selections dynamically filter relevant processes, while unit conversions occur automatically.<\/p>\n
        Key features include buttons to calculate, save, or reset data, and an image upload option for traceability. By streamlining these tasks, 3C saves time and allows engineers to focus on decision-making rather than calculations.<\/p>\n
          \n
        1.  Case Study: Real-world application of 3C<\/strong><\/li>\n<\/ol>\n
          4.1. Component description & specifications<\/strong><\/p>\n
          Project Name: Folding Bracket<\/p>\n
          Component Name: LB_16<\/p>\n
          Material: Stainless Steel (0.409 kg)<\/p>\n
          Manufacturing Processes:<\/p>\n
            \n
          1. Laser Cutting<\/li>\n
          2. Bending (U-profile)<\/li>\n
          3. Grinding<\/li>\n<\/ol>\n
            \"\"<\/p>\n
            Figure 6. <\/strong>LB_16 Calculation<\/pre>\n
            4.2. Step-by-step walkthrough of cost estimation<\/strong><\/p>\n
            Select Project and Component:<\/p>\n