ANALYSING A SPREADSHEET AND PRESENTING A BUSINESS REPORT Case Study ‘Pete’s Chea
ID: 394613 • Letter: A
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.
Peter is interested in clear, detailed advice supported by relevant evidence from past sales data.
A spreadsheet and following data has been provided for you containing the sales data for January 1 to June 30, 2018:
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.
Report Format
Specifically you should:
Ensure your report is concise: 1000 words. This word count includes title page and TOC. It does not include appendices.
Present the report in a professional, business format with no spelling or grammatical errors.
Use appropriate headings and subheadings throughout the body of the report.
Align text using full justification.
Use double or 1.5 spacing
Include a page header with a suitably formatted title (the header must not appear on the title page).
Include at least two charts – which you have created - either in the body of your report (appropriately labelled) or as an appendix at the end of the report. The material in the charts must be discussed as part of the paper.
In an appendix include screen shots of calculations which you have used to assist in your analysis.
inputs business costs sales Sales Staff SALES001 SALES002 SALES003 SALES004 Lex Julia Jules Anna Robertson Robert Ng Polletzo Salepersorn Senior saleperson Salesperson Senior Salesperson nual Base Salary 25,000 60,000 30,000 55,000 commission-on profit earned per vehicle Commission private/retail fleet 20% 15%Explanation / Answer
For making the above report, the calculations and analysis needs to be done in a precise manner. The content provided would not really suffice for the analysis as it is in png format. It needs to be in excel format to derive on certain conclusions, that is, profit margin, sales, inventory or which car is proving more profitable. It would be helpful if you attach the excel sheet for the analysis so that i would aid for preparing the report.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.