improving cash to cash cycle by improving demand forecasting techniques

8/6/2019 Improving Cash to Cash Cycle by Improving Demand Forecasting Techniques

1/20

1Improving Cash to Cash Cycle by improving Demand Forecasting Techniques

ACKNOWLEDGEMENT

Words are not substitute for thoughts and feeling. I take this opportunity to thank one

and all, whose contribution to this project cannot be forgotten.

I express my sincere thanks to Dr. Gururaj H Kidiyoor , Dean (Student Affairs), TAPMI,

for his encouragement and valuable suggestions on this project work.

I am grateful to Mr. Ramesh N.Chandra, Vice President Operations, CEMA Electric

Lighting Products India Pvt Ltd., who helped me all along the project period with directions for

learning. I am also indebted to Mr. P V Jose, Sales Manager, CEMA Electric Lighting Products

India Pvt Ltd., who was ready to offer the required assistance and practical insights into a wide

array of topics as and when needed.

I gratefully remember the cooperation and assistance of my project guide and usher Dr.

Ajith Kumar, without whom this project would not have taken shape. I also thank the other

faculty members of TAPMI for their assistance and help. I wish to thank them with profound

reverence not only for having initiated me to come out with this project, but also for giving their

mental and oral support throughout this project work and for solving my problems.

At this juncture, I also wish to thank my beloved friends from Placement committee for

providing me a great learning opportunity.


2/20


About the Company:

CEMA ELECTRIC LIGHTING PRODUCT INDIA PVT. LTD.

CEMA Electric Lighting is among the top 5 lighting solutions companies in India. The

company offers a full range of lighting products and accessories and has a major presence

across India catering to around 2 lakh retail outlets through its network of over 600 distributors

and 15 branch warehouses.

The company manufactures and markets a wide variety of lighting products including

Compact Fluorescent Lamps (CFL), Tubular Fluorescent Lamps (FTL), High Intensity Discharge

Lamps (HID) and Metal Halide Lamps (MH), Domestic & Commercial Light Fixtures.

CEMA owns the CEMA brand and also possesses a license from General Electric Corp. to

manufacture and market consumer lighting products under the GE brand name in India, Sri

Lanka, Bangladesh, Nepal, Bhutan and Maldives. Both the CEMA and GE brands have a high

equity and acceptability in the Indian lighting market.


3/20


About the Project:

The project was to find the scope of improving Cash to Cash Cycle by Improving

Demand Forecasting Techniques. This part aimed at finding the amount that can be effectively

saved if the forecasting errors are removed at each and every branch. The second part of the

project was to find the right focus for each and every branch, which in turn for the company as

a whole. CEMA recently purchased forecasting software (Forecast Pro), and wants to review its

performance after a year

Objectives:

Objective1:

To find the scope for improvement in Cash to Cash Cycle by studying the current performance

of the forecasting system, this included Identifying the areas which can be improved Identifying the factors in selected areas Studying the performance selected factors Suggesting improved methods Finding out how much money that could be saved

Objective 2:

To find the current focus for each and individual branch and company as a whole, this included

Product performance analysis Studying product movement within a branch Studying product movement within a region Suggesting the right focus for the branch Suggesting the areas of concern and branches to be taken care of

Things Provided:

Forecast and Sales data of various SKUs for all the branches for two years Supplier details such as location, lead time etc., Stock at close for each and every month Selling price of all the SKUs

Tools Used:

Microsoft Excel


4/20


Summary

The project deals with improving the Cash to Cash cycle of the organization as a whole,

which was achieved by studying the historical Forecast-Sales variation of various product

portfolios across 15 sales hubs of the company allover India.

Improved forecast accuracy leads to many downstream improvements in a variety of

business areas such as customer service and asset management. Understanding and predicting

the elements that make up the final SKU forecast forms the basis for improving Cash to Cash

Cycle, first phase of the project focused on finding the variables that directly affect the sales

and focus of each branch.

With more than 300 SKUs in consumer, commercial, roadway and industrial lighting, the

first and foremost variable that was found to affect the sales of individual branches was

identified as the Focus. To identify the right focus area for each and every branch a Product

performance analysis was done in a national scale and compared it with regional and individual

branch performances. A list of Top 20 SKUs and Bottom 20 SKUs with respect to sales

revenue and no of units was done for regional level and individual branches and compared with

each other, from this study the areas of concerns, which should be given more importance and

poor performing SKUs which should be removed from individual branch or region as a whole

were identified.

With over 25 suppliers and services catering to around 2 lakh retail outlets through its

network of over 600 distributors, the second most important factor affecting the organizationsales and increasing the Cash Cycle was identified as logistical lag. A detailed study about the

forecast report period and lag in delivery was done and the average sales loss for every branch

due to logistical lag was calculated. Comparing the branch location and the supplier location

new forecast report date was advised for individual SKUs which were problematic.

The third major problem was found in the forecast itself. Due to nature of some SKUs

they were over forecasted (optimistic forecasting error) while some SKUs were under

forecasted (pessimistic forecasting error) and the company was found ignoring the seasonal

changes in their forecasting process. The average inventory carryover cost due to optimisticforecasting, seasonal errors and logistics along with loss in sales due to pessimistic forecasting,

seasonal errors, and logistics was computed and a weighted average score was given in terms of

loss incurred by every branch was given. This can be used to identify the important areas of

concern in each and individual branch along with branches to be taken care immediately.


5/20


Studies Conducted:

Product Performance Analysis Logistical lag study Forecasting bias Seasonal product movement

Product Performance Analysis:

Objective of the study:

This study was conducted to find the products which require more focus and things that

should be removed out of the portfolio for overall good of the company. This study was also

conducted to compare branch wise product performances and find the reasons which resulted

in top SKUs of other branches fail miserably in few places.

Method Used:

First individual sales data for each and every branch was consolidated in terms of sales

revenue and sales units. Top and bottom twenty SKUs of each and individual branch and region

were found in terms of SKUs and in terms of sales revenue. This list was compared with all other

branches to find how the branch was performing, if a branch was different from all others, it

was selected for further study. The second part of the study involved in studying deeply in to

Weightage associated with each and individual product in terms of sales and no. of SKUs sold.

This helped me to identify the products which should be given more importance/focus and the

products where focus should be removed.

Shortcomings:

The study fails to find the reasons for any failure or problem that is associated with the

product/branch. This study shows only that there exists a problem associated with such and

such product and with the branch. This study did not take into account of the lost opportunity

of various products due to forecast and logistical errors.

Logistical Lag study:


For a company which sources 100% of its final products from outside suppliers with

almost all of the CFL and GLS (which totally accounts for 90% of sales) procured from the North


6/20


region, logistical lag should be minimized, keeping this in mind, the average loss in sales, in turn

resulting in excess inventory was to be found to improve its Cash Cycle.

Method Used:

By taking the shipment in transit, which was a result of late forecast-reporting, which inturn resulted in loss of sales and excess inventory, the exact value lost in terms of sales profit

and carryover cost was computed for each and every branch.

Seasonal product movement:


This study was conducted to identify any seasonal or cyclical behavior within the

product sales movement.

Method Used:

Graphical method was used to identify the cyclical movements. Sales movements of

various products for the last 3 years were plotted together to identify the cyclic movements. If

the cyclic movement of the product was not accounted, the amount which could have been

saved by avoiding that mistake was calculated and accounted for improvements.

Forecast Bias and Seasonal errors:


This study was done to find the exact value of excess inventory which was more than the

normal level to service 95% of the cases (95% service levels), loss in sales due to pessimistic

forecasting of some products and loss in sales in excess inventory due to seasonal changes,

which was not taken into account while calculating the forecasts.

Method Used:

First the inventory levels as a function of last five month sales, required for 95% service

levels, as a historical trend of different product groups were calculated and this number was

compared with the inventory levels and the causes for deviations, if any, was categorized into

the known error terms. The error terms were then categorized as Pessimistic forecasting error,

Optimistic forecasting error, Cyclic/Seasonal error and the Logistical Lag error.


7/20


Study Results and Conclusions:

Based on the study carried out, the performance of each and every branch is reported to

the company on various parameters. The performance is compared with its regional peers and

the national average.

Since its not possible to include all the parameters and all the branches within the given

word limit of the project, only important parameters for three of one branch of the East region,

is explained here to illustrate the structure of the results given to the company. Summary for all

the 15 branches is given in the end.

Results for Branch wise Study:

Branch: Kolkata

Region: East

Kolkata is an average branch in terms of sales, occupies 2nd

position in terms of sales in

eastern region and 10 in national level. Kolkata accounts for 4.07% of national sales and 2.30%

of all units sold in the country by CEMA. The branch is having 120 active SKUs and

approximately 2 crores in sales for the financial year 2010-2011. Out of 175 active SKUs of

CEMA, Kolkata focuses only on 120, but still the last twenty SKUs accounts for less than 0.5% of

the overall sales of the branch in both Sales volume classification and in Sales units

classification.

Table 1.1

Branch Average SalesSales Contribution to

the country %Ranks

Portfolio

Portfolio Rank

Units Amount Units Amount Units Amount Size %Rank

Kolkata 579879 19449341 2.30% 4.07% 11 10 120 68.5 9

Contribution of Top

20

Contribution of Bottom

20

Units Amount Units Amount

91.74% 86.75% 0.14% 0.19%


8/20


Top SKUs of the branch:

Top20 by sales revenue:

Table 1.2

By Sales Amount(Yearly Fig)

Top20 by sales value

Name SV SUSV%Within

Branch

SU%Within

Branch

CEMA CFL ENDURA T4 15W 2U 65K B22 3086955.7 39205 15.87% 6.76%


CEMA CFL WHIRLITE T3 23W SPIRAL 65K B22 1880791.9 16452 9.67% 2.84%

CEMA FTL TC3 4 40W T12 1860012.5 68142 9.56% 11.75%

CEMA HYPHEN-D T-5 batten 28w 818640.0 2508 4.21% 0.43%

CEMA GLS CLEAR 100W 230V BC CL/150PK 711017.4 114989 3.66% 19.83%


CEMA CFL TINY T3 5W 2U 65K B22 621654.6 7989 3.20% 1.38%CEMA CFL TINY T3 8W 3U 65K B22 537143.8 6456 2.76% 1.11%





CEMA Trende T-8 36w Elec. Batten 362340.0 1647 1.86% 0.28%


CEMA FTL FSU STARTER 20/40W 230-250V 341860.0 75480 1.76% 13.02%


CEMA Prajjwal 36/40w FTL ballast 214345.0 3263 1.10% 0.56%CEMA CFL WHIRLITE T3 15W SPIRAL 65K B22 209909.8 1810 1.08% 0.31%

CEMA CFL BIGLITE T5 65W 4U 65K B22 203307.4 527 1.05% 0.09%

CEMA FTL ENERGY SAVER 36W 202440.0 8040 1.04% 1.39%

86.75% 83.08%

As we can see, 16% of the top SKUs in terms of sales value results in around 87% of sales

in terms of volume and they result in 83% of all the units sold in the branch. The company

wants to find out the missing links in some of the poor performing SKUs which are performing

well in other branches.


9/20


Top20 by sales units:

Table 1.3

Top20 by sales units

Name SV SUSV%Within

Branch

SU%Within

Branch




CEMA FTL TC3 4 40W T12 1860012.5 68142 9.56% 11.75%




CEMA CFL WHIRLITE T3 23W SPIRAL 65K

B221880791.9 16452 9.67% 2.84%

CEMA GLS 25W 230V BC CL /150 pk 78927.0 13350 0.41% 2.30%



CEMA CFL TINY T3 5W 2U 65K B22 621654.6 7989 3.20% 1.38%


CEMA GLS CLEAR 200W 250V BC CL 69000.0 5520 0.35% 0.95%


CEMA NIGHT LAMP 15W 250V BC YELLOW 22593.4 3306 0.12% 0.57%

CEMA Prajjwal 36/40w FTL ballast 214345.0 3263 1.10% 0.56%

CEMA NIGHT LAMP 15W 250V BC RED 19944.0 2880 0.10% 0.50%


B22 393910.7 2580 2.03% 0.44%

CEMA NIGHT LAMP 15W 250V BC MILKY 17289.0 2510 0.09% 0.43%


79.60% 91.74%

As we can see, like all other northern branches, GLS is completely absent from the top

SKUs by sales list even though we can see many of those SKUs feature in the Top20 by sales

units list. One interesting fact to be noted is that the top 20 SKUs of the branch accounts for

85% and 92% of all its sales revenue and the units sold respectively.


10/20


Bottom SKUs of the branch:

Bottom 20 by Sales:

Table 1.4

Bottom20

Name SV SU SV%Within Branch SU%WithinBranch

CEMA Candle Clear 25W B22 412.5 50 0.00% 0.01%




CEMA Candle Coated 25W E14 1202.5 130 0.01% 0.02%


CEMA Candle Clear 25W E14 1268.8 145 0.01% 0.03%

CEMA Classic Round Clear 40W E14 1312.5 150 0.01% 0.03%

CEMA Maxlite CFL ballast 10-13w 240v 1550.0 25 0.01% 0.00%CEMA Classic Round Clear 60W E14 1750.0 200 0.01% 0.03%

CEMA Classic Round Coated 40W E14 1850.0 200 0.01% 0.03%


CEMA HPSV 70 E 2090.0 11 0.01% 0.00%




CEMA Candle Clear 40W E27 2362.5 270 0.01% 0.05%


CEMA HPSV 150 T 2700.0 12 0.01% 0.00%CEMA Candle Coated 40W E14 2775.0 300 0.01% 0.05%


0.19% 0.58%

The real concern is with the bottom SKUs of the branch. The last 30 of the branch

accounts for only 0.5 % of the branchs sales and no of units sold. Some particular SKUs product

categories like CEMA HPSV and CEMA Whirl light etc. can be removed from the portfolio as they

account for less than 0.1% of the sales. The inventory charges of HPSV and Whirl light will

certainly exceed the profit margins. Though Whirl light is categorized under CFL retrofit, it can

be seen that it is failing miserably. One interesting thing to note is that CFL Tiny and Maxlite

having some of SKUs which are not having any sales, while some of its peers are doing well in

the same region. These inactive Tiny/Maxlite pair carries on some amount of inventory

throughout cycle period.


11/20


Bottom 20 by Sales Units:

Table 1.5

Bottom20

Name SV SU SV%Within Branch SU%WithinBranch

CEMA MH IBU 70w 3680.0 8 0.02% 0.00%

CEMA CFL MAXLITE-D 18W/2700K 2 Pin 0.0 10 0.00% 0.00%


CEMA HPSV 70 E 2090.0 11 0.01% 0.00%

CEMA HPSV 150 T 2700.0 12 0.01% 0.00%

CEMA HPSV 150 E 3480.0 12 0.02% 0.00%

CEMA 400W MH SE-Tubular-E40 8730.0 18 0.04% 0.00%

CEMA Maxlite CFL ballast 10-13w 240v 1550.0 25 0.01% 0.00%

CEMA CFL WHIRLITE T3 23W SPIRAL 65KE27

4292.8 30 0.02% 0.01%


B224370.0 30 0.02% 0.01%

CEMA HPSV 70 T 4311.0 35 0.02% 0.01%


CEMA FTL T5 28W/827 3000.0 50 0.02% 0.01%

CEMA CFL TINY T3 5W 2U 2700K E27 3900.0 50 0.02% 0.01%



B225950.0 50 0.03% 0.01%


7059.6 60 0.04% 0.01%

CEMA CFL WHIRLITE T3 9W SPIRAL 65K E27 7679.1 70 0.04% 0.01%

CEMA HYPHEN-E T-5 batten 14w 19600.0 70 0.10% 0.01%


CEMA Maxlite CFL ballast 18w 240v 4975.0 75 0.03% 0.01%

0.50% 0.14%

Problematic areas:

The overall study indicates that forecasting errors accounts for most of the problems in

the branch. With almost 40% of the excess inventory and 40% in loss of sales is due to

forecasting errors. This excess inventory is calculated by calculating the excess inventory which

was more than the safety stock required for 95% service levels.

As far as logistics as considered the branch is below par than its peers and has to be

focused on.


12/20


Seasonal Changes:

Seasonal changes in products such as CEMA Maxlite and CEMA HPSV in all the volt

ranges are not considered buy the company. This resulted in excess inventory in the branch for

most of the time in the last two years.

Logistical Problems:

Logistical problems are observed with the GLS suppliers form Noida and Delhi, so it is

advised for the branch to revise their lead time estimates and change their order placing cycle

to avoid further delays in from areas.

Monthly Savings that can be achieved:Table 1.6

Branch Name Kolkata

Sales as a % of national sales Weightage 4.07%Average sales Rs 1620778.39

Average excess stock at close Rs 2490725.57

Average deficit in stock Rs 583577.82

Average inventory in excess due to

forecast errorsRs 1245362.79

Cost of capital WACC% (industrial average) 11.00%

Error type

Forecast Pessimistic 40.00%

Optimistic 40.00%Seasonal 5.00%

Logistics 7.00%

Savings that can be made if errors are corrected

Unrealized profit=Profitmargin*avg deficit in

stock*pessimistic28011.74

Savings from reduced inventory by

correcting optimistic forecast

=optimistic*avg excess inventory

excess to req safety stock

inventory*WACC+Holding cost

79703.22

Savings from reduced inventory by

correcting optimistic forecastSavings from improved logistical

services

=optimistic*avg excess inventory

excess to req safety stockinventory*WACC+Holding cost 7540.05

Savings from correcting seasonal

changes5385.75

Total Gain/month 120640.75

As we can see there is a lot of space for improvement in this branch and we can save as

much as 120640 Rupees per month from this branch.


13/20


Results for Branch wise Study:

Region: East

East region consists of Kolkata, Guwahati and Ranchi. This is the smallest region, both in

terms of number of branches and in terms of sales. Last year the region accounted for 5.3Crores in sales. The region holts 120 portfolios and accounts for 5.5% of overall sales in terms of

units sold and 11.2% in terms of value of sales. Though it governs only 68% of the active SKUs of

CEMA, the sales in the region are highly skewed. The 16% of the top products accounts for 83%

and 91% in terms of number of units and value sold.

Table 2.1

Branch Average SalesSales Contribution

to the country %Ranks Portfolio

Units Amount Units Amount Units Amount Size % RankEast 1395661 53487360 5.55% 11.19% 4 4 120 68 4

Contribution of Top

20

Contribution of Bottom

20

Units Amount Units Amount

83.34% 91.32% 0.07% 0.28%

Guwahati is the top performer in the branch, followed by Kolkata and Ranchi. Due to

less number of branches in the region and presence of poor performers like Ranchi in the list of

branches, this region is not having much impact on the overall performance of the company.

Due to less importance, these branches are not given required attention, there

by the logistical lags in the branch as a whole is high, and the geographical disadvantage

magnifies it.


14/20


Top SKUs of the Region:

Top20 by sales revenue:

Table 2.2

Top20

Name SV SU SV%WithinBranch

SU%WithinBranch





B223144677.6 31179 5.88% 2.23%

CEMA FTL TC3 4 40W T12 3054891.5 110236 5.71% 7.90%








B22860909.4 5690 1.61% 0.41%




B22651789.9 3430 1.22% 0.25%



CEMA Trende T-8 36w Elec. Batten 509740.0 2317 0.95% 0.17%



B22476265.7 4225 0.89% 0.30%


91.32% 81.92%

As we can see, 16% of the top SKUs in terms of sales value results in around 91% of sales

in terms of volume and they result in 82% of all the units sold in the branch. Though this region

is not been advertised well the top performers all over the country are having a good presence

in this region. The GLS sector is absent in the region as a whole, which is a point to note as the

sales are fully driven by the CFL sector.


15/20


Top20 by sales units:

Table 2.3

Top20

Name SV SUSV%Within

Branch

SU%Within

Branch




CEMA FTL TC3 4 40W T12 3054891.5 110236 5.71% 7.90%






B223144677.6 31179 5.88% 2.23%



CEMA GLS 25W 230V BC CL /150 pk 113244.8 18893 0.21% 1.35%


CEMA GLS CLEAR 200W 250V BC CL 124625.0 9970 0.23% 0.71%


CEMA NIGHT LAMP 15W 250V BC RED 46188.0 6620 0.09% 0.47%

CEMA NIGHT LAMP 15W 250V BC YELLOW 45537.4 6546 0.09% 0.47%


B22860909.4 5690 1.61% 0.41%

CEMA NIGHT LAMP 15W 250V BC MILKY 36693.0 5250 0.07% 0.38%


CEMA NIGHT LAMP 15W 250V BC FROSTED 32058.0 4720 0.06% 0.34%

83.34% 93.64%

In number of units sold, the region is following the similar trend as the top performers in

terms of sales volume. It clearly indicates that 84% of the products merely accounts for 7

percent of the units sold. The company should take this into account and decide whether it

should keep those SKUs or should they change their sales strategy for these products.

Bottom SKUs of the region:

Bottom 20 by Sales:

The real concern is with the bottom 70 SKUs of the branch. The last 50 of the branch

accounts for only 3 % of the branchs sales and no of units sold. The bottom 30 of the branch

accounts for only 0.5% of the sales. Some SKUs product categories like CEMA HPSV and CEMA


16/20


Whirl light, CFL Tiny, CFL Candle light, Maxlite, Classic round etc, Can be shelved so that the

Nagpur can focus on other areas.

Table 2.4Bottom20

Name SV SUSV%Within

Branch

SU%Within

Branch






CEMA HPSV 70 E 2090.0 11 0.00% 0.00%

CEMA Candle Clear 25W E14 2318.8 265 0.00% 0.02%CEMA HPSV 150 T 2700.0 12 0.01% 0.00%



CEMA CFL MAXLITE-S 11W/2700K 2Pin 2923.6 80 0.01% 0.01%


CEMA MH IBU 70w 3680.0 8 0.01% 0.00%





4638.4 40 0.01% 0.00%





0.12% 0.28%

As we can see the bottom 20 SKUs by volume accounts for only 0.12% of the sales. But

the companys policy of equally allocating the overheads over the entire product range along

with the advertisement costs will make these products costlier to have it in the overall portfolio,

so the company should take a look into their accounting principles based on this.


17/20


Bottom 20 by Sales Units:

Table 2.5

Bottom20

Name SV SUSV%Within

Branch

SU%Within

Branch

CEMA MH IBU 70w 3680.0 8 0.01% 0.00%

CEMA CFL MAXLITE-D 18W/2700K 2 Pin 0.0 10 0.00% 0.00%

CEMA HPSV 70 E 2090.0 11 0.00% 0.00%

CEMA HPSV 150 T 2700.0 12 0.01% 0.00%

CEMA HPSV 150 E 6180.0 24 0.01% 0.00%


CEMA 70W MH DE RX7S-DL 6625.0 25 0.01% 0.00%

CEMA HPSV 400 T 11700.0 36 0.02% 0.00%


E274638.4 40 0.01% 0.00%

CEMA HPSV 70 T 5789.0 47 0.01% 0.00%

CEMA 250 W MH SE-Tubular-E40 22080.0 48 0.04% 0.00%




CEMA CFL WHIRLITE T3 9W SPIRAL 65K E27 7679.1 70 0.01% 0.01%






CEMA 70W MH T G12 DL 49875.0 95 0.09% 0.01%

0.30% 0.07%

The same trend is followed in terms of number of units sold as an average across the

months. The bottom 20 accounts for only 0.3% of overall sales.

Problematic areas:

The overall study indicates that forecasting errors accounts for most of the problems in

the branch. With almost 43.3% of the excess inventory and 46.6% in loss of sales is due to

forecasting errors. This excess inventory is calculated by calculating the excess inventory which

was more than the safety stock required for 95% service levels.

As far as logistics is considered the company is below par than its peers and should be

taken to account.


18/20


Seasonal Changes:

Seasonal changes in products such as CEMA Candle Clear and CEMA Classic Clear in all

the volt ranges are not considered buy the company. This resulted in excess inventory in the

branch for most of the time in the last two years.

Logistical Problems:

Logistical problems are observed with the GLS and CFL candle suppliers form

Noida and Delhi, so it is advised for the region to revise their lead time estimates and change

their order placing cycle to avoid further delays in from areas.

Monthly Savings that can be achieved:

Table 1.6

Branch Name East

Sales as a % of national sales Weightage 11.19%

Average sales Rs 4457280.04

Average excess stock at close Rs 4457280.15

Average deficit in stock Rs 1368571.04

Average inventory in excess due to forecast

errorsRs 2476006.11

Cost of capital WACC% (industrial average) 11.00%

Error type

Forecast Pessimistic 43.33%

Optimistic 46.67%

Seasonal 4.33%

Logistics 4.00%


Unrealized profit=Profitmargin*avg deficit in

stock*pessimistic71165.69

Savings from reduced inventory by correcting

optimistic forecast

=optimistic*avg

inventory*WACC+Holding cost

176621.77

Savings from improved logistical services 9911.50

Savings from correcting seasonal changes 10737.46


As we can see there is a lot of space for improvement in this branch and we can save as

much as 2.6 lakhs Rupees per month from this region.


19/20


Summary:

The consolidated list for each and every branch and regions were studied and the items

which should be given importance/removed from the portfolio were found. The overall

importance that should be given to different issues in each and every branch was highlightedand suggestions were given to the company. Though the forecasting models is found to be not

working in most of the cases, it can be customized based on the suggestions given, ignoring

forecasting models will make the company fail miserably in meeting the demands from the

market. One of the biggest limitations to the study is that it gives the ultimate benchmark after

the occurrence of the event; we cannot say with hundred percent sureties that it can be

achieved by improving the practices. Its like a company with 100% efficiency, a benchmark.

Table 3.1

Branch Average Sales Sales Contribution tothe country %

Ranks Portfolio

Units Amount Units Amount Units Amount Size % Rank

Kolkata 579879 19449341 2.30% 4.07% 11 10 120 68 9

Guwahati 710637 28098542 2.82% 5.88% 10 7 100 57 13

Ranchi 105145 5939478 0.42% 1.24% 15 15 70 40 15

East 1395661 53487360 5.55% 11.19% 4 4 120 68 4

Ahmedabad 5550729 87707115 22.06% 18.36% 1 1 161 92 2

Bhiwandi 4824246 67464610 19.17% 14.12% 2 2 162 92 1

agpur 719848 10256144 2.86% 2.15% 9 14 100 57 13West 11094823 165427869 NA NA NA NA 162 92 NA

Delhi 411416 11139015 1.63% `2.33% 14 12 138 78 6

Ghaziabad 1775446 54777736 7.06% 11.46% 6 3 152 86 3

Jaipur 1197504 26397670 4.76% 5.52% 7 8 119 68 11

Dehradun 448783 10896550 1.78% 2.28% 13 13 120 68 9

Zirkapur 547606 16312311 2.18% 3.41% 12 11 138 78 6

orth 4380755 119523283 NA NA NA NA 152 86 NA

Bangalore 2215606 36024337 8.80% 7.54% 4 5 140 80 5

Cochin 1903223 44301162 7.56% 9.27% 5 4 116 66 12

Chennai 983438 24040231 3.91% 5.03% 8 9 142 81 4

Hyderabad 3191070 35013025 12.68% 7.33% 3 6 127 72 8

South 8293337 139378755 NA NA NA NA 142 81 NA

India 25164576 477817268 105.55% 111.19% NA NA 175 100 NA


20/20

20I i C h t C h C l b i i D d F ti T h i

The overall savings that can be done in a national level is 23 lakhs per month as

consolidated and shown in Table 3.2.

Table 3.2

Branch Name India

Sales as a % of national sales 111.19%

Average sales 44275385.71

Average excess stock at close 42683309.73

Average deficit in stock 13820917.41

Average inventory in excess due to forecast

errors21589020.90

Cost of capital 11.00%

Error type

Forecast 31.74%

46.51%

4.93%

Logistics 9.75%


Unrealized profit 526419.97

Savings from reduced inventory by correcting

optimistic forecast

1536273.56

Savings from improved logistical services 201203.33

Savings from correcting seasonal changes 101749.19


Though the analysis is showing promising returns by removing the forecasting errors and

concentrating on the logistical lag along with improved focus, the analysis fails to capture the

important picture, that is some of the SKUs, even if they sell only 2-3 per month, they only form

a correct mixture or a bundle for the product group as a whole, absent of those small product

groups may have a huge impact over other sales, the analysis fails to take a look at that

perspective as, that is way too beyond scope of this study.

Reference:

Consolidated sales Data of CEMA Electric Lighting Products India Pvt. Ltd., for thefinancial year 2010-11

Operations Management, Jae K. Shim, Joel G. Siegel.

improving cash to cash cycle by improving demand forecasting techniques

Documents