CLICK HERE TO DOWNLOAD THIS ANSWER INSTANTLY $9.50 Only
Objectives:
To perform ratio calculations and analysis.
To look at an Annual Report for financial information.
To better understand dealer groups (which are publicly traded corporations).
To use Excel for a realistic business task.
Late assignments will receive a grade of zero.
What you need to do:
The corporation & years you have been assigned are:
Go online and obtain a copy of the most recent Annual Report (or 10K) in order to find the data needed to complete this assignment. This is found on their company website under “Investor Relations” (or some name like that), or at the EDGAR website (SEDAR if Canadian), or at a website like Yahoo Finance or Google Finance.
You may also need the prior year’s information for some of the comparative information.
Using Excel, present your calculations and analysis of the results of the two years assigned to you. You must calculate the following ratios: current ratio, A/R turnover, debt ratio, ROS, and ROA.
use the formulae printed in the first chapter in your course pack.
You must use the mathematical functions in Excel to do the calculations (do not just input the totals).
use the model below to assist with your layout. I am quite specific about what I want to see.
use $ signs and % signs where applicable. 2 decimal places for all columns except the year 1 and year 2 amounts.
Double lines on outside, single on inside.
It will need to be ‘landscape’ in order to fit “one page wide”. Standard or legal size. Adjust column widths as needed. Get formula column wide enough to fit on one line.
Show your calculations for subtotals in your ratios. In order to do this you will need a second, labelled tab for ‘data’ where you list the amounts you will use in your calculations.
Shade/colour results. Analysis cells should be coloured red (bad)/ green (good)/ yellow to save on words.
Shade the titles in your choice of colour that is not red, green, or yellow.
Include your name, and the company name in the header of your spreadsheet.
Write the analysis in the spreadsheet next to the calculation in the space allocated for it. Explain to the reader what the ratio is telling you about the business.
What is it measuring and is your business doing well or not?
If there is a problem because of the result, what is that problem?
If things are going well, what exactly is going well?
Is it doing ‘better’ or ‘worse’ than the prior year or compared to a benchmark from the textbook? Point form is acceptable. This analysis will be several lines long. (Benchmarks are useful but not required.)
State any assumptions you needed to make as footnotes.
If there are no dividends or you cannot find that information, then use $0.
If you cannot access information ie credit sales, just use the next closest piece of information (total sales) and write this as a footnote to your document.
Answer these questions on a separate, labelled tab:
Is this just a dealer group or what other businesses do they include in their consolidated statements?
Identify one performance standard that they brag about in their annual report which relates to dealership operations. Ok to cut/paste this one. Look for gross profits, service absorption, or some other measure of dealership success. (not # dealers or share price)
Cut and paste just the financial statement information you used in your calculations. Make each of the statements a separate, labelled tab in the Excel file. There are a number of free tools available to do this sort of copying. Just ask if you need assistance J
Make sure the ratios page is set up to print properly on one page. The recipient of a file should be able to just click on ‘print’ and not have to do any formatting.
