Overview of the assignment. How the assignmen was implemented. This should include broad steps taken to complete the assign

Assignment 1

OM 424: Advanced Business Analytics Instructor: MAJID KARIMI

Instruction: Please read and answer the following questions and submit the following deliverables on Cougar Courses.

1. A .PDF file of your written response. The report in pdf should include: (a) Title page with class section number, assignment number, and the student

name.

(b) Overview of the assignment.

(c) How the assignment was implemented. This should include broad steps taken to complete the assignment.

(d) Short Tutorial on how to use the .xlsm file. This should also be included in a sheet labeled “Main”, in your Excel submission.

(e) VBA Code. Please copy and paste your code as an appendix. Here is a good thread on how to neatly copy and paste your code in Mi- crosoft Word.

2. A Macro Enabled excel file labeled Assignment1.xlsm.

Time Estimate: Completing this assignment should approximately take you anywhere between 7.5 to 15 hours. Here is a detailed breakdown.

1. Read the problem description: 5 – 10 min

2. Analyze the data set (Excel file): 10 – 20 min

3. Review the lecture presentations related to this assignment: 30min – 1 hour

4. Select and implement your analytics solution in Excel: 5 – 10 hour

5. Create a draft of your written response: 1 – 2 hour

6. (Optional) Sit back and enjoy your great work: 5 – 10 min

7. Review and apply the necessary changes: 30 min – 1 hour

8. Submission: 10 – 20 min

Strategy for completing this assignment: This assignment is directly related to the first three topics we discussed: Visual Basics for Applications, Visual Analytics, and Dash- board Design. So be sure to review those materials first.

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.)

4

Raw_Data