VBA Model Assignment

In this assignment we are are creating an automated system for generating a mini dash- board for the use of the manager in the Production Planning problem, please refer to the file “Production Planning Problem.pdf ” on the Assignment Module for more information about the production planning problem. The data file PPP A1.xlsx includes all the required data for this assignment.

This assignment’s objective is to create three buttons which generates the following graphs.

• A line chart of the historical demand.

• A line chart of the historical inventory levels.

• A line chart of the historical production unit along with the demand.

To facilitate the interaction with your file, you need to create a main sheet that explains different part of your assignment. Below is an illustration of how the main sheet, and the graph sheet should look like.

Figure 1: This is how the main sheet should look like.

2

Figure 2: An illustration of one of the graph sheets (Notice the formatting of the dates, the aesthetics of the chart, and the illustration that the chart is displayed in a chart sheet).

3

The VBA functions/functionalities that you need to complete this assignment are the followings.

• How to format cells (or ranges).

• How to use WorksheetFunctions.

• How to activate a worksheet.

• How to delete an existing worksheet.

• How to refer to a chart in a worksheet.

• How to assign a macro to a button.

o

Note: The current data file include 57 observations. Once your are done with the above implementation, make sure that your Excel file can handle any number of observations. That is, if the data files’ observations increase or decrease, your Ex- cel file still works and creates the appropriate outputs. (Hint: One way to achieve this functionality is to use the concept of “last row” as discussed in class.)