Analysis Of Pete’s Cheap Car Sales And Recommendations For Increased Profitability

Analysis of Stagnating Profits

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:

1. Options regarding any changes he should make to the range of cars Pete focuses on in the caryard
2. Staff performance with regard to a possible promotion.

Peter is interested in clear, detailed advice supported by relevant evidence from past sales data.  

A spreadsheet has been provided for you containing the sales data for January 1 to June 30, 2018.

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

This report should include:

• A Title Page– With a relevant graphic, report title, student name/ number, course name (Business Computing 1), course number (ISYS2056), your workshop time and your workshop tutors’ names.
• A Table of Contents (TOC)generated by Microsoft Word.  Default settings for the table of contents should not be altered.
• A brief Introductionthat outlines clearly the purpose of the report.
• A brief presentation of theanalysis 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 summaryof the content of the report.
• Finally, at least three clear recommendationsthat 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.

“Pete’s Cheap Car Sales”, is a small second-hand car yard situated in Melbourne, Victoria. This small business consists of not more than four employees and facing a downward slope in their business activities. The purpose of this report is to analyse the performance of this business in regards to its profitability level, as well as staff performance, in order to help this business in achieving their goals of increased profits and reduced vehicle turnover time. In this report, the staff performance is also analysed as one of the senior salesperson has announced retirement so a junior salesperson will be promoted in that place. This report will help to understand the current situation of the business so that recommendations can be provided to make necessary changes in their business.

Analysis regarding the stagnating profits and review of sales staff

Analysis on the stagnating profits 

From the given sales data of January to June 2018, the analysis has been carried out to find the total sales value of cars based on the manufacturing year. The table below represents the total value of cars sold as per the year:

In the above analysis, it has been identified that cars manufactured in 2017 were being held more in the caryard and that that too it was Private/Retail type vehicles.

Analysis on the review of sales staff

In this analysis, the total profit achieved by the individual salesperson has been calculated. The table below represents the value of total profit generated by the four salesperson:

The above analysis depicts that salesperson “SALES003” (Jules Ng) has the most total sales with a value of $ 2,567,337.00.

Results of the Analysis

Results from analysis on the stagnating profits

The analysis being carried out with respect to the stagnating profits demonstrates that most of the cars which were being held in the caryard for more duration is manufactured in the year 2017 and that too those were of Private/Retail types. This means that the popularity of the Private/Retail types is less than the Fleet type cars. However, the most sales was achieved from the cars of 2017 year that is $2,837,674.00. From the count of vehicles being held in caryard, it has been determined that there is less demand from private/retail customers about $ 4,989,603.00 but the total sales was higher as compared to Fleet customers. Hence, it is recommended that the business should focus more on private/retail customers.

Results from analysis on the review of sales staff

From the analysis on review of sales staff, it has been identified that the salesperson “Jules Ng” is the most suitable salesperson for the position of senior salesperson. Jules Ng is considered appropriate for the promotion as the analysis shows “SALES003” has the most count of vehicles sold and it contributes to most of the sales being done by the business.

Discussion regarding other factors

The other factors that has to be taken into consideration before making final decision for the staff promotion is the behavior and other competitive skills. The technical skills along with the loyalty of that staff have to be analyzed and evaluated prior to promotion. The sales figure should not be considered as the single criteria for promoting to senior salesperson.

Conclusion

From the overall analysis from the provided sales data of “Pete’s Cheap Car Sales”, it has been identified that the main reason behind stagnating profits is sitting of certain cars in the car yard for longer periods of time. The review of staffs with respect to the total sales and count of vehicles being sold demonstrates that Jules Ng is the most suitable person for promotion to senior salesperson.

Recommendations

• The focus should be given on fleet customers so that the cars could be sold at increased rate and there is no stock overflow in the car yard.
• The sales figure only should not be considered while promoting the staff.
• The purchase of the vehicles from auction should be done more of latest vehicles rather than old make and model.

Bree, R.T. and Gallagher, G., 2016. Using Microsoft Excel to code and thematically analyse qualitative data: a simple, cost-effective approach. AISHE-J: The All Ireland Journal of Teaching and Learning in Higher Education, 8(2).

Kraus, D., 2014. Consolidated data analysis and presentation using an open-source add-in for the Microsoft Excel® spreadsheet software. Medical Writing, 23(1), pp.25-28.

Kumar, B.A. and Rahamathulla, V., 2018. A Novel Approach of Quantitative Data Analysis using Microsoft Excel.

Mondal, H. and Mondal, S., 2016. Sample size calculation to data analysis of a correlation study in Microsoft Excel®: A hands-on guide with example. International Journal of Clinical and Experimental Physiology, 3(4), p.180.

SlezÃ, P., Bokes, P., Pavol, N.Ã. and WaczulÃkovÃ, I., 2014. Microsoft Excel add-in for the statistical analysis of contingency tables. International Journal for Innovation Education and Research, 2(5), pp.90-100.

Tanavalee, C., Luksanapruksa, P. and Singhatanadgige, W., 2016. Limitations of using Microsoft excel version 2016 (MS excel 2016) for statistical analysis for medical research. Clinical spine surgery, 29(5), pp.203-204.

Winston, W., 2016. Microsoft Excel data analysis and business modeling. Microsoft press.

Zhu, J., 2014. Quantitative models for performance evaluation and benchmarking: data envelopment analysis with spreadsheets (Vol. 213). Springer.