Business Solutions Using Spreadsheet Models

Tap Globe Business Report

Case Two

“Taps Galore” is a specialist online store, situated in Singapore and was recently established by owners Alex and Ash.  It sells a limited range of tapware at discounted prices.

Alex and Ash currently stock a range of taps from eight manufacturers.  They advertise on social media and supply four different ranges – value, standard, supreme and superior.  They have calculated different discounts for each manufacturer based on the tap range.

They believe that customers buying ‘value’ items will be more focused on ‘discounts’ than customers who purchase more expensive tapware.  Alex and Ash buy the tapware at cost price and sell at recommended retail price less the advertised discount.  Alex and Ash charge a flat rate of $9.99 for delivery but have recently noticed that several of their competitors have begun to offer deals that include free delivery.

Alex and Ash conducted extensive market research before setting up the business.  Since their first sale in January 2018 they have recorded the details of all sales in an excel spreadsheet.  They have asked you to use the data provided in the spreadsheet to complete relevant calculations and to use Excel ‘tools’ to analyze the data provided.  They then require you to write a business report which analyses and discusses both the past profitability of the business and the various options available to help increase future profitability.

Alex and Ash are interested in both the popularity and profitability of different manufacturers and ranges. They hope you can provide some insight regarding, the type of tapware they should focus their efforts on. They also want to identify their best customers so that they can market to them appropriately. In addition, they want to have some ideas of potential growth in the coming months and the likely effect on the business if they offered free delivery for all purchases over $100.00.

A spreadsheet has been provided containing the data for “Taps Galore” sales for 2018 and 2019.

Evaluation Tasks

• You are expected to undertake descriptive, predictive, and prescriptive analysis of the data that Alex and Ash have maintained over the past two years. The results of the analysis are then required to be presented in a professionally organized report with respect to the requirements of the owners above and some specific expectations as follows:
• Provide an overview of gross profit trends, undertaking both descriptive and predictive analysis.
• Discuss the results of your descriptive analysis and the options for future sales growth based on the requirements of Alex and Ash.
• Identify and discuss the potential effects to the business if ‘Taps Galore’ offered free delivery for all purchases over $100.00.  Do you believe this is a good option? Why?
• Make three recommendations with regards to future changes which might assist in further improving the profitability of the business. Briefly justify these recommendations (i.e. prescribe what should be done and why this would be helpful, drawing on your predictive and prescriptive analysis).