AIN3701 Assignment 3 (COMPLETE ANSWERS) 2024 (592440) - DUE 10 September 2024

AIN3701 Assignment 3 Full Solutions 2024 (592440) - DUE 10 September 2024 ;100 % TRUSTED workings, Expert Solved, Explanations and Solutions. For assistance call or W.h.a.t.s.a.p.p us on ...(.+.2.5.4.7.7.9.5.4.0.1.3.2)........... AIN3701 – Data analysis and automation Assignment 3 – 2024 Unique number: 592440 Deadline: 10 September 2024, at 23:00 You have been appointed as a financial manager at Ubuhle Bemvelo Bistro (UBB). UBB is one of the top restaurants in South Africa, serving traditional South African dishes. UBB is situated in a private game reserve in Pretoria and is being visited by local and international visitors. UBB was also recognised by an international magazine as one of the must visit attractions in South Africa. Another achievement achieved this year was for UBB to be awarded as one of the top 10 restaurants in South Africa. You are reporting directly to the financial director, Mrs Ceki, who qualified as a CA(SA) in 1998. Part of your duties is to assist Mrs Ceki with drawing up visual dashboards for UBB. UBB uses Microsoft Excel 365 to generate visual dashboards. UBB has invested in a new sales system who generates summative records of sales during the year, per product category (category) and product. These records include the month of sale as well. The CEO of UBB, Mr Gokal, is very exited about this new system and has provided Mrs Ceki with the electronic records of the system. He asked Mrs Ceki to draw up a visual dashboard reflecting the sales volumes for the 2024 year. Mrs Ceki is not well versed in visual dashboards but know a few functions in Excel 365. She has spent around two weeks to generate a draft dashboard for one of the product categories. She presented the draft dashboard to Mr Gokal, and they agree that the dashboard should look like the following dashboard: Open Rubric lOMoARcPSD| Mrs Ceki is on leave for the following two weeks, and she asked you to draw up the visual dashboard. The visual dashboard should be fully automated in such a way that if data is changed, the dashboard should update immediately. Therefore, the use of Pivot tables is discouraged. Mrs Ceki and Mr Gokal also agree on the following company rules regarding this dashboard: • Company colours should be used. The RGB colours to be used is: • The yearly trend graph should have a dynamic y-axis. The way to calculate is that the lower value of the y-axis should be the minimum of the product category Category Yearly trend - All Categories Ranking of Categories Yearly trend - Product per Category Yearly trend - Braai Top 10 Products SALES ANALYSIS REPORT - 2024 400 500 600 700 800 900 1,000 1,100 1,200 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Breakfast Braai Bunny Chow Potjiekos Deserts 125 175 225 275 325 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Boerewors Steak Grilled Chicken Lamb Chops Snoek 12,900 12,080 10,540 9,000 6,133 Braai Deserts Potjiekos Bunny Chow Breakfast 3,530 3,070 2,930 2,710 2,570 2,540 2,380 2,300 2,140 2,120 Top / Bottom 10 Top 10 Bottom 10 COLOURS RED GREEN BLUE 100 38 0 130 0 0 156 81 33 255 192 0 lOMoARcPSD| selected less ten units and the upper value of the y-axis should be the maximum of the product category selected plus ten units. The y-axis should change when the combo box value is selected. Mrs Ceki has investigated this, but it seems that Excel 365 cannot accommodate this. One of her friends suggested that this could be possible by using Visual Basic for Applications (VBA). • This visual dashboard should only reflect units and not revenue. REQUIRED: Marks (a) Your task is to create a visual dashboard in Excel using the provided file "Assignment_". All coding and calculations should be done within this workbook. Once completed, save the workbook as "yourstudentnumber_Surname_A" (macro-enabled workbook), for example, "_VanStaden_A". The visual dashboard should meet the requirements specified by Mrs. Ceki and Mr. Gokal. Note that no marks will be awarded for using PIVOT tables. Please show all workings in the Workings worksheet. 39 (b) Answer the following section in your Excel workbook in the textbox provided in worksheet “Part B”. Analyse the visual dashboard you create and provide key findings based on the data presented. 8 (c) Answer the following section in your Excel workbook in the textbox provided in worksheet “Part C”. Evaluate the limitations of the visual dashboard and the data provided by the new sales system. Discuss any constraints or challenges in the dashboard, as well as any shortcomings or inaccuracies in the data that may affect the reliability of the insights generated. 3 [50]