SIMTech, A*STAR‎ > ‎

Optimising Inventory

Project Title: Optimizing Inventory
Organisation: SIMTech, A*star
Mentor/s: Dr Koh Niak Wu
Group members: Lee Lin Ying. Zhao Yu Qing

Brief description of the project

The objective of this project is to allow us to understand the effects of inventory levels on cash flow, and to give us a brief introduction to the supply chain, inventory management, cash flow, and time value of money.
A simple cash flow forecast is to be created at the end of project to quantify the effects of inventory levels on cash flow.

Blog (please include "3 content/knowledge skills you learnt; 2 interesting aspects of your learning; 1 take-away for life)


posted Feb 9, 2011, 9:36 PM by 1129 Zhao Yuqing   [ updated Feb 21, 2011, 6:08 AM ]

3 content/knowledge skills you learnt;

 I learnt that supply chain was a system of organizations, people, technology, activities, information and resources involved in moving a product or service from supplier to customer. Supply chain activities transform natural resources,raw materials and components into a finished product that was delivered to the end customer.

I learnt that cash flow was the movement of cash into or out of a business, project, or financial product. Moreover, in a corporate finance sense, cash-flow forecast was the modeling of a company or asset’s future financial liquidity over a specific timeframe .To an extent, it can best estimate the prospect of a company.

I learnt that there were several formulas which linked safety stock , lead time, service level and reorder point together. And from that we can find the best number which not have excessive inventory or not enough.


 2 interesting aspects of your learning;

-          People always make a common mistake about inventory which is that they think they can sell the goods sooner or later anyway. In a sense, this is true.However, there are some serious costs to holding inventory. Anytime inventory is held, there are holding costs. Holding costs are simply the costs that are incurred just by holding onto inventory. These costs can come from a variety of sources. Here are just a few common costs associated with high levels of inventory: higher rent from increased need for extra warehouse space to hold extra inventory, higher premiums needed to insure extra inventory. Potentially, the inventory could spoil, expire, or become outdated and lose value. There are also oppurtunity costs of the holding inventory. Just imagine, what else could you have invested the money and warehouse space in had you not been spending it on inventory?

-          Excel solver is a powerful tool that helps us find the best way to allocate scarce resources. The resources may be raw materials, machine time or people time, money, or anything else in limited supply. The "best" or optimal solution may mean maximizing profits, minimizing costs, or achieving the best possible quality.  An almost infinite variety of problems can be tackled this way. It is very convenient and we do not need to use a calculator anymore.


 1 take-away for life

Doing an internship is very different from school-based learning. The first difference that I felt is that any choice you make or any work you do has a

direct and irreversible impact on your client. There is no turning back like in school where you can redo your work when you make a mistake. This helps me to cultivate a serious attitude whenever I do my work. Another lesson I learnt is that you don’t get a definite answer to a problem such as the one you get when you solve a maths equation. This encourages me to think more widely and come up with the most suitable rather than the most correct answer to any real life problems.


LinYing's Wiki

posted Feb 3, 2011, 1:31 AM by 1101 Lee Lin Ying   [ updated Feb 22, 2011, 7:24 AM ]

Simtech A*Star Optimising Inventory
A photo with our mentor, Dr Koh Niak Wu
A photo with one of the staff in SIMTech, addressed as Mr Ram


This course has been an interesting one. I have learnt much from the entire attachment, such as understanding what the supply chain, cash flow, inventory management and time value of money are all about. In addition to research, we had to do a cash flow forecast for a company. At the end of the entire project, our mentor gave us one last task to complete. A cash flow problem was given to us, and we had to use a software known as the excel solver to determine the exact values for the variables (given), so that the revenue is maximised.

3 Content/knowledge skills learnt

1)     Learning about the supply chain has made me understand the difficulties and considerations that need to be taken to produce a product. It has allowed me to see that there many activities and administration details that have to be carried out to ensure that a product is successfully delivered to the customer. Consumers are always complaining about not being able to get the specific design that they are looking for, and about the slow transport of goods. I was also one of these consumers. However, after learning more about the supply chain, I now understand how hard it is to satisfy the individual needs of each customer, and to ensure that products arrive on time.


2)     I have learnt how to construct a simple inventory cash flow forecast, which is used to predict the amount of inventory needed by a company. This was done by estimating the demand of a product to prevent shortage or surplus of goods, which would lower the profit of the company.
I now understand the difficulties and obstacles faced, and the numerous considerations that a firm needs to make in order to ensure its survival and profitability, through the creation of a cash flow forecast.

3)     By doing the cash flow forecast, I am now able to read and understand the financial statements of companies, which I did not understand before, and would not have bothered to understand if not for this attachment.


Through this entire project, I now understand the basic concepts that need to be kept in mind to ensure that a company has an adequate amount of inventory in its hand to satisfy a consumer’s needs. I am also able to understand how complex and tricky it is to a business.

2 interesting aspects of your learning

Doing the cash flow forecast

The difficulty in making a cash flow forecast lies in the estimated values of demand.

Estimating the value of demand is complicated, as it is hard to determine how the tastes of consumers will change in the future, in this fast-paced society. Estimating these values was really a challenge to me, as I wanted to make my values seem as realistic and possible.

Doing a forecast is really not an easy task. Even in this hypothetical situation, it took a long time for me to estimate the demands of the consumers. It must be really stressful and tough to construct a cash flow forecast that is to be used by a real company for its business.

There were many terms that seemed alien to me, such as accounts payable, accounts receivable and depreciation. Accounts payable and accounts receivable refers to the amount of money owed to a company and to be received from others respectively. Accounts payable are taken as positive cash flow (This money is seen to be “given” to the company, since the company has not officially paid for the goods), and accounts receivable are taken as negative cash flow (This money is seen to be “given” away to the customer, as the company has yet to collect payment.)

Depreciation is an expense that is recorded as a tangible asset over its useful lifespan. If a machine costs $1000 and is expected to be useful for 5 years, amount of depreciation per year is $200.

In the income statement (reports the earnings/costs of a company), depreciation is subtracted.

While the value of depreciation is added back into the cash flow statement (a statement that lists all cash flows within a period). This is due to the fact that the cash flow statement only lists down the movement of cash at that point where the cash is received or expended. The cash flow statement does not account for time. This means that the total cost of the machinery has already been deducted at the moment of purchase.  Deducting depreciations will only result in a double deduction.

Learning about the time value of money

Learning the concept and equation for the time value of money will really be helpful in the future. Through my research, I learnt how to calculate the time value of money. It is interesting to see how the value of money would decrease as years pass by. I understood that putting your money in an investment allows your money to grow, but it has never cross my mind that a specific sum of money would be worth lesser in the future than it is worth now due to the investment. It has always occurred to me that the value of money decreases over the years due to the increase in incomes and wages.

1 takeaway for life

I experienced how it is like to work in a company. When you are given a task to do, you have to complete it by yourself. Determination and discipline is essential for one to complete a project before or by the date due, as one will not be reminded by others to complete the project. It is all up to one to allocate and divide one’s time to ensure that all projects assigned can be completed by the date due. One must also constantly think of new ideas and questions to broaden one’s knowledge, especially when doing research, so that one will always have things to do and will be able to learn as much as one can. This will make the task easier. Doing more research now would also mean that less research would be needed in a subsequent similar project.

In addition, it is important to be vocal. No one would have the time or energy to monitor your progress continuously to check if you have difficulties in any areas. Information and explanations will have to be sourced on your own through research, as you will not be spoon-fed.  

This experience has highlighted the importance of being independent, to be able to decide, and of having thirst for knowledge. Through my mentor, I understood how important it is to love your work. One will be able to work and learn to his maximum only when one truly loves his work.


Brief definitions and explanation on some terms

Supply Chain

The supply chain is a system which indicates the movement of a product or a kind of service from the supplier to the customers. Aside from products and services, information concerning the fluctuations in the market and production capabilities between the corporations and companies, and cash (From customer to supplier) also flows through the supply chain.

This includes all the logistics and administration that occurs during the purchase/sale of a product.

Reseller à A person who purchases with the intention of reselling instead of consuming the product.

Retailer à A retailer sells goods and merchandises from a fixed location. Retail establishments are also known as shops and stores.

Thin dark arrows = Flow of information and finances.

Thick light arrows = Flow of products and materials.

Flow of information must be quick to allow accurate production of goods and services.


1)     The retailer would have to inform the supplier to supply him with more goods if there is a shortage of stock. The supplier would then contact the manufacturer, who in turn would contact their suppliers.


2)     The retailer informs their supplier who in turn informs the manufacturer to delay the manufacturing and purchase of materials for a particular product if the demand of that product falls.



Cash Flow

Cash flow refers to the movement of cash into and out of a business, project or a company. Cash flow is used to determine a company’s welfare, such as the liquidity (extent to which a person or firm is able to repay its debts when it is due) of a company.

Positive cash flow indicates that the company is receiving money.

Negative cash flow indicates an outflow of cash from the company.

It is important to note that cash flow is not profit.

A negative cash flow does not indicate that the company is not profitable in the long run.

Negative cash flow can occur when a company is expanding. Huge funds needed to purchase long-term and short-term assets such as machineries can cause a temporary negative cash flow. The company is basically profitable, but faces a substantial outflow of cash due to procurement of assets.

Having positive cash flow does not indicate that a company is profitable.

The cash flow of a company can be positive if it receives a lot of cash from investors. On the other hand, the company could be trading at a loss, making them unprofitable in the long run.

 Net cash flow of a company over a period = Change in cash balance over that same period.

There are 3 types of cash flow.

1)     Operational cash flow

a.     Movement of cash due to a company’s internal activities, such as cash earnings or working capital (Current assets – current liabilities)

b.     This figure must remain net positive in the long run for the company to remain solvent (ability to repay all liabilities. Value of a company’s asset is greater than the value of liabilities.)

2)     Investment cash flow

a.     Cash received from the sale of long-life assets or cash used on capital expenditure, such as investments, long-life assets, or acquisitions.

3)     Financing cash flow

a.     Inflow of cash from debt and equity issued, or outflow of cash for dividends, repurchasing of shares or repayments of debts.


Inventory management

Supply chain management basically involves the considerations of these questions:

  1. What point and what quantity should an order be placed to the supplier?
  2. When should an order be placed to the supplier to ensure that stocks come on time?
  3. What happens if the supplies don’t come on time? How long can the current inventory allow the production to continue without disruption? How much inventory should be hold to ensure that stock will not run out before the supplies arrive?
  4. What if 2 materials are ordered from different suppliers, and 1 is delayed? )

Time Value of Money

The time value of money encompasses the idea that the money available now will be worth more than that amount in the future due to its potential earning capability. This is also known as present discounted value.

Formula is given as: Present value = (Future value)/[(1+i)^n]
Formula is obtained by reversing the equation to determine compound interest.

For compound interest: Assuming that interest rate is 3%, and amount invested is $10,000.

End of 1st year: $10,000 * (1.3)

End of 2nd year: $10,000 * (1.3)2.

End of nth year: $10,000 * (1.3)n.

Formula: Future value = Present value * (1+i)n.

Hence, by reworking the formula for compound interest, the formula for present discounted value can be obtained.


Youtube video for Product Mix Example 

Using Excel Solver in Excel 2007

1-2 of 2

1129 Zhao Yuqing,
Feb 9, 2011, 9:32 PM
1129 Zhao Yuqing,
Feb 9, 2011, 9:32 PM