Business Management | Resources

Excel Guidance For Manual Warehouse Management - B2b Cross Border E Commerce

Nov 17, 2022 | Shivphool Singh and Konark Ogra

Introduction Warehouse management refers to the oversight of operations in a warehouse. This includes receiving, tracking, and storing inventory, managing shipping, workload planning, and monitoring the movement of goods. For any warehouse manager, Tracking the overall operating profit in terms of SKUs and customers is critical. All the warehouse management processes can be categorized into 3 subcategories- Demand Forecasting Inventory Management Tracking financial performance

Excel Guidance for Manual Warehouse management

Demand Forecasting- Forecasting models are one of the many tools businesses use to predict outcomes regarding sales, supply and demand, consumer behavior, and more. These models are especially beneficial in the field of sales and marketing.

Various forecasting models can be applied based on situational requirements. The chosen model must include the following things-

1- Use of available data source

2- It must consider the stage of the product’s life cycle for which it is making the forecast.

3- Model accuracy vs inherent cost trade-off i.e purpose of forecasting

4- A combination of qualitative, time series, and casual models for forecasting

Inventory Management and Strategic Warehousing- Inventory management plays a critical role in defining strategic warehousing. The right balance of desired SKUs plays an important role while fulfilling the demand and creating an effective impact in managing a healthy balance sheet. Inventory management always plays a trade-off between the cost of keeping the inventory and the revenue earning. In a direction, where overstocking or wrong stocking can affect negatively put inventory-keeping expense stresses. In the same manner, understocking will leads to unfulfilled demand leading to a loss of credibility with customers.

Tracking Financial Performance- The financial performance of a typical warehouse can be tracked in two terms-

SKUs financial to track the overall operating profit/loss
Customer wise financial to track the overall profit/loss

Ways of warehouse management

Manual and digital methods may both be used to monitor and control the warehouse's most crucial operations. Management approaches may be chosen based on several criteria, including the size of the business, the number of product lines managed, the needed operating profit margin, and the desired turnover rate.

Warehouses with a larger number of SKUs, fewer or no customers, and greater turnover rates are ideal candidates for a fully digital warehouse management system. Greater transparency and better control are the trade-offs for the larger initial expenditure required by this method of administration.

Smaller warehouses are more likely to adopt a manual management system. It may cause unbalanced management, inefficiency, and a host of other issues in this context. If these issues are resolved, the warehouse's overall performance may be optimized, making this tool ideal for those in charge of maintaining it.

Excel Guidance for Manual Warehouse management

Excel with proper modeling is one of the most powerful tools that can be used in manual warehouse management. For small-scale warehouses, It is an efficient and free-of-course tool that can be used for enhancing performance in different parts like inventory management, demand forecasting, and overall performance tracking.

Excel Model link- Rural_Handmade_DATA.xlsm - Google Sheet

Instruction Manual

The excel model is created for assisting warehouse management. It has mainly 4 sections i.e. forecasting, inventory management, monthly performance tracker, and live tracking dashboard for different parameters.

Forecasting Workbook Manual

1- Forecasting Model Sheet

Model Result	The model calculates the three-month forecast data for any SKU.

Basic Methodology	The model is designed based on winter's prediction methodology.
	It includes all three parameters- level, trend, and seasonality.
	All three smoothing factors are considered i.e. Alpha, Beta, and Gamma
	The Error is minimized post-process using a solver.

Getting Started	Enter the real demand for a month (let’s assume for April in 5th year)
	Calculate level, trend, and seasonal factor for the same month)
	Calculate RMSE for the same month.
	Minimize RMSE value using Solver to optimize alpha, beta, and gamma values.
	Calculate the forecast value for the next three months i.e May, June, and July.

Step-wise Instruction
Step-1	Fill the real demand for a single month ex. Apr in 5th year
Step-2	Drag the level, trend, and seasonal factors to auto-populate the value for the month.
Step-3	Use the below-mentioned formula to calculate the demand forecast for the next three months ex. May, June & July.
Step-4	Minimize the RMSE value for adjusting Alpha, Beta, and Gamma values using a solver.
Step-5	Use the below-mentioned formula to calculate the demand forecast for the next three months ex. May, June & July.

	May= (April level+1April trend) (April seasonal factor for the previous year)
	June= (April level+2April trend) (April seasonal factor for the previous year)
	July= (April level+3April trend) (April seasonal factor for the previous year)

Inventory Management Workbook Manual

2- Inventory management

Model Result	The model gives the day-to-day level inventory movement. It also triggers the reordering time and quantity needed to reorder with the supplier.

Basic Methodology	The model is designed based on EOQ and a continuous ordering system.
Basic Methodology	The service level is taken as 95% i.e. out of 100 demand cycles, It will be able to fulfill the demand completely in 95 cycles, and in the rest, the partial demand will be met.

Getting Started	Calculate the yearly demand and demand standard deviation using the backside calculation side
	Calculate EOQ, No of Orders that need to be placed yearly, and Cycle time.
	Calculate the safety stock and Reorder point for the ROP value setting.
	In the inventory management sheet, fill the details of day-wise retail and inventory received (if any)
	Trigger the reorder by EOQ value once the closing inventory of the day level goes down below ROP.

E0Q= ((2*Annual mean demand* Ordering Cost)/ (holding cost)) ^0.

No of orders= Annual mean demand/EOQ

Order cycle=365/No of orders

SS= z value @95% service level*(Std. deviation of demand over lead time)

ROP= mean demand *lead time + SS

Monthly Performance Tracker Workbook Manual

3- Monthly Performance Indicator

Model Result	The model gives the monthly financial performance in terms of operating profit. The profit track is reflected in two ways. Firstly, it shows the SKU-specific profit earned by the warehouse and in second way, It gives the overall profit calculation for a retail customer.

Basic Methodology	The monthly performance indicator sheet works on the basic idea of profit i.e. (Revenue - cost)/ cost. It allocates all the fixed and variable costs of warehouse management to SKUs in proportion to the revenue generated.

Getting Started	Update the no of specific SKUs sold wrt to customer data.
	Update the overall manufacturing cost and selling price per SKU wrt to customer mapping.
	Put the overall monthly warehouse fixed and variable management cost in "Warehouse total Facility Storage & Ops cost".

Dashboard Workbook Manual

4- Dashboard

Model Result	The model gives the day-to-day inventory received (If any), opening inventory, SKU sold, and Closing inventory visual representation. It is a dynamic dashboard that will keep on changing based on real-time data.

Basic Methodology	The dashboard works based on the data sheet generated i.e. forecasting, Inventory, and backside calculation.

Getting Started	Update the item received (if any) and SKU sold in the inventory management sheet on daily basis.
	Update the real demand every month in the forecasting sheet.
	Check the visual representation of each critical data in the live dashboard.

Coconut Shell Craft Suppliers: Eco-friendly, Unique, And Affordable

Nov 17, 2024

Coconut trees are often called the "tree of life" in Kerala due to their versatility and contribution to the state's economy. Among the many uses of coconut, one that stands out in its artistry is Coconut Shell Craft, a unique blend of tradition and eco-friendliness. This ancient craft has gained renewed attention in recent years as artisans are combining tradition with modern design, creating products that are both functional and beautiful.

Read more

Community | People

Dum Dum, Kolkata: The Hub Of Guitar Craftsmanship For B2b And Wholesale Markets

Nov 17, 2024

Guitars first made their appearance in Europe around the 12th century, and from there, they made their way to America. Early guitars were made from hollow wood, creating an acoustic resonance that gave the instrument its signature sound. The development of the electric guitar and the use of synthetic materials came much later, but the traditional craftsmanship of acoustic guitars remains a cherished art.

Read more

Community | People

Clay Jewelry From Dumdum, Kolkata: A Wholesale Opportunity Blending Tradition And Modern Craftsmanship

Nov 17, 2024

Jewelry has always held a significant place in Indian culture, particularly in the hearts of women. The craft has evolved over centuries, preserving its traditional roots while adapting to modern aesthetics. Among the many forms of jewelry in India, clay jewelry, particularly handmade pieces, stands out for its natural appeal and versatility. This form of ornamentation, often made with materials like terracotta, jute, bamboo, and modern compounds like epoxy, has become a fashionable substitute for precious metals. With bold designs and vibrant colors, clay jewelry complements both ethnic and western attire, offering an eco-friendly and cost-effective alternative to traditional jewelry.

Read more

Join maker community

Excel Guidance For Manual Warehouse Management - B2b Cross Border E Commerce

Recommended

Coconut Shell Craft Suppliers: Eco-friendly, Unique, And Affordable

Dum Dum, Kolkata: The Hub Of Guitar Craftsmanship For B2b And Wholesale Markets

Clay Jewelry From Dumdum, Kolkata: A Wholesale Opportunity Blending Tradition And Modern Craftsmanship