A close friend of mine once went for an interview. The job role required an intermediate knowledge level of Excel as a requirement. During the interview, a question was asked, “What is your knowledge level of Excel?” Without hesitation, she affirmed her status as an eight out of ten. The next question that followed concluded the interview. “Can you create a financial model for an institution?” In shock, he responded, “Can Excel really do that?” With that, the interview ended.
Excel is an advanced tool. Many underutilize its capabilities. We use it to create tables and generate basic reports. However, its potentials are far-reaching. In the last class on data analysis, we consider the normal distribution and probabilities and some real-life application scenarios.
Now, we see that under the normal distribution, about 68% of any data would lie between one standard deviation from the mean, 95% of any data lies between two standard deviations from the average, and about 99% of data will fall within three standard deviations from the mean.
Consider a scenario, an oil servicing company with the following data. All data is in tens of thousands. The company issues a purchase order for replenishment at twenty gallons. After a period of six months, the management noticed a steady decline in revenue. The sales team was assigned to investigate the cause of this decline. The sales manager noticed that the loss in revenue was due to customers coming in when they were out of stock. Based on the available data, the mean is about fifteen gallons and a standard deviation of six gallons. With this information, we can compute the probability that they will run out of stock before re-stock. The chance of running out of stock at a re-purchase point of 20 gallons is about 20%.
After submitting this report to the board, the managing director requested that this probability should be reduced to about 5%. We saw that the sales manager can also perform an inverse normal distribution function with a new probability of 95%, a standard deviation of six, and a mean of fifteen to get about twenty-five gallons. With this, we have seen how Excel functions can support us in inventory management.
With this new knowledge, I intend to apply this knowledge in the financial markets. Previously, I entered and exited my positions within the fifty to sixty-two percent range. Now seeing that sixty-eight percent of users are within one standard deviation and ninety-five percent of users will appear within the two standard deviations, I would adjust and use the range of sixty-eight percent to ninety-five percent. I know that the values in the financial markets are not “normally distributed”. This makes this strategy a high-risk setup. Well, the risk is on demo money and what is life without a little bit of an adventure?
Excel is rich and with every passing class, I get to see more of what Microsoft had to offer us when they built this powerful program. Remember, excel can only help to the degree of accuracy of your data, so keep correct data about your business. Ensure your decisions are based on detailed analysis of accurate data.
Life, Love, and Life
#DA #MMBA4
