# Excel Applications for Accounting Principles

Business

## Quiz 29 :

Model-Building Problem Checklist

Answer:

Financial statements

There are different types of financial statements prepared for accounting purpose like income statements, statement of retained earnings, balance sheet, statement of cash flow etc. These financial statements are used by owners, investors, banks, creditors, stock exchange, financial institutions etc.

Balance sheet shows all types of assets, all types of capitals, liabilities, loans balances etc. It shows financial position at a point of time which is closing date. Balance sheet is also called statement of financial position.

Prepare the financial statements before adjustments as shown below:

Therefore, the net income and balance sheet totals before adjustments are

and

respectively.

Prepare the financial statements after adjustments as shown below:

Therefore, the net income and balance sheet totals before adjustments are

and

respectively.

Prepare a pie chart based on the second set of data showing all major sources of funding for the company at year-end as shown below:

Total current liabilities are $1,165.6, long-term liabilities are $1,016, and stockholders' equity is equal to $1,263.6.

Answer:

Answer:

Calculate the following ratios for all five years (from 2008 to 2012) using the given financial statements in the Excel file.

Standard ratios and Statistics

Notes:

1. Working Capital is equal to current assets minus current liabilities.

2. Current ratio is equal to current assets divided by current liabilities.

3. Quick ratio is equal to current assets minus inventories divided by current liabilities.

4. Accounts receivable turnover is calculated by dividing net sales by the average account receivables.

5. The number of days' sales in receivables is calculated by dividing accounts receivables by net sales and multiplying by the number of days. In this case, 365 were used as the number of days.

6. Inventory turnover is calculated by dividing net sales by inventory.

7. The number of days' sales in inventory equals inventory divided by cost of goods sold and multiplied by the number of days. In this case, 365 was used as the number of days.

8. Times interest earned is calculated by dividing earnings before interest and tax by interest expense.

9. Total asset turnover equals net sales divided by total assets.

10. Rate of return on total assets equals net income divided by total assets.

11. Rate of return on stockholders' equity equals net income divided by stockholders' equity.

12. Earnings per share are calculated by dividing net income by the average of common shares outstanding.

13. Dividends per share are equal to dividends declared and paid divided by the average of common shares outstanding.

14. The price-earnings ratio equals market value per share divided by earnings per share.

15. The dividend yield equals dividends per share divided by price per share.

16. The dividend payout ratio equals dividends declared and paid divided by net income.

17. Gross profit ratio equals net sales minus cost of goods sold and divided by net sales.

18. Profit margin equals net income divided by net sales.

19. Long-term debt to equity ratio is calculated by dividing long-term debt by the stockholders' equity.

20. The overall debt ratio equals total debt divided by total assets.

21. Book value per share equals total stockholders' equity divided by the average of common shares outstanding.

22.

The ratio analysis shows that the company's profitability has decreased over these five years as the return on asset decreased from 5.06% to 1.64% and the return on equity decreased from 17.28% to 7.45%.

23. Moreover, the company's solvency has worsened because the long-term debt to equity ratio has increased from 1.48 to 2.58 resulting in more debt compared to equity. On the other hand, the company's liquidity has improved as the current asset ratio rose from 1.34 to 1.55 meaning that the company has increased its current assets in respect to its current liabilities.

Trends

The following table shows how each of the following accounts has changed year-over-year.

The year-over year change is calculated by subtracting the previous year's value from the current year's value and dividing by the previous' years value.

Since data for 2007 is not provided, the year-over-year change for 2008 cannot be calculated.

Note that Gross profit equals net sales minus cost of goods sold.

Other

(a) Sales per square feet are calculated by dividing net sales by total square feet.

(b) The common size income statements (vertical analysis) are calculated by converting all the amounts as percentage of net sales, this is done by dividing all the amounts by net sales. This has to be done for all five years using the each year's net sales.

(c) The common size balance sheets (vertical analysis) are calculated by converting all the amounts as percentage of total assets, this is done by dividing all the amounts by total assets. This has to be done for all five years using the each year's total assets

.

(d) Prepare a statement of cash flows for 2009-2012.

Cash flow from operating activities is calculated by starting with net income of each year

- Adding any non-cash expense, such as depreciation in the net income

- Adding decreases in current assets and subtracting increases in current assets

- Subtracting decreases in current liabilities and adding increases in current liabilities

Note that changes in the cash account are not included in the calculation of cash flows from operating activities.

Cash flows from financing activities include the dividends paid. Cash flows from operating activities include capital expenditures.

(e) Compute comparative income statements between years 2012 and 2011 (horizontal analysis). The dollar change between years 2012 and 2011 is computed by subtracting the 2011 amounts from the 2012 amounts. The percentage change is calculated by subtracting the 2011 amounts from the 2012 amounts and dividing by the 2011 amounts.

(f) Compute comparative balance sheets between years 2012 and 2011 (horizontal analysis). The dollar change between years 2012 and 2011 is computed by subtracting the 2011 amounts from the 2012 amounts. The percentage change is calculated by subtracting the 2011 amounts from the 2012 amounts and dividing by the 2011 amounts.

(g) Compute the dollar change and percentage change of the quarterly results for years 2012 and 2011. The dollar change between years 2012 and 2011 is computed by subtracting the 2011 amounts from the 2012 amounts. The percentage change is calculated by subtracting the 2011 amounts from the 2012 amounts and dividing by the 2011 amounts.

There is no answer for this question