ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT

ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT

Case Study

Strobe Inc. is a large trendy warehouse nightclub in Melbourne’s inner Eastern suburbs. Here, patrons can enjoy a dance floor where DJs play loud electronic music and bars with a variety of non-alcoholic and alcoholic beverages.

The business is jointly owned by friends, Jose and Ellie, who met in an information systems workshop during their undergrad at RMIT. Jose takes care of the accounts/finances whilst Ellie manages Strobe Inc.’s nightly operations. They employ 3 security guards, 5 bar staff and 1 cleaner who are paid by the hour. Security and bar staff finish work depending on how busy the venue is so their hours can vary a little. Staff receive pay rises which apply from the 1st Jan each year. The DJ is paid a flat fee depending on the nights worked e.g. it varies from $500 on Thursday up to $800 Saturday. Jose and Ellie pay themselves out of the profit of the business.

Since opening in 2018 business has been booming, with upwards of 300 patrons on most nights. Strobe Inc. is open from 7:00pm to 7:00am nightly, Thursday evening to Monday morning. They charge an entry fee after 11:00pm which depends on the night- Friday and Saturday $30 and Thursday, Sunday $15. Currently they serve packaged snacks (eg chips and nuts) but no cooked or fresh food, mainly because they don’t have proper kitchen facilities.  They keep their pricing simple with a profit percentage on all drinks and snacks.

Recently (since Jan 2020), leasing and other costs have increased significantly and  Jose and Ellie are concerned this will put the business at risk of shutting down. Additionally, with construction completed on an apartment block next door, they have been receiving noise complaints from their new neighbours. They have not kept detailed financial records of the business- just nightly takings of drinks, snacks and entry fees. They pay their staff cash at the end of each night based on hours worked and have recorded the hours and pay for each staff member.

They have a spare, noise insulated band practice room that is currently used for storage. They have considered opening up the band practice room as another music option for earlier in the evening or on the quieter nights to increase business. The band room can accommodate roughly 100 audience members. Jose and Ellie have not done a thorough analysis yet but they think they would need at least 1 more junior security staff to manage the band room and  at least 1 more junior bar staff. Of course they would have to pay for bands (usually about $1000 per night) so an entry fee to the band room might be required to cover the costs or perhaps they just pay for the bands out of current profits. They’ve also estimated a setup cost of $350,000 to get it ready e.g. renovations, a small bar, furnishings, etc, which they would fund from their own savings.

Jose and Ellie love what they do and want to remain open. To do this, they seek your consultation. They not sure how been profitable they’ve been so far, for the current and the past financial year and they’re not sure if they’re going to be profitable in the last financial quarter of 2020 given the increasing staff costs and lease expenses.  Perhaps the band practice room is a good idea – if it makes them more profitable however they don’t know the analysis to conduct to ensure it is sensible. They’d have to work out to charge customers to make sure that they earn their $350000 back within 5 years.  Are they charging enough for their current product sales?  Are there problems with the staffing?

A spreadsheet has been provided containing data for the business. A “financial year” usually runs from 1st July until 30th June the following year. Josie and Ellie have recorded data from July 2018 until March 2020.

They have also provided some cost information and point out that some of their costs change on the 1st January each calendar year rather than the 1st July financial year.

As a business they haven’t really kept track of how many units of drinks are sold (This can be hard to know how many pints from a keg, or how many glasses from a bottle of wine) but they  keep track of sales figures in $ amounts for each category and they have given the profit% for each category.

Some of the data and calculations have been completed for you and you are guided to some of the other calculations and tools.

You will need to calculate Patron Revenue and Product Profits on the Nightly Takings worksheet. There are three additional columns at the end of the Calculations worksheet you will also need to complete, plus any additional calculations you may think the business needs.  A summary sheet has been started for you. You may continue to use these or you may make your calculations using any Excel methods/tools you are comfortable with.

Report Requirements

You are expected to undertake descriptive, predictive and prescriptive analysis of the data that Jose has provided and use Excel to analyse the data. The results of the analysis are then required to be presented in a professionally organized report that analyses and discusses both profitability so far and the various options available to help increase future profitability. Some specific expectations are as follows: 

  • Analyse the past two financial year’s sales figures –  has the business been profitable? Identify the key variables/features (eg products, costs, customer revenues, time, etc.) which have lead to this outcome.
  • Predict the likely profits for the final quarter of the financial year of 2020 (April – June) and for the following financial year July 2020 – June 2021 (predictive analysis)
  • Discuss (briefly) options for improving profitability based on your analysis in the first bullet.
  • Identify at least one business issue you have identified from your analysis that the business should further explore. Clearly explain your reasoning using data to support your discussion
  • Advise them on their expansion plans – Is it feasible to repay the $350000 costs within 5 years from the profits of the band room expansion given current interest rates? What should they charge? Justify your answer with calculations.
  • Make two specific recommendations with regard to improving profitability which are based on your previous analysis.  Briefly justify these recommendations (i.e. prescribe what should be done and why this would be helpful, drawing on your previous predictive and descriptive analysis). (These are in addition to the expansion plans)

Preparing the Business Report

Report structure and specifications

The analysis, findings and recommendations which you prepare for Jamie and Xin Rong should be outlined in a professional business report. This report should include

  • A brief introduction which outlines clearly the purpose of the report.
  • A discussion regarding the results of your analysis.  Relevant descriptive, predictive and prescriptive analysis has been included.
  • Relevant charts which are labelled correctly and assist the reader in understanding the results of your analysis.
  • A short concluding summary of the major findings of the report. (supported by relevant data: eg, use final figures you have calculated. etc)
  • Clear recommendations that the business owner can adopt. These recommendations must be drawn from the analysis you have carried out in the body of the report.
  • You should incorporate good report writing practices – refer to “Writing a Business Report” on the assignment page.

Report format

The report should be developed using the guidelines covered in Canvas and workshops. Specifically, you should:

  • Ensure your report is concise – No more than 1200 words.  This word count includes title page and TOC.  It does not include appendices
  • Include relevant charts and tables you have created in the body of your report (appropriately labelled). The charts and tables (screenshots of your spreadsheet are not appropriate) must be discussed as part of the paper.
  • In an appendix include screen shots of calculations (as discussed below) which you have used to assist in your analysis. (Note: You do not need to refer to this appendix in the body of your report.) A guide to inserting screen shots commences on page 4 of this document.
  • Do not include references or citations in this report

A Guide to inserting a screenshot in ‘Word’

  • Open and/or minimise the program/sheet you wish to create the screen shot from (in this case your Excel document). As your screenshots are designed to show calculations, you should make sure you have highlighted the relevant cell and the function is clearly visible in the function dialogue (fx) box.
  • Open your ‘Word’ document.
  • Select Insert/Screenshot/Screen Clipping.
  • Select the sheet you wish to create the screenshot from.   The screen should fade and a + appear.  Use the + as a cropping tool to select the required screenshot.  Note: There is no need to show every cell. (See Figure 1 in which one of the relevant cells is highlighted (B3) and the function for column B is clearly visible.)
  • Label the screenshot appropriately.
A screenshot of a cell phone
Description automatically generated

Figure 1 – Screen shot of Countif function

If you need to create a screenshot of a column that is not located near the fx box (see Figure 2) you can:

  • Select the relevant cell.  Double click on the cell to show the formula.
  • Create screen clipping as per the instructions above. Provide an appropriate label.
A screenshot of a cell phone
Description automatically generated

Figure 2 – Screenshot of Sumif function

To show several formulas in the one screenshot (see Figure 3) you can:                               

  • Select the relevant cells.
  • Select Formulas/Formula Auditing/Show formulas.
  • Take the screenshot as per the directions above.  (NOTE:  Text needs to be large enough to be readclearly.)
  • A screenshot of a social media post
Description automatically generatedFigure 3 – Screenshot showing functions in several columns

Leave a Reply