Inventory Management Excel1 - Free Download | Page 4
4.1, 3333 votes
Please vote for this template if it helps you.
Inventory Management Excel1 Page 4
Inventory Management Excel1
B. Instructions, MSH/INFORM, Inventory Management Assessment Tool
(D03699BEB07CECEE5ECDBF29CEE05073.xls), version 1
Option A
Option B
Consult the analysis and interpretation guidelines provided on the "Analysis" sheet for an explanation of the
possible causes of your results. Space is provided on this sheet for you to write the actions you will take to
improve record-keeping and stock management practices at your warehouse.
Step 4: Analyze the findings
Record the most recent balance indicated on the stock card in column E. Do not correct any mathematical
For each product on your list, count the physical quantities in the warehouse. Do not include expired
products in the count. Record the findings in column F.
To calculate the indicators using the spreadsheet:
The spreadsheet will display the indicators graphically on the "Graphs" sheet. Although it is not necessary,
those who do not have Excel may find it helpful to plot their own graphs for a visual representation of the
indicator results.
2c. Count the number of DAYS each product was out of stock within the assessment period
Refer to the sample stock card provided in Annex C as you read the following instructions.
Example: Determining the number of days out of stock
• Starting with the beginning of the assessment period (in our example the assessment period begins on
April 26), identify the first time there was a 0 balance. (According to the sample stock card, the first stockout
for chloroquine was on May 4.)
2d. Record the current stock card balance for each product
Step 3: Calculate the indicators
Tabulate and fill in columns G and H on the "Data Collection and Calculation" sheet.
• Total the number of days the product was out of stock. (7 days + 9 days = 16 days). In this example, you
would write 16 in column D.
• Continue until you have counted the number of days per stockout for each time the product had a 0
balance during the 100 day assessment period.
• Count the number of days between the day when the 0 balance began and the next receipt of stock. In this
example the stockout began on May 4 and continued until a shipment arrived on May 11, it was out of stock
for 7 days (11 - 4 = 7).
• Count the number of days until the next receipt (9 days).
• Identify the next stockout (June 8).
There are three sections included in the analysis: "Results," "Graphs," and "Analysis." Print out these sheets
(the spreadsheet is formatted to display the indicator results on each sheet) and use them together to
understand your results and identify appropriate solutions. If you do not have Excel, record your results in
the appropriate space on each page.
For each product, refer to the transactions on the stock card during the past 100 days. For each stockout
during the 100 day period, add up the number of DAYS the product had a 0 balance. Write the total number
of DAYS out of stock in column D.
Enter the data in columns B-F into the table on the "Data Collection and Calculation" sheet. (Note that you
must enter 0 for all zero values.) The data in the shaded areas (columns G and H, the total number of
products (N), the totals row (of columns D-H), and the counts at the bottom of the page (I-L)) will be
calculated automatically. The indicators will be calculated and displayed on the "Results" sheet. Continue to
Step 4 to analyze the findings.
2e. Conduct an inventory to record the current physical balance for each product
Using your recorded numbers, fill in the spaces at the bottom of the sheet by counting: the number of
products in the assessment (N), the number of zeros in column G (I), the number of negative numbers in
column G (J), the number of positive numbers in column G (K), and the total number of products present
(not zero) from column F (L).
Total columns D through H in the "Totals" row provided at the bottom of the table.
To calculate the indicators manually:
Proceed to the "Results" sheet to calculate the indicators using the formulas provided. Then continue to step
Inventory Management Excel1 Previous Page Inventory Management Excel1 Next Page
Inventory Management Excel1