Tuesday, December 29, 2020

Inventory Optimization - Calculate Safety Stock

Safety stock provides a "cushion" in inventory to address the uncertainty in a customer's demand. It is important to maintain the "right" amount of safety stock. If it is too low, we may not be able to fulfill a customers' orders in a timely fashion. On the other hand, safety stock that is too high incurs significant financial and/or logistics burden to the business.

The calculation of safety stock is based on a number of factors include historical customer demands, product lead time and fill rate (a.k.a., demand satisfaction). The team at safetystockcalc.com builds a website and describe a popular approach to calculate safety stock using an example. On the website, you may also find two free tools that are useful, an online calculator and a downloadable spreadsheet with all the formulas. Enjoy!

Online Safety Stock Calculator Screenshot

Spreadsheet Safety Stock Calculator Screenshot

Monday, November 30, 2020

An Online Tool for Analyzing Chinese Text

We have developed a free online tool for analyzing Chinese document. The URL of the tool is located at here.
A user fills in the textbox with the content and click Submit button. The tool identifies words, calculates frequencies for those words and display a word cloud picture.

. Enjoy!

Monday, April 20, 2020

Real COVID-19 Death Rate

In my last post, we did research on COVID-19 death rate based on the ratio between the number of deaths and the number of confirmed cases. However, this method is inherently flawed. Some infected people did not show up at a hospital or a testing station to get tested. As a result, the death rate is exaggerated.

Blood antibody tests on randomly sampled residents in Santa Clara, California in early April shows that the number of people infected is 55 to 85 times more than confirmed cases (https://www.cnn.com/2020/04/17/health/santa-clara-coronavirus-infections-study/index.html). Thus, the real death rate for people who infected with coronavirus is between 0.1% and 0.17% which are similar to that of flu.

We use the following charts to illustrate two ways of calculating death rates.

COVID-19 Death Rate (Flawed) = Number of Deaths/ Number of Confirmed Cases

COVID-19 Death Rate (Real) = Number of Deaths/Number of Infected
As we can see, the chance of COVID-19 bullet hitting the bullseye, i.e., causing death, is much slimmer that appears based on confirmed cases alone.

Thursday, April 02, 2020

Study on COVID-19 Annualized Death Rate

Probably this is the first time you see a chart like this. When people hear COVID-19 death rate for older people is high, they panic. We did research and published a paper on COVID-19 death rate( Study on COVID-19 Annualized Death Rate). Please notice the death rates for COVID-19 are "annualized". But we have to look at things in context. When COVID-19 death rate is annualized, it can be compared with other statistical data that are on annual basis. This is the key contribution of our research.

Please click the chart for sharper view.

Only when COVID-19 death rate is annualized, it can be compared with other statistical data that are on annual basis. This is the key contribution of our research. The following are the conclusions.

Conclusions

We propose a method to calculate the annualized death rate (ADR) related to COVID-19. Based on ADR related to COVID-19 and the 2018 death rate for the population of the United States, we gain the following insights:
  • Incremental annual death rates related to COVID-19 for age groups 45-54, 55-64, 65-74, 75-84 and 85+ are 0.4%, 1.2%, 2.2%, 3.1% and 6.0%, respectively.
  • Percentages of incremental annual death rates related to COVID-19 for age groups 45-54, 55-64, 65-74, 75-84 and 85+ are 100.9%, 131.8%, 124.4%, 69.8% and 44.5%, respectively.
  • If the herd immunity strategy is used in the United States, the incremental annual number of deaths related to COVID-19 for people equal to or older than 45 will be between 1.1 and 1.47 million.
  • The PDF file for the paper can be downloaded Study on COVID-19 Annualized Death Rate

    Monday, March 02, 2020

    Querying Database Using Command Line Client and Powershell

    I have found Powershell is a powerful tool. When it combines with a command line tool, such as mysql, we can perform sophisticated tasks easily. The output of Powershell is an object instead of a text string. We can perform SQL-like operations such as selecting columns and filtering rows using where clause. Since I found out the power of Powershell last year, I have been using Powershell as my major tool to query databases including SQL Server, MySQl and Postgre. In this post, I demonstrate how to query MySQL using mysql client and Powershell.

    First, we run the following command to set up our host, user name, password and port.
    mysql_config_editor set --login-path=local --host=localhost 
                             --user=root --port=3306 --password
    
    When we run the above command at a shell, it will prompt us to input password. It will create a file .mylogin.cnf that is not humanly readable under home directory (I am using Mac. For Windows, the file is under %APPDATA%\MySQL). The reason of doing this is to avoid an annoying security warning message if we supply the password in mysql command. If you don't mind the warning message, you can skip the step. Then I write a Powershell function to query MySQL database. I save the scripts as mysql_f.ps1 on my Mac.
    function runsql {
    param($s)
    mysql --login-path=local -D dataexp -B -e "$s" | convertfrom-csv -delimiter `t
    }
    
    I issue the following command from the shell
    prompt>. "./mysql_f.ps1"
    
    Now the function is defined. I test the function.
    prompt>runsql "select 1 as val"
    
    val
    ---
    1
    
    It worked. The following query displays the table in the current database.
    prompt>runsql "select TABLE_SCHEMA, table_name, create_time 
           from information_schema.tables where table_schema=database()"
    
    TABLE_SCHEMA TABLE_NAME             CREATE_TIME
    ------------ ----------             -----------
    dataexp      flyway_schema_history  2019-11-21 11:56:31
    dataexp      mdl_sampled            2020-01-14 10:40:06
    dataexp      mdl_set                2020-01-14 09:46:59
    dataexp      sample_user_id         2020-01-14 09:58:34
    dataexp      t_all_pk1              2019-12-05 11:53:43
    dataexp      t_all_pk1_int          2019-12-05 22:14:54
    dataexp      t_all_tab_cols         2019-11-21 15:39:00
    
    From the Powershell output, we select only table_name an create_time by piping the output to "select" command of Powershell.
    prompt>runsql "select TABLE_SCHEMA, table_name, create_time 
           from information_schema.tables 
      where table_schema=database()" | select table_name, create_time
    TABLE_NAME             CREATE_TIME
    ----------             -----------
    flyway_schema_history  2019-11-21 11:56:31
    mdl_sampled            2020-01-14 10:40:06
    mdl_set                2020-01-14 09:46:59
    sample_user_id         2020-01-14 09:58:34
    t_all_pk1              2019-12-05 11:53:43
    t_all_pk1_int          2019-12-05 22:14:54
    t_all_tab_cols         2019-11-21 15:39:00
    
    The above operation is possible because in runsql function, we use convertfrom-csv to convert text output of mysql to Powershell object. Convertfrom-csv is really a magic function! Once we have an Powershell object, we can do all sorts of things.