Hey guys, this might help you. I got 18 out of 20 for this one.
I’ve used VBA in the excel file, so make sure to enable macros to use all the features (after opening the excel file).
Here’s the task:
The excel file that needs to be submitted is here:
Task 1:
The three employees: Allen, Bhaljeet, and Conner have been running the manufacturing of sock world business. The “Task 1” sheet of the excel file depicts the information about the expenses on salary, rent, insurance, utility, and other expenses.
Allen works for 3 days (Monday, Tuesday, Wednesday) in a week whilst Bhaljeet works for rest of the weekdays (Thursday and Friday). Whereas, Connor works for all the weekdays. Even working for all the weekdays, Connor gets a lesser yearly salary than Allen. This is because the pay rate of Connor is lesser than Allen. Bhaljeet gets the salary at the same rate as Allen does.
Replacements for Allen, Bhaljeet, and Connor gets the salary at a significantly higher rate. The table at the bottom of the sheet depicts the summary of all the annual expenses (salaries, rent, insurance, and utilities) and their sum.
Task 2:
The first graph 2 (a) in the worksheet depicts the information about the quote amount of Allen and Bhaljeet with respect to time. The line graph shows how the quoted amount has changed over time for both the employees. Overall, during the working period of Allen, the quote amount reaches the maximum value on the 10th of September. The minimum quote amount of $61.6 also has been quoted during the working period of Allen. During the working period of Bhaljeet in the given month periods, the quote amount doesn’t exceed $1000 whereas, this limit exceeds multiple times in the working period of Allen.
Chart 2 (b) shows the manufacturing costs of each day. It clearly shows that the first two days the manufacturing cost is lesser than the rest of the weekdays. The manufacturing cost appears maximum on Fridays and minimum on Tuesdays. Which also means: during the working period of Bhaljeet maximum amount is spent on manufacturing the products and minimum during the working period of Allen (on Tuesdays).
Chart 2 (c) shows the increasing quote amount for each next month, whereas, as very few data is only present for the month of the October so the quote amount in the bar diagram for October appears less.
Line graph 2(d) portrays the information about the sum of manufacturing prices for each material in 5 different months. The graph shows “Cotton” as the maximum amount spent for its manufacturing as compared to the other items in the month of June whereas, in July, the maximum amount is spent on the manufacturing leather products. For the rest of the months, the maximum amount is spent on the manufacturing of silk products. Tweed products manufacturing costs the minimum for all the months.
Pie chart 2(e) shows the quote amount for each month in different colors. The chart clearly shows the maximum amount quoted in the month of September and minimum in the month of October as the data of only 14 items of October has been provided.
Task 3:
In the spreadsheet namely: Task 3 & 4 the top section does the computation of quote amount. The drop-down (list) is available for the selection of the items and right next to it the drop-down (list) for the selection of the size is given, which as a combination allows us to select different materials with their respective sizes. The button under those drop-down gives the quote amount.
The action of the button has been programmed using VBA (Visual Basic for Applications) and macros. As the button is pressed all the items of the sheet “Given Data” is scanned and only the amount and number of the matching items (as selected from the drop-down of the “Task 3 & 4” sheet is processed. During the computations quote amount for the selected item and size (from the drop-down) is added and the number of matching item is also counted by using a for loop (increasing counter for each match) also displayed in the sheet (Ongoing Calculations section). The sum of the quote amount is divided by the number of matches. The average quoted amount, hence obtained, is displayed in the cell with a green background color.
Task 4
The bottom section of the spreadsheet depicts the solution for Task 4. After selecting material and size, when the button “Manufacture Immediately?” is pressed, in the cell right above, shows if the manufacturing should be started immediately or not.
Visual Basic for Applications (VBA) and macro is used to apply action in the button “Manufacture Immediately?”. When the button is pressed, the material and the size selected from the drop-down is searched from the “Given Data” spreadsheet. During the scan, the number of matching rows is counted. The value of a counter is increased by one every time a match is found. The ongoing calculation section also shows the number of items matched.
As the question lets us decide to figure out the popular item, I’ve set the counter limit to 10. According to my program, if the number of items exceeds 10, the item is considered popular. When the number of matching items is more than 10, the button press shows which of the item is popular along with a message: the manufacturing can be started soon.
For the counter number less than 10, “No need to start the manufacturing.” is displayed.
Leave a Reply