Matching Items (2)
Filtering by
- All Subjects: Excel
- All Subjects: Inventory Management
- Creators: Taylor, Todd
Description
The purpose of this project is to provide our client with a tool to mitigate Company X's franchise-wide inventory control problem. The problem stems from the franchises' initial strategy to buy all inventory as customers brought them in without a quantitative way for buyers to evaluate the store's inventory needs. The Excel solution created by our team serves to provide that evaluation for buyers using deseasonalized linear regression to forecast inventory needs for clothing of different sizes and seasons by month. When looking at the provided sales data from 2014-2016, there was a clear seasonal trend, so the appropriate forecasting model was determined by testing 3 models: Triple Exponential Smoothing model, Deseasonalized Simple Linear Regression, and Multiple Linear Regression.The model calculates monthly optimal inventory levels (current period plus future 2 periods of inventory). All of the models were evaluated using the lowest mean absolute error (meaning best fit with the data), and the model with best fit was Deseasonalized Simple Linear Regression, which was then used to build the Excel tool. Buyers can use the Excel tool built with this forecasting model to evaluate whether or not to buy a given item of any size or season. To do this, the model uses the previous year's sales data to forecast optimal inventory level and compares it to the stores' current inventory level. If the current level is less than the optimal level, the cell housing current value will turn green (buy). If the currently level is greater than or equal to optimal level or less than optimal inventory level*1.05, current value will turn yellow (buy only if good quality). If the current level is greater than optimal level*1.05 current level will be red (don't buy). We recommend both stores implement a way of keeping track of how many clothing items held in each bin to keep more accurate inventory count. In addition, the model's utility will be of limited use until both stores' inventories are at a level where they can afford to buy. Therefore, it is in the client's best interest to liquidate stale inventor into store credit or cash In the future, the team would also like to develop a pricing model to better meet the needs of the client's two locations.
ContributorsUribes-Yanez, Diego (Co-author) / Liu, Jessica (Co-author) / Taylor, Todd (Thesis director) / Gentile, Erica (Committee member) / Department of Economics (Contributor) / Department of Information Systems (Contributor) / Department of Marketing (Contributor) / School of International Letters and Cultures (Contributor) / School of Life Sciences (Contributor) / Department of Supply Chain Management (Contributor) / Barrett, The Honors College (Contributor)
Created2017-05
Description
The project mainly involves creating a standardized tool to help American Airlines evaluate the cost of Non-Recurring Engineering fees (NRE fees) that arise as a result of maintenance and repairs on airplanes. Since there are few manufacturers licensed by the FAA to complete these modifications, let alone have the capabilities to complete them, American Airlines is often charged substantial fees to complete even minor work. The team will begin by conducting academic research looking into how parallel industries such as Automotive, Aerospace, High-Tech Manufacturing, etc. deal with heavily regulated modifications. We will then use this academic research to building a framework that American Airlines is able to use to estimate the fair cost of completing some of these modifications. The hope is that American Airlines can use this framework to determine whether they are being charged fair prices, and if they are not, to use the framework as leveraging tool in negotiations.
ContributorsShah, Shimoli (Co-author) / Harris, Taylor (Co-author) / Hebel, Ryan (Co-author) / Taylor, Todd (Thesis director) / Faris, Kay (Committee member) / Department of Information Systems (Contributor, Contributor) / Department of Economics (Contributor) / Department of Finance (Contributor) / Department of Supply Chain Management (Contributor) / Computer Science and Engineering Program (Contributor) / Barrett, The Honors College (Contributor)
Created2016-12