ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT Case Study ‘Pete’s Chea

Question

ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT

Case Study

‘Pete’s 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, Peter’s 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 Alan’s 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.

Explanation / Answer

Contents

The main purpose of this report is to provide insights into improving the business of Pete’s Cheap Car Sales’ second-hand company. Clearing out the long pending stocks of existing cars, providing insights on which car need to be bought, trends of cars widely sold. To avoid buying the old cars or least trend cars to buy and sell.

2. A brief presentation of the analysis covering the questions concerning Pete and Alan’s stagnating profits and the review of sales staff.

-> Some cars sell faster some doesn't.

-> They are in dilemma what type of cars are sold faster.

-> Difficulty in analyzing the type of customers buy cars more, whether it is fleet customers or private or both.

-> Is Japanese cars are selling faster?

-> Difficult to analyze the trends of sales.

-> Cars overflow and not enough space to accommodate.

-> Analyzing the quarter sales done by sales staff to make the decision whether they are eligible for promotion.

3. Discussion regarding the results of your analysis.

The analysis clearly shows that there is no proper plan for buying the cars, type of cars, and a number of cars. It is unclear what patterns of sales they have undergone previously.

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

-> Sales pattern need to be identified.

-> Customers base need to be analyzed.

-> Current trend needs to be taken care.

-> Types of cars sold need to be identified.

-> Insights from the existing data analyzed.

-> Quarter-wise sales and identification of what type of cars sold faster and didn't sale during each quarter.

-> Marketing mechanisms.

5. A short concluding summary of the content of the report.

The project summary provides insights and further techniques and mechanisms to be incorporated to increase the car sales and to identify the correct type of cars to buy.

6. 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.

1. The main reason for the overflow of cars is non-identification of a type of cars easily sold, not considering customer's interest. This need to be corrected. The report provides clear insight into what types of cars sold very higher in each quarter, the type of customers who bought, car's selling price and other factors.

2. The existing stock needs to be cleared. Identification of very old stock cars and cars which have less scope in market and selling in the discounted price. If old cars are kept, soon it will be depreciated and also since there is no good space to accommodate. It is again an issue.

3. Improving the marketing strategy based on customers interests.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.