Category: Excel Modeling

Categories
Excel Modeling Operations Management

From Factory to Market: Distribution Problem (Linear Programming Approach)

This exercise introduces an important topic in the operations and supply chain management – distribution problem. Essentially, it deals with a problem where you need to match the supply and demand with the least transportation cost.

(Sorry for the humming sound and poor microphone quality, I had to record it in a hotel restroom after my family went to bed. I was attending DSI 2019 conference in New Orleans, LA)

To understand the impact of this kind of approach in the real world, please refer to the following talk by Jack Levis, the guy who led the optimization effort in UPS.

More detailed information about the UPS ORION is published by the team here:

Holland, C., Levis, J., Nuggehalli, R., Santilli, B., & Winters, J. (2017). UPS optimizes delivery routes. Interfaces, 47(1), 8-23. https://doi.org/10.1287/inte.2016.0875

Categories
Excel Modeling Operations Management

Service Level, Fill Rate, and Newsvendor Model

Service level is the frequency of the stockout, while the fill rate means the proportion of demands satisfied.

Newsvendor model determines the desired service level, not the fill rate.

Categories
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.

Categories
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.

Categories
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 Notions.so 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.https://support.office.com/en-us/article/norm-dist-function-edb1cc14-a21c-4e53-839d-8082074c9f8d
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.https://support.office.com/en-us/article/video-advanced-if-functions-17341cef-510d-4d60-a657-913f878fdacf
OR(cond1, cond2)LogicalTesting two more conditions, return TRUE as long as one condition is true.
SUM(range)Math
SUMPRODUCT(range1, range2)MathHandy in calculating weighted averagehttps://support.office.com/en-us/article/video-weighted-average-c1ad3cd4-cf64-4461-b3b7-0767498b2135
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.
Categories
Business Statistics Excel Modeling Quality Management

From Population Distribution to Sampling Distribution (Simulation)

Sampling distribution is an important concept in statistics. We rely on sampling distributions (e.g., sample mean and sample proportion) to make decisions about whether to accept or to reject the hypotheses about the population properties.

Students usually have tough time understanding the concept due to its highly theoretical nature. The attached Excel spreadsheet can help visualize the process of obtaining a sampling distribution of population mean. Specifically, it allows you to specify a sample size n and the number of sample groups of k. Thus, you will have k number of sample averages can be used to construct a distribution. This Excel applet allows the process of obtaining the sampling distribution more visible.

Note:

  • Original file has a population of 10,000 observations that follow a normal distribution with mu = 500 and sigma = 50. If you wish to demonstrate the law of large numbers, you can replace the population data with your own.
  • When you specify a very big k, for example 400, Excel will freeze for a moment to process the request. Please monitor your CPU usage.
  • My website does not allow me to upload Macro enabled Excel file as/is. That is reason why you are seeing a zip file.
  • When use in Windows 10, please enable Macro, Data Analysis ToolPak, and Data Analysis ToolPak – VBA. Otherwise, it will report a run time error.
  • For Mac, please see this link. Essentially you need to enable the developer ribbon.

(Note: Written in Excel VBA)

I have another similar worked example in R here.

Repeated SamplingDownload

Categories
Excel Modeling Operations Management

Price Break Model

This one problem type that confuses the most.

Price Break models are used where the price of inventory varies with the order size. In these models the economic order quantity is calculated for each price and compared to the amount of inventory that will be available at that price.