Excel Modeling Operations Management

Inventory Simulation Using Excel Solver (linear programming approach)

This technique offers an Excel solver solution on the previous inventory simulation problem I posted.

This exercise can serve as a bridging topic between the inventory planning and the optimization technique. The inventory planning chapter from the textbook our university is using (Jacob, the core, 5th edition), does not introduce the idea of optimization before it jumps into the Newsvendor modeling. It introduces the idea of the optimization in the logistics management chapter where it uses a network optimization problem to introduce the distribution problem.

Excel Modeling Operations Management

Inventory Simulation (newsvendor)

In this modeling exercise, you are asked to determine an inventory level that would minimize the grand total cost for a two-hundred-month planning period.

  • For each month, the demand follows the same pattern described below.
    • Demand: normally distributed with a mean of 100, sd of 30
    • Overage cost: 10 unit/USD
    • Shortage cost: 5 unit/USD
  • Once determined, you will not change the inventory level during the two-hundred-month period.

This problem exists a clear analytical solution using a newsvendor model. Students will have to do a brute-force approach to determine the cost minimizing inventory level.

Operations Management Quality Management

Research Article Collection for Quality Management Class

Shah, R., Ball, G. P., & Netessine, S. (2017). Plant operations and product recalls in the automotive industry: An empirical investigation. Management Science63(8), 2439-2459. (

  • Using samples from auto manufacturing plants, this article demonstrates the positive relationship between the plant utilization and the likelihood of resulting a recall.

Corbett, C. J., Montes-Sancho, M. J., & Kirsch, D. A. (2005). The financial impact of ISO 9000 certification in the United States: An empirical analysis. Management science51(7), 1046-1059. (

  • Using event study fashion, this article seeks to identify both short-term and long-term implications of receiving an ISO-9000 certification.

Guajardo, J. A., Cohen, M. A., Kim, S. H., & Netessine, S. (2012). Impact of performance-based contracting on product reliability: An empirical analysis. Management Science58(5), 961-979. (

  • This article suggests that even the type of contract (time and material contracts and performance-based contracts) with the manufacturer can have impact over the product reliability.

Operations Management Quality Management

Variation Reduces Capacity Utilization

There is a well-known story about how to “properly” fill a jar with sand, rocks, and water. For the detailed story, please refer to the video below. The story is often used as an analogy to inspire people about the importance of priority.

But from the operations management perspective, the story also tells us an important principle about the capacity utilization. The fact that it still had space to fill sand and water after the rocks are filled first, is because the irregular shape (variation) of the rocks were inhibiting the space from being utilized efficiently!

Efforts to reduce variations can be seen everywhere! Workstations in the factory is moving in “cycle times”, packages follow certain design specifications so that the container (or truck) spaces can be maximally utilized.

Packages inside freight container follows uniformed sizes


Good Work Setup

A decent work setup helps improve productivity. It also reflects the personal tastes.

I move around my work setup at least once a year to help regain the productivity.

My computer table set up in 2014. It was a humble and small corner office. I always kept it clean. There was a right balance between privacy and transparency as an office for a junior teacher. I spent a very productive four years in the space.
If I have to punch a hole in the wall to hang a decoration, it must be something special and personal. “The Storm on the Sea of Galilee” by Rembrandt.

Operations Management Quality Management

Quality Control in Factory Level: OnePlus Factory Tour by Linus Tech Tips

In this video, a tech Youtuber, Linus, is showing us around the OnePlus factory. You do not have much factory experience or have never toured a manufacturing plant before, this is a good material.

It explicitly shows how the quality control is done in the factory level. Processes, Purposes, People, and Tasks of quality management are demonstrated clearly. (Although Linus never intended to make the video as a quality management case study. 🙂 )

Quality Management

Service Quality: Moving Company in Japan

Evaluating the quality of manufactured products can be straightforward because they are tangible. But the evaluation of services, which are intangible, can be more involving. According to the Evans and Lindsay, existing research suggest the following five principal dimensions that influence customer’s perception on service quality.

from OSCM3340 Lecture Note

As you can see, the level of effort the Japanese moving company is showing is very impressive. Can you comment on what/how the company has done in each of five dimensions listed above?

Quality Management Teaching

Good Management and Performance Excellence is Hard to Copy

To read the full article, please visit:

Copyright @HBR.
(Disclaimer: Video file is upload in this website to provide access to students in locations where Youtube, Vimeo, and other major platforms cannot be accessed.)


Sadun, R., Bloom, N., & Van Reenen, J. (2017). Why do we undervalue competent management. Harvard Business Review95(5), 120-127.

Porter, M. E. (1996). What is strategy?. Harvard business review74(6), 61-78.

Excel Modeling Teaching

Excel Formulas Useful for Production, Operations, and Manufacturing Management

This is a continuously updated list. For the most recent update, please visit my website here.

FormulaTagsBrief DescriptionExample Video or Manual
AVERAGE(range)StatisticsCount the average of the range
COUNT(range)StatisticsCount number of numeric values within the range
EXPON.DIST(x, lambda, TRUE)StatisticsProbability that a unit will fail within x hour given the hourly failure rate is lambda
NORM.DIST(x, average, sd, true)StatisticsProbability that is smaller than the provided x given the average and the s.d.
NORM.INV(p, average, sd)StatisticsSeeks that value x such that NORM.DIST(x, mean, standard_dev, TRUE) = probability
RAND()StatisticsGenerate random number between 0 and 1. Follows uniform distribution.
RANDBETWEEN(min, max)StatisticsGenerate random number between the value of the min and the max. Follows uniform distribution.
STDEV.S(range)StatisticsCount the standard deviation of the range. It considers the range as a sample
AND(cond1, cond2)LogicalTesting two more conditions, return TRUE when all conditions are true.
IF(condition, value_if_true, value_if_false)LogicalConditional statement.
OR(cond1, cond2)LogicalTesting two more conditions, return TRUE as long as one condition is true.
SUMPRODUCT(range1, range2)MathHandy in calculating weighted average
COMBIN(number, sample_size)MathNumber of different ways to choose sample_size from number. For example, 3 choose 2 = 3, there are three different ways of choosing 2 combination out of 3.

Retrieving and Visualizing COVID-19 Statistics from Local Health Authority Website

Motivation of this Work:

The Ohio Department of Health provide daily update on Covid-19 related data such as total confirmed, total hospitalized, and total death, but the access to the daily new case count (newly confirmed, hospitalized, and dead) is a bit tricky. So, I created my own version of daily case count tracking.

Also, in some cases, the department updates daily case count data a few days later. This has made me hesitate on how much trust I should place for the “latest” update. Therefore, to track the discrepancies, I decided to add a function to save the data each time I run the code. This way, I can make comparisons later to see if there are any patterns in the “late update”.

Primary Outcome: Visualized Daily New Case Count

Comparison of Daily Confirmed Cases among Four Major Cities in Ohio, USA

A detailed breakdown of what my code can do:

  • Retrieving daily update data from the Ohio Department of Health Website
  • Producing a daily confirmed case data from the raw data
  • Charting the daily confirmed case data graphically
  • Visualizing the trend (lowess smoothing)
  • Saving the daily confirmed case data into a .csv file for later comparison.
  • Run the code any time to get the results!

Code Block:

clear all

//retrieving the data
import delimited ""

//capturing the date and the time of the data
local c_date = c(current_date)
local c_time = c(current_time)

drop if _n == 1

//manipulating variable names and columes to obtain the daily count
rename v1 county
label variable county "county"

rename v2 sex
label variable sex "sex"
encode(sex), gen(gender)

rename v3 ageRange
label variable ageRange "Age range"

gen onSetDate = date(v4, "MDY")
format onSetDate %tdnn/dd
label variable onSetDate "Onset Date"

gen dateDeath = date(v5, "MDY")
format dateDeath %tdnn/dd
label variable dateDeath "Date of Death"

gen admissionDate = date(v6, "MDY")
format admissionDate %tdnn/dd
label variable admissionDate "Admission Date"

rename v7 caseCount
destring(caseCount), replace force
label variable caseCount "Case Count"

rename v8 deathCount
destring(deathCount), replace force
label variable deathCount "Death Count"

rename v9 hospitalizeCount
destring(hospitalizeCount), replace force
label variable hospitalizeCount "Hospitalize Count"

//Generating daily confirmed case count
collapse (sum)caseCount (sum)deathCount (sum)hospitalizeCount, by(onSetDate county)

//Charting four major counties 

twoway (scatter caseCount onSetDate if county == "Cuyahoga", msize(0.5)) (lowess caseCount onSetDate if county == "Cuyahoga"), name(Cuyahoga) legend(off) xtitle("") ytitle("") yline(5, lcolor(green)) title("Cuyahoga County (Cleveland)") note("Population: 1.24 million")

twoway (scatter caseCount onSetDate if county == "Franklin" & caseCount <= 100, msize(0.5)) (lowess caseCount onSetDate if county == "Franklin"), name(Franklin, replace) legend(off) xtitle("") ytitle("") yline(5, lcolor(green)) title("Franklin County (Columbus)") note("Population: 0.89 million; Some dates in late April to early May where the case count " "was higher than 100 is obmitted to keep the graph scale consistent.")

twoway (scatter caseCount onSetDate if county == "Hamilton", msize(0.5)) (lowess caseCount onSetDate if county == "Hamilton"), name(Hamilton, replace) legend(off) xtitle("") ytitle("") yline(5, lcolor(green)) title("Hamilton County (Cincinnati)") note("Population: 0.82 million")

twoway (scatter caseCount onSetDate if county == "Lucas", msize(0.5)) (lowess caseCount onSetDate if county == "Lucas"), name(Lucas, replace) legend(off) xtitle("") ytitle("") yline(5, lcolor(green)) title("Lucas County (Toledo)") note("Population: 0.43 million")

graph combine Cuyahoga Franklin Hamilton Lucas, col(1) xcommon ycommon ysize(2) xsize(1) note("Data Source: Ohio Department of Health; Green Line = 5 Cases") title("COVID-19 Daily New Case Count in OH") subtitle("Updated on $S_DATE" )

//Saving the data using the date and time of the data
local c_date = c(current_date)
local c_time = c(current_time)

local c_time_date = "`c_date'"+"_" +"`c_time'"

local time_string = subinstr("`c_time_date'", ":", "_", .)
local time_string = subinstr("`time_string'", " ", "_", .)
display "`time_string'"

local folderPath = "D:/OHIO_COVID_DAILY_CONFIRMED_"
local fileName = "`folderPath'" + "`time_string'"
display "`fileName'"

export delimited using `fileName'.csv, replace