Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

IE 212: Computational Methods for Industrial Engineering

Winter 2023

Term Project

The term project is worth a maximum of 180 points. The first evaluation of your solutions will assess how well it works. Based on this criterion, the first 40 points will be awarded based on the following scale:

· 40 points – Excellent (everything works; solution produces valid and consistent results).

· 30 points – Good (graphical user interface is good; solution produces inconsistent results).

· 20 points – Bad (graphical user interface is poor; solution produces invalid results).

MAIN DELIVERABLES

The remaining requirements are included in this document with the remaining 140 points coming from:

· 14 points - General requirements 

· 63 points - VBA solution 

· 63 points - Python solution 

PROJECT OBJECTIVE

In partial fulfillment of the requirements of this course, you will develop an Excel VBA program AND a Python program to represent a decision support system (DSS).  The program is intended for employees working at a manufacturing plant to calculate the total work in progress (WIP) forecast based on production plans.  The WIP forecasts are results from 25 simulation model runs for the production plan and represent a future state of the factory WIP. The DSS you develop will allow the user to specify which products the user is interested in seeing the forecast for.  The DSS will show results of the WIP forecast for the products specified.

Employees at this manufacturing plan frequently need to plan future work in their area and having an estimate for how much WIP of specific products helps in setting work plans.  To aid decision making, the forecasts are to be given as a range based on a user specified confidence level (i.e. 95% confident that the WIP will be between a low value and a high value).

A user will provide product ID’s, and your DSS will return the confidence interval based on the specified confidence level and product ID.  The DSS will also calculate a total confidence interval for each production plan once the user indicates they are done inputting products.

GENERAL REQUIREMENTS – Both VBA and Python! (14 points):

· (4 pts) You must use at least 1 function that returns a value – and use that value in subsequent code.

· (2 pts) You may only use module level/public/global variables that need to be kept track of “from button click to button click”. i.e. don’t use a global variable when you can pass that variable to the function that needs to use it.

· (4 pts) No sub/function procedure should be longer than 30 lines of code (not including white space or comments)

· (2 pts) Properly and consistently indenting your code so that it is easier to read. (Required in Python, do it VBA also)

· (2 pts) Adding extensive comments to your code.

SOLUTION REQUIREMENTS – VBA (63 points total)

Your Excel VBA solution must include two worksheets named “Report” and “WIP”. The worksheet “WIP” contains data that has already summarized model results for WIP forecasts by product. Each product will contain a current WIP value, as well as an average and standard deviation for the product WIP forecast (these are sample average and sample standard deviation values calculated from the 25 simulation model runs). The worksheet “WIP” must always be hidden from the user. The specific actions to be performed in worksheet “Report” are described below.

WORKSHEET “Report” (6 points)

· The worksheet “Report” must include the following components:

o (1 pts) A rectangular shape with instructions about how to interact with the three buttons available in this worksheet.

o (1 pts) Three rectangular shape buttons labeled “ADD PRODUCT”, “CALCULATE TOTAL”, and “CLEAR FORM”.

§ (1 pts) The rectangular shape button labeled “CALCULATE TOTAL” must become visible only after there are at least two products in the table.

· (1 pts) The worksheet “Report” must not display gridlines. This functionality must be implemented via Excel VBA code.

· (1 pts) All data (i.e., inputs, results) and data formats must be cleared from the worksheet “Report” every time the file is opened.

· (1 pts) The only objects that must always be visible when the worksheet “Report” is first displayed or cleared are:

o The rectangular shape with the instructions, and

o The rectangular shape buttons “ADD PRODUCT” and “CLEAR FORM”.

Button “ADD PRODUCT” (30 points)

· The following functionality must be provided when the user presses the button “ADD PRODUCT”:

o  (1 pts) The first time a product is added to the report, the user will be prompted via an input box to specify the confidence level that the WIP ranges will be displayed with.

§ (1 pts) The input must be in decimal format (between 0 and 1).

§ (2 pts) If the user enters an invalid confidence level a message box telling the user to enter a valid confidence level must be displayed.

§ (2 pts) If the user presses the “Cancel” button or presses the button "OK" without entering a value, the program must stop execution, ensure nothing additional is shown on the report, and variables have not been updated.

o (1 pts) The user must be prompted to enter a valid product ID via a single input box.

§ (4 pts) The product ID entered must be validated that the product entered exists in the worksheet “WIP”. Checking against this worksheet (your version of a local database) ensures that any new products not originally in the worksheet will be allowed by simply updating the “database”.

§ (2 pts) If the user enters an invalid product ID a message box telling the user to enter a valid product must be displayed.

§ (2 pts) If the user presses the “Cancel” button or presses the button "OK" without entering a value, the program must stop execution, ensure nothing additional is shown on the report, and variables have not been updated.

o  (2 pts) The first time a product is added to the table, the column headers of the table that will organize the report must be displayed on the worksheet “Report”.

 

Table 1. Example of report table headers.

o  (2 pts) Once the user provides a valid product, a check must be performed to determine if that product has already been displayed.

§ (2 pts) If this is a duplicate product to one already displayed, a message needs to be displayed to the user that this product has already been added to the table. Do not add a new row to the table or update any variables.

o Once the user provides a valid product that is not a duplicate, a new row must be added to the table that will organize the output using the confidence level specified by the user.

§ (2 pts) The new row must include the product ID entered by the user, as well as the current WIP level.

§ (5 pts) The forecast needs to display low, average, and high levels that are calculated using the confidence level specified by the user and the formulas provided in formula 1 at the end of this document.

§ (2 pts) The output numbers must have consistent formatting and display no more than 2 decimal points each.

Button “CALCULATE TOTAL” (15 points)

· The following functionality must be provided when the user presses the button “CALCULATE TOTAL”:

o The total WIP forecast of the products selected by the user must be calculated using the information from the products entered by the user to their table.

§  (3 pts) The Total WIP forecast should be computed using the following formula:

TotInv =

§  (5 pts) The Total Variance should be computed using the following (pooled variance) formula:

TotVar =

(Keep in mind each value reported in the WIP worksheet represents results from 25 model runs, so the count that should be used for each i in these formulas is 25)

§ (5 pts) Display the Total confidence interval as a final row in the output table. Using the TotInv and TotVar values, the confidence intervals for all products selected by the user can be calculated using formula 1.  (Keep in mind that Standard deviation = ).  

§ (1 pts) Choose formatting to highlight this row differently than the rest of the table.

§ (1 pts) Ensure the value entered in all columns make sense (i.e. Your first column represents the Product ID, so call this Total or something similar, etc.)

Button “CLEAR FORM” (2 points)

· The following functionality must be provided when the user presses the button “CLEAR FORM”:

o (1 pts) All data (i.e., inputs, results) and data formats must be cleared from the worksheet “Report”.  

o (1 pts) The button labeled “CALCULATE TOTAL” must be hidden.

VBA specific requirements (10 points):

· (4 pts) Explicitly declare all variables (use Option Explicit).

· (4 pts) Except for the Excel VBA code needed to enable the functionality required when the Excel workbook first opens, all other Excel VBA code must be written in a single module (i.e., Module1).

· (2 pts) Make sure your program does not experience excessive flickering when processing the input data or displaying the results (i.e. if your program ever goes to another worksheet, it must not show up in the animation – turn screen updating off!).

SOLUTION REQUIREMENTS – Python (63 points total) 

Your Python solution must read in the data from a CSV file that contains the information in the WIP Excel file. The worksheet “WIP” contains data that has already summarized model results for WIP forecasts by product. Each product will contain a current WIP value, as well as an average and standard deviation for the product WIP forecast (these are sample average and sample standard deviation values calculated from the 25 simulation model runs). The specific actions to be performed in the Python program are described below.

Initial Information (4 points total):

· When your program starts, you must

o (2 pts) give the user general instructions for the program

o (2 pts) and then prompt the user to add a product.  

· “ADDING A PRODUCT” (32 points total)

o (1 pts) The first time a product is added, the user will be prompted to specify the confidence level that the WIP ranges will be displayed with.

§ (1 pts) The input must be in decimal format (between 0 and 1).

§ (2 pts) If the user enters an invalid confidence level a message telling the user to enter a valid confidence level must be displayed.

o (1 pts) The user must be prompted to enter a valid product ID.

§ (4 pts) The product ID entered must be validated that the product entered exists in the database information that was read in from the CSV file. Using this file (your version of a local database) ensures that any new products not originally in the worksheet will be allowed by simply updating the “database”.

§ (2 pts) If the user enters an invalid product ID a message telling the user to enter a valid product must be displayed.

o (2 pts) Once the user provides a valid product, a check must be performed to determine if that product has already been entered.

§ (2 pts) If this is a duplicate product to one already entered, a message needs to be displayed to the user that this product has already been added to the table. Do not add a new row to the table or update any variables.

o Once the user provides a valid product that is not a duplicate, a new row must be added to the table that will organize the output and use the confidence level specified by the user.  The updated table needs to be printed including:

§ (2 pts) Header information must be included that specify what all the output include (At a minimum it should include Product, Current WIP, Forecast Low, Average, High)

§ (2 pts) The new row must include the product ID entered by the user, as well as the current WIP level.

§ (5 pts) The forecast needs to display low, average, and high levels that are calculated using the confidence level specified by the user and the formulas provided in formula 1 at the end of this document.

§ (2 pts) The output numbers must have consistent formatting and display no more than 2 decimal points each.

· (2 pts) After a row has been added and printed, the following options need to be made available to the user: Add a new row, calculate the totals (only if two rows have been added), print to output file, or clear table and start over.

o (2 pts) The user’s choice must be verified and the correct choice must be performed.

o (2 pts) If an incorrect choice is made, the choices must be offered again.

· “CALCULATING TOTAL” (14 points)

o The total WIP forecast of the products selected by the user must be calculated using the information from the products entered by the user to their table.

§  (4 pts) The Total WIP forecast should be computed using the following formula:

TotInv =

§  (5 pts) The Total Variance should be computed using the following (pooled variance) formula:

TotVar =

(Keep in mind each value reported in the CSV file represents results from 25 model runs, so the count that should be used for each i in these formulas is 25)

§ (5 pts) Display the Total confidence interval including header information. Using the TotInv and TotVar values, the confidence interval for all products selected by the user can be calculated using formula 1.  (Keep in mind that Standard deviation = ).  

·  “PRINTING TO OUTPUT FILE” (11 points total)

o (6 pts) The resulting table with all products added must be printed to an output CSV, TXT, or XLSX file.

o (2 pts) If the user calculated the totals, those must also be printed to the same output file.

o (1 pts) The file needs to be saved in the same directory as where the .py file is running.

o (2 pts) Output needs to be easy to interpret. (Consider adding a whitespace line or two between the product table and the totals table.)

· “CLEARING INFORMATION” (2 points total)

o (2 pts) All data saved information must be reset so that the user could add a product from the beginning.  

FORMULAS REQUIRED

Confidence level and confidence intervals:

Confidence intervals fall under the topic of statistical inference where the objective is to make conclusions about a population or probability distribution parameter based on sample observations. Confidence intervals are most often constructed as an interval that contains the mean of a probability distribution with a specified “confidence”.  The interpretation of confidence level applies to the repeated collection of data and construction of confidence intervals, where the confidence level equals the fraction of confidence intervals constructed that contain the actual mean of the probability distribution. The confidence level is often expressed as 1-α, where α is the probability of a Type I error (the probability the confidence interval does not contain the true mean of the probability distribution).

 

However, confidence levels are based on assumptions made about the probability distribution for which the data samples are realizations. If the data are assumed to be independent realizations of a normally distributed random variable X, and the standard deviation of X is estimated from the data, then the 95% confidence interval for the mean of X is:

 

Formula 1: Confidence interval formula for 1-α confidence level.

Where  is the sample average of n observations, s is the sample standard deviation, and  is a value from a t-distribution with n-1 degrees of freedom such that the probability of observing a value greater than is equal to α/2.  

When working in Excel note that: T.INV(1-α/2, n-1) = T.INV.2T(α, n-1) in Excel.  For example with α = 0.05 (95% confidence level) and n = 5,  = T.INV(0.975, 4) = T.INV.2T(.05, 4).

When working in Python, you can use the scipy.stats.t.ppf(q, df) function (see: https://www.geeksforgeeks.org/how-to-find-the-t-critical-value-in-python/). In this case, it is similar to the T.INV(1-α/2, n-1) function, where you need to use q = 1-α/2 and df = n-1 as the values you specify.  For example with α = 0.05 (95% confidence level) and n = 5,  = scipy.stats.t.ppf(0.975, 4).