Financial statement analysis

Profit and Loss

Instructions: 1. Using Microsoft Excel formulas, calculate a horizontal and vertical analysis for the first three months of
QB Cloud’s operations for the profit and loss.
QB Cloud
Profit and Loss
Horizontal & Vertical Analysis
	October	November	December	October - December	Quarterly Vertical Analysis	Horizontal Analysis
Income
401 Sales	6,000.00	11,920.00	13,220.00	31,140.00	100.00%	120.33%
Total Income	$6,000.00	$11,920.00	$13,220.00	$31,140.00	100.00%
Cost of Goods Sold
501 Cost of Goods Sold	0.00	5,960.00	6,610.00	12,570.00
Total Cost of Goods Sold	$0.00	$5,960.00	$6,610.00	$12,570.00		NA
Gross Profit	$6,000.00	$5,960.00	$6,610.00	$18,570.00
Expenses
601 Advertising	125.00	250.00	330.00	705.00	2.26%
603 Bank Charges	20.00	20.00	20.00	60.00
605 Dues & Subscriptions	150.00	0.00	125.00	275.00
607 Depreciation Expense	0.00	0.00	800.00	800.00
609 Freight & Delivery	64.65	89.15	52.65	206.45	0.66%
611 Insurance	0.00	0.00	750.00	750.00
619 Meals and Entertainment	126.40	46.40	109.71	282.51
621 Office Supplies	226.85	111.82	200.11	538.78		-11.79%
623 Rent or Lease	0.00	0.00	6,000.00	6,000.00
625 Repair & Maintenance	140.00	0.00	150.00	290.00
627 Shipping and delivery expense	47.00	0.00	47.00	94.00
629 Stationery & Printing	425.22	127.96	115.82	669.00
633 Telephone Expense	158.32	158.32	240.45	557.09
635 Utilities	79.00	84.32	85.33	248.65
Total Expenses	$1,562.44	$887.97	$9,026.07	$11,476.48
Net Operating Income	$4,437.56	$5,072.03	-$2,416.07	$7,093.52
Net Income	$4,437.56	$5,072.03	-$2,416.07	$7,093.52
Address the following questions. Be mindful of writing in complete sentences and using proper spelling and grammar.

1a. Identify and explain three horizontal analysis items that are not 0%.

1b. Identify and explain three vertical analysis items that are not 0%.

1c. Which expenses seem to be appropriated only to certain months? Do you believe this is the correct application or should they be appropriated differently? Why or why not?

Quarterly Vertical Analysis considers all items as a percent of Total Income. The formula is: Item/Total Income. I have done a couple for you- if you click on the cell, you can see the formula. To get out of that view, click the Enter or Return button. NOTE: THE ONLY COLUMN USED for this analysis is the October-December column. Horizontal Analysis measures the change in an account balance from one point in time to another. In this instance, we are measuring the change from October to December. The formula is (Current Value - Base Value)/Base Value, or (December amount - October amount)/October amount. Again, I've done a couple for you. Watch your formulas on this one- if you don't use the brackets around the subtraction order, Excel won't calculate your work correctly. Note: Any items that have a 0 balance in October will result in a #DIV/0! answer. You can replace those with a simple NA as your answer, since it cannot be calculated with the information given. This also means for the analysis questions, these items are not appropriate for horizontal analysis at this time.

Notice what the questions ask: IDENTIFY and EXPLAIN three items THAT ARE NOT 0%. This does NOT mean to explain why 3 items are not 0%!!! Rather, CHOOSE three items that are something other than 0% and explain them. If the term "explain" is not clear, you should be INTERPRETING what those three items mean. For example, horizontal analysis of the office supplies account shows the account has decreased by 11.79% over the three months the business has been open. This might indicate that the company had invested in a large amount of office supplies to get started, but have not needed to make the same investment monthly since. Note the colored font in the explaination above: Red for the IDENTIFICATION element, Blue for the EXPLANATION element of the question. WHY aren't 0% or 100% amounts good for our analysis? Because 0% indicates no change has occurred, so there's nothing to report. 100% is typically a base number, so it will ALWAYS be 100%. Therefore, there's nothing to analyze.

Balance Sheet

Instructions: 2. Using Microsoft Excel formulas, calculate a horizontal and vertical analysis for the first three
months of QB Cloud’s operations for the balance sheet.
QB Cloud
Balance Sheet
Horizontal & Vertical Analysis
	October	November	December	Quarterly Vertical Analysis	Horizontal Analysis
ASSETS
Current Assets
Bank Accounts
101 Checking	50,437.56	61,469.59	53,493.52	71.12%
Total Bank Accounts	$50,437.56	$61,469.59	$53,493.52		6.06%
Accounts Receivable
105 Accounts Receivable (A/R)	0.00	0.00	400.00
Total Accounts Receivable	$0.00	$0.00	$400.00
Other Current Assets
115 Merchandise Inventory	0.00	11,960.00	9,870.00
123 Prepaid Rent	6,000.00	6,000.00	0.00		-100.00%
125 Prepaid Insurance	3,000.00	3,000.00	2,250.00
Total Other Current Assets	$9,000.00	$20,960.00	$12,120.00
Total Current Assets	$59,437.56	$82,429.59	$66,013.52
Fixed Assets
135 Computer Equipment	10,000.00	10,000.00	10,000.00
137 Accumulated Depreciation	0.00	0.00	-800.00
Total Fixed Assets	$10,000.00	$10,000.00	$9,200.00
TOTAL ASSETS	$69,437.56	$92,429.59	$75,213.52	100.00%
LIABILITIES AND EQUITY
Liabilities
Current Liabilities
Accounts Payable
201 Accounts Payable (A/P)	0.00	17,920.00	3,120.00
Total Accounts Payable	$0.00	$17,920.00	$3,120.00
Other Current Liabilities
205 Loan Payable	5,000.00	5,000.00	5,000.00	6.65%
Total Other Current Liabilities	$5,000.00	$5,000.00	$5,000.00
Total Current Liabilities	$5,000.00	$22,920.00	$8,120.00
Total Liabilities	$5,000.00	$22,920.00	$8,120.00
Equity
301 Common Stock	60,000.00	60,000.00	60,000.00		0.00%
305 Opening Balance Equity	0.00	0.00	0.00
318 Retained Earnings	0.00	0.00	0.00
Net Income	4,437.56	9,509.59	7,093.52
Total Equity	$64,437.56	$69,509.59	$67,093.52
TOTAL LIABILITIES AND EQUITY	$69,437.56	$92,429.59	$75,213.52
Address the following questions. Be mindful of writing in complete sentences and using proper spelling and grammar.

2a. Identify and explain three horizontal analysis items that are not 0%

2b. Identify and explain three vertical analysis items that are not 0%.

2c. Do you think the short timeframe being measured here impacts the reliability of your analysis? Why or why not?

Quarterly Vertical Analysis considers all items as a percent of Total Assets. The formula is: Item/Total Assets. I have done a couple for you- if you click on the cell, you can see the formula. To get out of that view, click the Enter or Return button. NOTE: THE ONLY COLUMN USED for this analysis is the December column. Quarterly Vertical Analysis for the balance sheet will use the December column for our calculations because the balance sheet accounts are always cumulative. Therefore it doesn't make sense to have a "quarterly" column with the account balances added like in the income statement. Example: We have 50,437.56 Checking balance in October, 61,469.59 in November and 53,493.52 in December. But we do NOT have an overall balance in Checking of 165,400.67. We have a balance of 53,493.52. Notice how the accounts are naturally cumulative, so the December column IS the quarterly column. Horizontal Analysis measures the change in an account balance from one point in time to another. In this instance, we are measuring the change from October to December. The formula is (Current Value - Base Value)/Base Value, or (December amount - October amount)/October amount. Again, I've done a couple for you. Watch your formulas on this one- if you don't use the brackets around the subtraction order, Excel won't calculate your work correctly.

For details on how to address these questions, see the Profit & Loss tab for details and an example.

Ratio Analysis

Instructions: 3. Using Microsoft Excel formulas, calculate the following ratios
for QB Cloud and interpret the results:
	October	November	December
Debt Ratio	7.20%
Current Ratio		3.60
Profit Margin			-18.28%

Interpret the results of the ratios.

Ratio analysis is fairly simple. Complete the calculations and interpret the results. I've done a few for you- calculate them on your own to self-check your understanding. Debt Ratio is Total Liabilities/Total Assets Current Ratio is Total Current Assets/Total Current Liabilities Profit Margin is Net Income/Total Income For assistance on interpretation, check out the Module 04 Lessons folder materials. Because each of the ratios present tells a different part of the company's story, you need to interpret each of them individually. Be specific and clear!