AIN3701 Assignment 3 (DETAILED ANSWERS) 2024 - DISTINCTION GUARANTEED

AIN3701 Assignment 3 (DETAILED ANSWERS) 2024 - DISTINCTION GUARANTEED - DISTINCTION GUARANTEED - DISTINCTION GUARANTEED Answers, guidelines, workings and references .. 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: 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 CategoryYearly trend - All CategoriesRanking of CategoriesYearly trend - Product per CategoryYearly trend - BraaiTop 10 ProductsSALES ANALYSIS REPORT - ,000 1,100 1,200JanFebMarAprMayJunJulAugSepOctNovDecBreakfastBraaiBunny ChowPotjiekosDesertsJanFebMarAprMayJunJulAugSepOctNovDecBoereworsSteakGrilled ChickenLamb ChopsSnoek12,900 12,080 10,540 9,000 6,133 BraaiDesertsPotjiekosBunnyChowBreakfast3,530 3,070 2,930 2,710 2,570 2,540 2,380 2,300 2,140 2,120 Top / Bottom 10Top 10Bottom 10COLOURSREDGREENBLUE 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.