ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT

Case Study

Petes Cheap Car Sales is a small second-hand car yard situated in Melbourne, Victoria. Peter and Alan Moffet own the business and lease the car yard. There motto is We sell great cars at a cheap price

Peter selects and buys cars at auction and organises road worthy certificates, cleaning and repairs. Alan is responsible for managing the accounts, HR and marketing. They employ four sales staff to handle sales and assist with administration. Cars are sold to both private and fleet customers.

Over the past few months Peter has become concerned about stagnating profits. He notices that, whilst some cars sell quickly, others sit in the car yard for long periods of time. He realises that having cars sitting idle in the car yard is costing them money. He can find no pattern to sales trends. Is it the newer cars that sell well or perhaps smaller cars are more marketable? Should they be focusing on fleet buyers? Perhaps customers prefer Japanese cars? He is becoming extremely confused and frustrated. The one thing he knows for certain is that the car yard is small and the vehicle stock is overflowing. He clearly needs to reduce stock levels. His overall goal is to increase profits and he realises that he needs to focus on selling the most profitable vehicles.

Peter has employed you, a business analyst to help solve his dilemma. He has given you a copy of his sales details for the past six months and hopes you can use this data to recommend how he can achieve his goals of increased profits and reduced vehicle turnover time.

In addition, Peter is presently conducting a review of sales staff. Julia Robert, Peters senior salesperson, has announced her retirement and Peter hopes to promote a junior sales person to a senior position. You have been asked to conduct a financial review of the profitability and earnings of sales staff and make recommendations re possible promotions. As part of this review you should discuss at least two other factors that Peter might need to consider before he makes a final decision in this regard.

He has asked you to complete any relevant calculations and to use excel tools to analyse the relevant data. He then requires you to provide a business report which analyses:

Options regarding any changes he should make to the range of cars Pete focuses on in the caryard

Staff performance with regard to a possible promotion.

PREPARING THE BUSINESS REPORT

Report Content

The analysis, findings and recommendations which you prepare for Pete should be outlined in a professional business report.

This report should include:

A Table of Contents (TOC) generated by Microsoft Word. Default settings for the table of contents should not be altered.

A brief Introduction that outlines clearly the purpose of the report.

A brief presentation of the analysis covering the questions concerning Pete and Alans stagnating profits and the review of sales staff.

Discussion regarding the results of your analysis.

Identification and a discussion regarding other factors that may need to be considered before a final decision can be made regarding the staff promotion.

A short concluding summary of the content of the report.

Finally, at least three clear recommendations that Peter and Alan can adopt. These recommendations must be drawn from the analysis that you have carried out, and must have been discussed in the body of your report.