ISOM3230
Business Applications Programming
Learning Outcomes
Assignment 1
1. Apply programming concepts to solve business problems
2. Describe the logic and flows of given programs
3. Predict the output of a program
4. Write programs with common programming practices
5. Identify and fix logical and run-time errors in programs
Objective
Students are to design and develop an Excel application with VBA for managers to generate a sales report from the raw data. The application can provide all necessary information and analysis for managers to make a business decision.
Demo (for reference only)
Please find the demo video on Canvas > Modules > Assignment 1.
Please note that the demo is just for providing you with the basic idea. You may need to make modification to fulfil all the requirement and achieve better user friendliness.
Background
Sales report is essential tool for sales managers to make decision. However, the process of generating a sales report from raw data is time consuming. They need a highly user-friendly application to facilitate the process.
Users, i.e., Sales Managers need to input the path of the raw dataset which includes
– Product type
– Product name
– Unit Price
– Unit Cost
– Quantity sold for shops
and the application will generate a detailed summary with suggestions based on the data.
Basically, there are three core parts in this program
1) Data import
2) Calculation
3) Report generation
1
ISOM3230
Business Applications Programming
Requirements
1. You cannot use built-in formulas from Excel e.g., Average or Sum.
2. Programmers should prevent runtime errors, e.g., wrong path name of a data file or wrong file type.
3. Hidden sheets for storing value or calculation are allowed.
4. The number of shops is fixed. However, company may add more products. Make sure the program can be executed when the company adds more products.
5. Looping is NOT allowed in your program.
6. Your program will only be executed and tested on Excel 2019 (Windows) or 365 (Windows).
7. Graphical User Interface (GUI): Students are required to design their own GUIs. But their application needs to include the following required components in GUI:
Cover Page:
a. Background information and instruction
b. A title showing the program name: “Sales Report Generator”
c. A button to import data
Data Page:
a. All data from the dataset
b. A button to start calculating the total quantity and revenue for each product
c. A button to go to a next row and calculate the total quantity and revenue for the new row
d. A button to generate a report
Report Page:
a. Total Revenue, Quantity and Profit generated from the calculation
b. Average Revenue and Profit
c. Number of Products
d. The most and least popular product
e. Bar chart and Pie chart showing the revenue from each product
f. A button for switching the chart
g. Suggestions for sales managers
2
The following is a sample design for your reference in case you do not have any ideas of what the layout may look like:
You MUST create your own GUI to ensure user-friendliness.
SAMPLE COVER PAGE:
SAMPLE DATA PAGE:
SAMPLE REPORT PAGE:
3
ISOM3230
Business Applications Programming
8. Here are the basic inputs of the program:
a. Dataset in the desktop: download the dataset from Canvas.
b. The path of the dataset: allow users to input the path of the dataset after clicking on import data
button for the program to import data.
9. After importing the data, user will be directed to data page and all the data should be shown. The data should be properly formatted.
10. After clicking on calculation, three columns, “Total Revenue”, “Total Quantity” and “Total Profit” will be created.
The calculation of Total Quantity is as follows:
Q from Shop 1 + Q from Shop 2 + Q from Shop 3 Where Q= Quantity
The calculation of Total Revenue is as follows:
P X Q from Shop 1+ P X Q from Shop 2 + P X Q from Shop 3 Where P = Unit Price, Q = Quantity
The calculation of Total Profit is as follows:
(P-C) X Q from Shop 1 + (P-C) X Q from Shop 2 + (P-C) X Q from Shop 3 Where P = Unit Price, Q = Quantity, C=Unit Cost
11. After clicking on the Next button, the program will calculate the total revenue, total quantity, and total profit for the next row. Users needed to click on the button six times if there were six products.
12. After the calculation, user can click on the “Report” button to generate report. Here are some elements that the report should include.
a. Total Revenue (All Products)
b. Total Quantity (All Products)
c. Total Profit
d. Average Revenue (Total Revenue / Number of Products)
e. Average Profit
f. Number of products
g. Most Popular Product (Based on revenue)
h. Least Popular Product (Based on revenue)
i. Pie chart and bar chart showing the revenue from each product
Hint: You may use macro-recorder to generate programs for your reference.
4
ISOM3230
Business Applications Programming
j. Suggestions for the sales managers
• Increase the price (if the profit of the Most Popular Product is 50% higher than the
average)
• Reduce the price (if the profit of the Least Popular Product is 50% lower than the
average)
• No suggestions
e.g. If the profit of the Most Popular Product is $8000 and the average of the profit is $5000, the profit of the Most Popular Product is 60% higher than the average. The application needs to suggest the manager to increase the price of the Most Popular Product.
e.g., If the profit of the Least Popular Product is $2200 and the average of the profit is $5000, the profit of the Least Popular Product is 56% lower than the average. The application needs to suggest the manager reduce the price of the Least Popular Product.
If the above conditions are not met, there is no suggestion for the manager.
13. “Back” and “Exit” button are required to allow users to go back to previous page or exit the program. 14. Your program must have sufficient comments to explain the code to other developers.
15. Your program must use condition statement, i.e., if-then-else or select-case.
16. Your program must include both ActiveX control and form control buttons.
17. Your program must set Option Explicit to be On.
5
ISOM3230
Business Applications Programming
Marking Criteria
Area
Percentage
Logic Flow and Accurate Calculation
• Satisfying all requirements
• Error-free (No runtime or logical errors)
60%
Use of VBA
• Appropriate and efficient use of VBA programming
15%
Creativity
• Ease of use
• User friendliness
• Design of program
15%
Comments and Programming Styles
• Providing sufficient and meaningful comments
• Using meaningful controls, variable, and constant names
10%
Total: 100%
FAQs
1. I think that I am not good at programming. What should I do?
We encourage students to start thinking about the programming logics first by thinking how the program works when each macro is executed. For example, when a button is clicked, think about what the output is and where the output is positioned. As for extracting data from internet or using other functions on the tabs, make use of macro-recorder to get the VBA syntax for that function.
If you are still very lost, please feel free to contact our TA.
2. How to get high marks in creativity part?
Design the program from user perspectives. Create a user-friendly interface with clear instructions. Make sure users know how to control the program even if they have not used the program before.
Submission Guidelines
1. Please zip your program files and name the zip files as your student ID (e.g., 07123456.zip)
2. Submit your zip file to Canvas. Multiple submissions are allowed but only the last submission will
be graded. Therefore, you need to make sure the last submission before the deadline is the best
and more importantly it works.
3. Late submission within 24 hours will result in a penalty of 30% deduction in your total marks. No
further late submission is allowed.
4. Plagiarism (Copying) is a serious concern. Students might search for information/reference/program
on the Internet and “Copy & Paste” it directly in their assignments. Hence, a strict rule is applied
that MORE THAN SEVEN WORDS copied from a source is considered as a cheating. The MINIMUM penalty is zero marks for the assignment.
6