Description

Financial Accounting Project

In this project you will perform a trend analysis on the Hotel sector for 3 years. The data provided to you is real data gathered on www.calcbench.com.  You will need the following formulas to perform the trend analysis.

Revenue

Using the revenue, you can examine the company’s expansion.

Operating Income

Using the operating income, you can examine the company’s profitability.

Operating Cash Flow

Operating cash flow is a measure of the amount of cash generated by a company's normal business operations. Operating cash flow is important because it indicates whether a company is able to generate sufficient positive cash flow to maintain and grow its operations, or whether it may require external financing.

Asset Turnover = Revenue/Assets

Asset turnover is the ratio that measures the amount of sales generated for every dollar of asset used.  Asset turnover shows the efficiency in use of assets, i.e. how much the assets generate sales. For example, an asset turnover of .70 means that for every dollar worth of assets owned, there was a sale of $0.70.

Analyzing historical asset turnover and by comparing it to other companies in the same industry helps to determine where a company stands in terms of managing assets.

If asset turnover is high compared to other companies, it can be an indication that the company is using the correct amount of assets to generate sales. If asset turnover is low compared to other companies, it can be an indication that capital is invested in too many assets in relation to what is needed.

Return On Assets (ROA) = Operating Income/Assets

Return on Assets (ROA) ratio measures the effectiveness of resource usage. ROA is a key profitability ratio which measures the amount of profit made by a company per dollar of its assets.  The better the job a company does in managing its assets to bring about profits, the greater this percentage will be.

Profit Margin = Operating Income/Revenue

A ratio of profitability measures how much out of every dollar of sales a company actually keeps in earnings. A higher profit margin indicates a more profitable company that has better control over its costs compared to its competitors.  Profit margin is displayed as a percentage; a 20% profit margin, for example, means the company has a net income of $0.20 for each dollar of sales.

Cleanse/Prepare the data

1.      Open Financial Accounting Raw Data and save it as Financial Accounting Trend Analysis your name completed.

2.      Review and understand the data. Resize and/or format the data as you desire.

Analysis

1.      Chart the Revenue for all hotels over three years. What conclusions can you draw from this chart? Document your conclusion on the chart or on the sheet next to the chart. (1.5 POINT)

2.      Chart the Operating Income for all hotels over three years. What conclusions can you draw from this chart? Document your conclusion on the chart or on the sheet next to the chart. (1.5 POINT)

3.      Chart the Operating Cash Flow for all hotels over three years. What conclusions can you draw from this chart? Document your conclusion on the chart or on the sheet next to the chart. (1.5 POINT)

4.      Create a new sheet called Asset Turnover.  (2.5 POINTS)

a.       Calculate Asset turnover for all companies for each of the three years. Use formulas with range names if you’d like. (0.5 POINT)

b.      Modify your formula to display 0 if assets are 0. Use conditional formatting on this range to identify asset turnover of 0. (0.5 POINT)

c.       Create a chart that shows all hotels with three years of Asset turnover. What conclusions can you draw from this chart? Document your conclusion on the chart or on the sheet next to the chart. (0.5 POINT)

d.      Create three more charts that show each year separately. What conclusions can you draw from these charts? Document your conclusion on the chart or on the sheet next to each chart. (1 POINT)

5.      Create a new sheet called ROA. ( 6.5 POINT)

a.       Calculate ROA for all companies for each of the three years. Use formulas with range names if you’d like. (0.5 POINT)

b.      Modify your formula to display 0 if assets are 0. Use conditional formatting on this range to identify ROA of 0. (0.5 POINT)

c.       Create a chart that shows all companies with three years of ROA. You will notice that if there is a huge range in these numbers the chart is difficult to read. Delete the chart. (0.5 POINT)

d.      Make a copy of your ROA calculations and then modify the formulas to display 0% if it is <-25%.   (0.5 POINT)

e.       Write a statement next to the range of numbers that large negative numbers been changed to 0%.  (0.5 POINT)

f.       Use conditional formatting on this range to identify numbers <= 0  (0.5 POINT)

g.      Use functions to determine the highest and lowest values in the entire range. (0.5 POINT)

h.      Recreate the chart to show all companies with three years of ROA. (0.5 POINT)

i.        Change the minimum and maximum on the vertical (value) axis to improve readability of the chart. (0.5 POINT)

j.        Change the Label position to improve the readability of the labels. (0.5 POINT)

k.      What conclusions can you draw from this chart? Document your conclusion on the chart or on the sheet next to the chart. (1 POINT)

l.        Create three more charts that show each year separately. Use functions to determine the highest and lowest values in each range. Change the minimum and maximum on the vertical (value) axis to improve readability of the charts. What conclusions can you draw from the charts? Document your conclusion on the chart or on the sheet next to each chart. (0.5 POINT)

6.      Create a new sheet called Profit Margin. (6.5 POINT)

a.       Calculate Profit Margin for all companies for each of the three years. Use formulas with range names if you’d like. (0.5 POINT)

b.      Modify your formula to display 0 if revenue is 0. Use conditional formatting on this range to identify profit margin of 0. (0.5 POINT)

c.       Create a chart that shows all companies with three years of profit margin. You will notice that if there is a huge range in these numbers the chart is difficult to read. Delete the chart. (0.5 POINT)

d.      Make a copy of your profit margin calculations and then modify the formulas to display 0% if it is <-25%.  (0.5 POINT)

e.       Write a statement next to the range of numbers that large negative numbers have been changed to 0%. (0.5 POINT)

f.       Use conditional formatting on this range to identify numbers <= 0 (0.5 POINT)

g.      Use functions to determine the highest and lowest values in the range. (0.5 POINT)

h.      Recreate a chart that shows all companies with three years of profit margin. (0.5 POINT)

i.        Change the minimum and maximum on the vertical (value) axis to improve readability of the chart. (0.5 POINT)

j.        Change the Label position to improve the readability of the labels. (0.5 POINT)

k.      What conclusions can you draw from this chart? Document your conclusion on the chart or on the sheet next to the chart. (0.5 POINT)

l.        Create three more charts that show each year separately. Use functions to determine the highest and lowest values in each range. Change the minimum and maximum on the vertical (value) axis to improve readability of the charts. What conclusions can you draw from the charts? Document your conclusion on the chart or on the sheet next to each chart. (1 POINT)