Supply chain analysis

Supply chain analysis 150 150 Affordable Capstone Projects Written from Scratch







Assignment Objectives:

  1. Understand external file handling.
  2. Understand the use of an array in a sub program.
  3. Understand the sorting algorithm.
  4. Think and implement VBA programming to create reports.


Task #1 – External File Handling

Consider yourself as a Supply Chain Analyst working for the company Supply Corp. You are provided with the file that contains the data for the locations of its manufacturing plants and the distribution hubs. Each plant serves five distribution hubs located near to it and you are provided with these details including the latitudes, longitudes of the plants and the distribution hubs. Your objective is to find the transportation distance between each plant and the distribution hubs served by them and to report them to your manager. The data was collected and compiled for you in a text document named ‘Network’ (Network.txt).

The values in the input text file are in the following sequence: Serial Number, Type, ID, Latitude, Longitude and Served by Plant. Your manager is asking you to create a report based on this data, ENGR 1412 – INTRODUCTION TO COMPUTER PROGRAMMING Dr. Jennifer Glenn, Fall 2018


showing the transportation distance of each distribution hub from its plant and the total transportation distance for each plant. The transportation distance between any two places a and b is given by the following formula:-


The sequence of the procedure is as follows:

  • Read the data from the external file “Network.txt” and store them as arrays.
  • Calculate the transportation distance between each distribution hub and its plant.
  • Calculate the total transportation distance for each plant and sort them ascending.
  • Print the Hub ID, Plant and Distance in Excel cells and external file.
  • After printing the values for all the hubs, print the total transportation for each plant in the external file (total transportation details not required in Excel cells).
  • Create a button “Distance” to run this program and a “Clear” button to clear the Excel cells.

The external file report and the Excel report should appear as below: (Pictures represent the actual answers.) ENGR 1412 – INTRODUCTION TO COMPUTER PROGRAMMING Dr. Jennifer Glenn, Fall 2018



Note:- Print your name and lab section number in the external report file (Report.txt) as shown in the sample. You do not need to submit the input file (Network.txt) or the output file (Report.txt) to brightspace. Submit only the Excel file saved as macro enabled and a word document that contains the VBA code.

Task #2

Effective and User-Friendly Worksheet Design/Presentation:

1) Use cell attributes (color, font, etc.) to make an impression and highlight important information.

2) Provide information in cells to describe and clarify worksheet calculations.

3) Graphs should also be checked for their color, weight, fonts, labels, axes etc.


Validate your work:

Do something independently to confirm that your program provides the right answer, and mention it in your memo. For instance,

1) Compare your results with a minimum of two other results that are independently calculated (if the comparison is against the solution of other students, make sure to reference their names and sections).

2) Use your calculator to check representative calculations/formulas that you have used on the worksheet.


In your submission, provide specific information regarding how you validated your results and what are the results of your validation tests! Remember checking your program and “knowing” that it is right, or simply saying “it works” is not validation.

Submission Instructions:

Soft Copy – Electronically submit your memo, the spreadsheet and the word document to the Brightspace Dropbox folder for your primary TA in your lab section.

Note: Students are supposed to create their own solution template and submit them. Submit a copy of your codes in a word document. ENGR 1412 – INTRODUCTION TO COMPUTER PROGRAMMING Dr. Jennifer Glenn, Fall 2018



Assignment #5 – Due during the week of April 9th – April 14th, 2018 (Beginning of Labs)

Student Name:

Teaching Assistant:

ENGR1412 Section: Feature Points Grade
Brightspace/ Email, Memo – Complete, professional, workbook provided, subject given (Course, Section, Assignment#, Name).

· No memo (-10)

· No memo on Brightspace (-3)

· Incomplete work (-1 to -10) depending on the amount of work

· Copy of codes not provided in word document (-5 points)


Validation – Sufficient, credible, logical & references if needed, validation should appear in your submission along with a discussion and explanation of your work. You should always stand behind your work – if you are unwilling to do so why should anyone believe you.

· No mention of validation (-5)

· Improper validation (-1 to -5) depending on the amount of work


Worksheet Design and GPP Proper GPP followed and design is clear.

· GPP followed throughout the assignment

· Design is easily understood

· Explanatory notes are included if necessary

· Not clear – haphazard (-3)

· Improper GPP (-3)


Task #1 – External File Handling – Sub program created, buttons placed on the spreadsheet and GPP followed and external file created properly.

· No Program (-40)

· No proper input from external file (-10)

· No output in external file (-10)

· No output in excel cells (-10)

· Distances not sorted (-5)


Total Points 60