Formula Array (II)

When I drill down the formula array, I found that it has reduced much the necessity of creating VBA function.  As you know, all the Excel functions are optimized internally; for the same functional effect, Excel built-in functions have much less overhead than VBA function.  Besides, formula array adds even more capacity to built-in functions.  I would like to quote below 2 examples:

1. If function which adds “or” / “+” and “and” / “*” operators

2. Vlookup function: columns summation

Are they marvelous?  I cannot imagine how powerful the formula array is.  I only know that there are many rooms to exploit.  At the same time, it may lead to re-thinking of Excel function usage.

Formula Array (I) – A powerful techniques in Excel

It has been many years I have not had any significant advancement with my Excel skills.  I become more focusing on template design in problem solving (algorithm).  Out of which is a technique in calculating the available inventory of 1 materials, after meeting the demand within the lead time, for other usage beyond any plan.  To be more elaborate, I quote the following example in bullet point:

1. The lead time of RM-1 is 10 weeks, that is, place order to supplier on week 1, you will get the materials received on week 11
2. Now it is week 1; the inventory balance is 15,000 in the factory, every week from now to week 11, there are scheduled demand (production schedule) and supply (goods received).  Assume that there is no unmet demand within the coming 10 weeks
3. A sudden order (order-X) comes and it requires RM-1.  The question is how many RM-1 could be available for this sudden order or how many in each week (Week 1 to 11), after the scheduled demand & supply, could be available for each week production of this sudden order.  As you know, if we place additional order to the supplier of RM-1 now, we can only get the goods received on Week 11.  If we only make use of the Week 11 receipt of RM-1, we may not be able to meet the customer’s urgency requirement.  At best, we need to utilize the available RM-1 without affecting the confirmed demand.
4. To resolve this, conceptually, I use a retroactive calculation of RM-1 from Week 10 back to Week 1.
   1. On Week 10, if the supply is 850 and the demand is 700; therefore, we can allocate 150 to order-X; however, if the supply is 630 and the demand is 890, gap is 260, then we need to reserve the inventory balance of week 9 to close the gap of week 10 (260)
   2. Assume that there is no demand and supply from week 1 to week 9, in short, we need to reserve 260 out of 1,500 from the week 1 inventory.  Therefore, 1,240 can be allocated to order-X on Week 1
   3. By all means, the real world situation should be more complicated.  There should be demand and supply from Week 1 to Week 9
   4. Then assume that on Week 9, the demand is 1,000 and the supply is only 830, the gap is therefore, 170; together with the gap of week 10 – 260; the culminated gap is 430; it needs the inventory of week 8 to close the gap of Week 9 & 10.
   5. Assume that there is no demand and supply from Week 1 to Week 8, the gap of 430 will required the appropriation of Week 1 inventory; therefore, 430 out of the 1,500 of week 1 inventory is reserved and only 1,070 is available in Week 1 for order-X immediate consumption

When I figured out the above mechanism, I was feeling excited and included this in one of my Excel training course as well as the planning mechanism of my existing company which took even longer period in the application development.

Then, few weeks ago, I was stuck up with the thinking if it was really necessary to write a VBA function to solve a problem.  I tried to google any alternative.  I came to realize that there exists a wonderful functional tools of formula array in Excel.  Take a look of the following screen dump.  I need to sum the products of column c and column d for those cells with column b contains “Dick” in the cell content.  This is the solution after I digest the online teaching of formula array.  Bravo!  Otherwise, I need to write a VBA function with the loop to examine each cell content in column b in order to do the sum of products of column c & column d.

What does it mean to me?

1. Sum can be sum(c5:c12*d5:d12) which is equivalent to sumproduct
2. It performs loop function which is looping each cell in B5:B12 to check for “Dick” though it is quite wired for the function of Find to contain this loop function!
3. Same loop capability functions in If.  Awesome!
4. However, there are more to explore: some functions, like vlookup, index, have limitation in doing formula array.  It requires more effort to sort out the limitation
5. Above all, it also needs to dig out how each other function could encapsulate the formula array function!

It goes without saying that if you are not doing data model in Excel, you will not share my exhilaration.  If you are not believing Kaizen (there is always room for improvement), you will not share the happiness of knowing that Kaizen is working all the times and everywhere.  Unless you believe it, you will not keep thinking that there is always a better solution!

Our people are everything!

Every year, there is a budget and there is difficulty.  Every year is difficult and my life as well.  I am always struggling with the impasse of budgeting, either over-commit and under-perform (always) or under-commit and over-perform (seldom happen).  However, when the vicious cycle starts, it will never end.  Even I try the best to tell the truth, we are given another worksheet of “stretch initiatives”.  Then who is to blame?  The controversy of principal-agent is never resolvable.  A short term compromise between the principal (the HQ) and the agent (the needy local management) is just to trigger the next complicated compromise of next year.

Another critical factor is never resolvable – motivation to the employees; to me, I am more concerned with the operators as they generate the efficiency though the management of production and mechanical engineering provide the necessary support.  However, it is a nightmare to get all required headcount on board, particularly after the Chinese New Year.  I never see a factory with sufficient labor force over the past 10 years.  Anyway, the willingness of the factory workers to work diligently becomes the topmost important issue.  There are only few successful Japanese factories and there are very few iPhone suppliers but there are million of mediocre manufacturers in mainland China building thin margin products/components.

I cannot recall who is the first celebrity scholar or entrepreneur raise the slogan something like focusing on people, people is human capital & etc.  And said is always much easier than done.  I am never successful neither in saying nor doing.  Never a worker thought his package is fair and enough.  What I need to do is to build up something new or eye catching every year.  Last December, I organized one event which I am very proud of it.  I hired a freelance photographer for 4 days walking around the factory area including the shop floor, warehouse, dorm, canteen and any open area, take whatever pictures he like with focus of the people and their eyes.  Let the operators be the focus.  But the pictures must be something touching and nothing more.

If you have a minute, view the following link.  The subtitle in the pictures is Chinese as it is for the local people.  Sorry about that!

The Day and Night of ECI