Loading...
Date of Submission
Spring 5-1-2024
Project Type
Senior Design
Major
Industrial and Systems Engineering
Degree Name
Bachelors in Industrial and Systems Engineering
Department
Industrial and Systems Engineering
Committee Chair/First Advisor
Dr. Adeel Khalid
Abstract
Parcel Buddies is an industrial engineering contractor that has been assigned by Company X, a packaging and delivery company, to optimize the Package Delivery and Pick up operations of the On-Road and Inside Excel model structures. The Company’s current excel process consists of manually inputting daily operational data, which is hard to read for employees because of the vast amounts of data and does not transfer all data to the ERP system in OneStream to display necessary important data. Parcel Buddies was asked to analyze the data, optimize the manual process, and find a way to visually represent the data in Excel and OneStream to the expectations and requirements of Company X. The requirements are to make the Excel model user friendly and identify the requirements of OneStream to ensure all data is transferred and used when analyzing the input data in Company X Excel Worksheets. During the research, the Parcel Buddies found that they can use Microsoft Access and Power Query to facilitate the manual input process in Excel. The Parcel Buddies also found that Company X can use Power BI to visually represent the forecasts and production targets. In the Company X Excel document, Corporate IE and Transportation employees translate Billed Volume forecasts to Operational Volume Forecasts and provide production targets. It takes Company X employees 51.5 hours per year to enter this data manually. As a result, Parcel Buddies decided to focus on automating the On-Road model. The two possible solutions mentioned previously were not carried out. The team concluded VBA Macros were easier to use and has a better learning curve compared to Power BI and Microsoft Access. This solution makes sure all calculations are being conducted to ensure production managers are receiving accurate data to plan out their daily production and that Company X industrial engineers are generating an efficient business plan. The Parcel Buddies then conducted VBA Macro tests in the On-Road model to identify the problem and requirements needed to create the macros and excel formulas necessary to satisfy the Company X project plan goal of eliminating most manual inputs from the current process.
Presentation Slides
FDR Report.pdf (5611 kB)
Final Design Report