Question $1[29$ marks$]$

Last month, your friend David expanded his business by opening a small cafe beside his clothing shop. He has kept track of the cafe's daily sales in a notebook entitled "Cafe Daily Sales." However, without detailed records, he finds it challenging to identify which menu items are the most popular and profitable. He's concerned that some items might not sell well and could impact the cafe's profitability. David has asked for your help analysing the sales data to address this issue. Below is the data from the "Cafe Daily Sales" book, where each record represents a sale:

$85$ cups of coffee sold at R$25\mathrm{.}00$ each

$42$ slices of cake sold at R$40\mathrm{.}00$ each

$28$ breakfast sandwiches sold at R$55\mathrm{.}00$ each

$57$ salad tubs sold at R$80\mathrm{.}00$ each

$35$ smoothies sold at R$50\mathrm{.}00$ each

$19$ specialty pastries sold at R$38\mathrm{.}00$ each

$68$ bottles of water sold at R$15\mathrm{.}00$ each

$23$ lunch combos sold at R$100\mathrm{.}00$ each

Required: $1\mathrm{.}1)$ Create a spreadsheet in Microsoft Excel with the provided information and name the spreadsheet "Cafe Daily Sales." $(4$ marks$)$

$1\mathrm{.}2)$ Calculate the revenue generated from each item sold in the shop. $(2$ marks$)$

$1\mathrm{.}3)$ Calculate the total revenue generated in Rand currency from all the items sold in Q$\mathrm{.}1\mathrm{.}2(2$ marks$)$

$1\mathrm{.}4)$ Use a formula to calculate the average quantity of items sold. $(3$ marks$)$

$1\mathrm{.}5)$ David offers a $15\%$ discount to his customers. Calculate the value of the $15\%$ discount applied to each item's unit price for David's customers. $(3$ marks$)$

$3$ HCLT$108-1-$July$-$Dec$2024-$FA$1-$LS$-$V$\mathrm{.}3-14072024$

$1\mathrm{.}6)$ Use the IF function to identify the items that sold more than $50$ units and label them as "Popular" and "Not Popular" for the items with less than $50$ units in a single column. $(5$ marks$)$

$1\mathrm{.}7)$ Insert a row above the table and add a DATE function to indicate the date of the sales analysis. Ensure the Long Date format is selected. $(3$ marks$)$

$1\mathrm{.}8)$ Use a function to find the lowest quantity sold. Use a function to combine the item name$($of the lowest quantity sold$),$ the lowest quantity sold, and the label based on your answer from Q$\mathrm{.}1\mathrm{.}6-$ "Popular" or "Not Popular." in a single column. $(7$ marks$)$

Question $2[15$ marks$]$

After completing the sales book for David, he has reached out for assistance, suggesting that visualizations would enhance understanding better than traditional bookkeeping methods.

Required:

$2\mathrm{.}1)$ Create a clustered column chart showing quantities sold using chart Style $4.(3$ marks$)$

$2\mathrm{.}2)$ Create a $3-$D Clustered bar chart $($Style $6)$ using Quantity Sold and Unit Price. Provide a title for your chart. $(4$ marks$)$

$2\mathrm{.}3)$ Create a doughnut pie chart using the Total Sales from the table. Enable the data labels and change the font size to $11.(4$ marks$)$

$2\mathrm{.}4)$ Create a Line with Markers chart using the Unit Price and enable Trendline to visualize the trend in David's shop. $(4$ marks$)$

$4$ HCLT$108-1-$July$-$Dec$2024-$FA$1-$LS$-$V$\mathrm{.}3-14072024$

Question $3[6$ marks$]$

$3\mathrm{.}1)$ You've been considering starting your own business in the tech industry. Inspired by an entrepreneurship event, you've decided to invest R$10,000$ at the end of each month for the next fifteen $(15)$ years to fund the development of a new mobile app, with an interest rate of $8\%$ per annum. You understand that this investment will play a crucial role in the success of your future business venture. How much should you have at the end of $15$ years? $(3$ marks$)$

$3\mathrm{.}2)$ You've recently decided to pursue your dream of owning a rental property. You've taken out a home loan valued at R$550,000$ to make this happen. The loan comes with an annual interest rate of $12\%$ and a repayment period of $20$ years. Calculate your monthly repayment amount if the monthly repayments are due at the end of each month.