Module-04-Assignment-Student-Name.xlsx

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!