Extensive detail and information is contained within the help function of Microsoft Excel and in the provided text.
You should enter your name, date, instructor's name, and course into the cells at the top of the page. This information will be printed on the top of each page if the template requires more than one page.
If more than one page is required by the template, manual page breaks have been set to provide consistent presentation.
And information or data which may be required by the solution will be entered in cells with borders to help identify them.
Be advised, the template workbooks and worksheets are not protected.
Overtyping any data may remove it.
Each template is set to print with File Name, Page # of # Page(s), the print date, and the print time to assist in assembly of multiple pages.
All of the cells have been correctly formatted for presentation and should not require any adjustment. For example, if the text requires one, two, or three significant digits in a presentation, the template has been set for that presentation in the appropriate cells.
In general, the yellow highlighted cells are the cells which work and effort should be presented. These entries may include date(s), account title(s), values, memorandum appropriate to the entry, or text answers to questions.
Where a yellow highlighted cell shows "Date" enter the appropriate date for that step of the challenge. This may be any date format that Microsoft Excel accepts. Some of these formats include "1/1/12", "01/01/12", and "01/01/2012." All of these will return January 01, 2012, in the format set in the template.
Where a yellow highlighted cell shows "Acct Nbr" enter the appropriate account number, provided in the template and in the text for that step of the challenge. This is entry may be a "Look to" formula to another cell where that information has been provided or previously entered.
Where a yellow highlighted cell shows "Account Title" enter the appropriate account title for that step of the challenge. This is a text entry and most of those cells are set for the proper indentation for that step. Frequently the chart of accounts appropriate to the challenge is provided and you can use the "look to" formula to reference the appropriate account title without typing it.
Check with your instructor to see if abbreviated account titles are acceptable. For example "A/R" for Accounts Receivable, "A/P" for Accounts Payable. If your instructor is using a comparison process between workbooks for grading, these abbreviates may not be acceptable.
Where a yellow highlighted cell shows titles such as "Values," "Amounts," or "Quantities" enter the appropriate numerical value for that step of the challenge. The cell is formatted for proper presentation of the entered information. If a dollar sign is appropriate, it should not be entered, Microsoft Excel will place it there through formatting. Commas and significant digits (decimals) are also set through formatting for common presentation. Since the formatting of the templates is not protected by any password, you may change any of the formatting found in the templates to meet your desires.
Where a yellow highlighted cell shows titles such as "Formula" you may enter the appropriate formula or enter a numerical value appropriate for that step of the challenge. Most of the values necessary for the appropriate formula are located on the template in cells with borders or in other yellow highlighted cells. The formula may be a simple "Look to" formula, an equal sign and a cell reference, "=E27" or more complex as "=E27*5," or something similar to the time-value-of-money formula. These are addressed in the tutorial text provided for Microsoft Excel.
Where a yellow highlighted cell shows titles such as "Journal Number" or "Journ #" you should enter the appropriate number provided in the template and in the text for that step of the challenge. In general this will appear in instances such as "Record the following events in General Journal number six."
The print area is defined to fit onto 8 1/2" × 11" sheets in portrait or landscape mode as required. Margins are generally set to no less than 1/2" so most printers can print them without a problem. If you printer cannot accept margins less than 1" you may have to reformat the margins through Page Setup.
The display may have "Freeze Pane" invoked so column titles remain visible during data entry. This can be removed by utilizing the View menu and selecting "Unfreeze Panes" under "Freeze Panes."
When negative values are required, enter them by starting with a minus sign, "-". Negative values may be shown as ($400) or -$400. Negative values in formulas can be created by putting a minus sign in front of the cell reference - "=E10*-E11" will return a negative value if both cells E10 and E11 contain positive values.
Microsoft Office and Microsoft Excel are products of, and copyrighted by, Microsoft Corporation, One Microsoft Way, Redmond, Washington 98052-6399
Where a yellow highlighted cell shows "Text" enter the appropriate text for that step of the challenge. This may be a
memorandum entry for a journal entry or a lengthy text answer discussing the results of an analysis of a company's financials. These titles can simply be typed over.
Where a yellow highlighted cell shows titles such as "Formula" you may enter the appropriate formula or enter a numerical value appropriate for that step of the challenge. Most of the values necessary for the appropriate formula are located on the template in cells with borders or in other yellow highlighted cells. The formula may be a simple "Look to" formula, an equal sign and a cell reference, "=E27" or more complex as "=E27*5," or something similar to the time-value-of-money formula. These are addressed in the tutorial text provided for Microsoft Excel.
Instructor:
Course:
1 a 7 c 2 c 8 c 3 b 9 a 4 b 10 c 5 c 11 a 6 b 12 b10. Hiring of a new president.
11. Settlement of prior year's litigation against the company. 12. Merger with another company of comparable size. 5. Retirement of the company president.
6. Issuance of a significant number of shares of common stock. 7. Loss of a significant customer.
8. Prolonged employee strike.
9. Material loss on a year-end receivable because of customer's bankruptcy.
E24-2 (Post-Balance-Sheet Events) For each of the following subsequent (post-balance-sheet) events,
indicate whether a company should (a) adjust the financial statements, (b) disclose in notes to the financial statements, or (c) neither adjust nor disclose.
1. Settlement of federal tax case at a cost considerably in excess of the amount expected at year-end.
2. Introduction of a new product line. 3. Loss of assembly plant due to fire.
4. Sale of a significant portion of the company's assets.
Primer on Using Excel in Accounting by Rex A Schildhouse
Intermediate Accounting, 14
thEdition by Kieso, Weygandt, and Warfield
Name:
Date:
Instructor:
Course:
1 Enter letter 7 Enter letter 2 Enter letter 8 Enter letter 3 Enter letter 9 Enter letter 4 Enter letter 10 Enter letter 5 Enter letter 11 Enter letter 6 Enter letter 12 Enter letter 10. Hiring of a new president.
11. Settlement of prior year's litigation against the company. 12. Merger with another company of comparable size. 5. Retirement of the company president.
6. Issuance of a significant number of shares of common stock. 7. Loss of a significant customer.
8. Prolonged employee strike.
9. Material loss on a year-end receivable because of customer's bankruptcy.
E24-2 (Post-Balance-Sheet Events) For each of the following subsequent (post-balance-sheet) events,
indicate whether a company should (a) adjust the financial statements, (b) disclose in notes to the financial statements, or (c) neither adjust nor disclose.
1. Settlement of federal tax case at a cost considerably in excess of the amount expected at year-end.
2. Introduction of a new product line. 3. Loss of assembly plant due to fire.
4. Sale of a significant portion of the company's assets.
Primer on Using Excel in Accounting by Rex A Schildhouse
Intermediate Accounting, 14
thEdition by Kieso, Weygandt, and Warfield
Instructor:
Course:
Operating Total Operating Identifiable
W $60,000 $15,000 $167,000
X 10,000 1,500 83,000
Y 23,000 (2,000) 21,000
Z 9,000 1,000 19,000
$102,000 $15,500 $290,000
Identifiable assets test: 10% × $290,000 = $29,000.
Segments W ($167,000) and X ($83,000) both meet this test. (b) Operating profit (loss) test.
Operating profit test: 10% × ($15,000 + $1,500 + $1,000) = $1,750.
Segments W ($15,000) and Y ($2,000 absolute amount) both meet this test. (c) Identifiable assets test.
(a) Revenue test.
Revenue test: 10% × $102,000 = $10,200.
Segments W ($60,000) and Y ($23,000) both meet this test.
Instructions:
Determine which of the operating segments are reportable based on the:
E24-3 (Segmented Reporting) LaGreca Company is involved in four separate industries. The following
information is available for each of the four industries.
Primer on Using Excel in Accounting by Rex A Schildhouse
Intermediate Accounting, 14
thEdition by Kieso, Weygandt, and Warfield
Name:
Date:
Instructor:
Course:
Operating Total Operating Identifiable
W $60,000 $15,000 $167,000
X 10,000 1,500 83,000
Y 23,000 (2,000) 21,000
Z 9,000 1,000 19,000
$102,000 $15,500 $290,000
(c) Identifiable assets test.
Enter answer in this area Enter answer in this area Enter answer in this area (b) Operating profit (loss) test.
Enter answer in this area Enter answer in this area
Instructions:
Determine which of the operating segments are reportable based on the: (a) Revenue test.
Enter answer in this area
Primer on Using Excel in Accounting by Rex A Schildhouse
E24-3 (Segmented Reporting) LaGreca Company is involved in four separate industries. The following
information is available for each of the four industries.
Intermediate Accounting, 14
thEdition by Kieso, Weygandt, and Warfield
Instructor:
Course:
15% of the $35,000 $6,000 $300,000 2013 2012 $18,200 $12,500 148,000 132,000 131,800 125,500 105,000 50,000 1,449,000 1,420,500 $1,852,000 $1,740,500 $79,000 $91,000 76,000 61,500 9,000 6,000 1,300,000 1,300,000 388,000 282,000 $1,852,000 $1,740,500 2013 2012 $3,000,000 $2,700,000 1,530,000 1,425,000 1,470,000 1,275,000 860,000 780,000 610,000 495,000 244,000 198,000 $366,000 $297,000 $100,000 and $102,500Primer on Using Excel in Accounting
by Rex A SchildhouseIntermediate Accounting
, 14th Edition by Kieso, Weygandt, and Warfieldthrough a public subscription of common stock. Daniel Brown, who owns
common stock, was one of the organizers of Bradburn and is its current president. The company has been successful, but it currently is experiencing a shortage of funds. On June 10, Daniel Brown approached the Topeka National Bank, asking for a 24-month extension on two notes, which are due on June 30, 2013, and September 30, 2013. Another note of is due on March 31, 2014, but he expects no difficulty in paying this note on its due date. Brown explained that Bradburn’s cash flow problems are due primarily to the company’s desire to finance a
Net income after income taxes
Depreciation charges on the plant and equipment of
for the fiscal years ended March 31, 2012, and 2013, respectively, are included in cost of goods sold. Cost of goods sold
Gross margin Operating expenses
Income before income taxes Income taxes
BRADBURN CORPORATION Income Statement
For The Fiscal Year Ended March 31 Sales
Common stock (130,000 shares, $10 par) Retained earningsa
Total liabilities and owners' equity
aCash dividends were paid at the rate of $1.00 per share in fiscal year 2012 and $2.00 per share in fiscal
year 2013.
Liabilities and Owners' Equity Accounts payable
Notes payable Accrued liabilities Accounts receivable (net) Inventories (at cost)
Plant & equipment (net of depreciation) Total assets
BRADBURN CORPORATION Statement of Financial Position
March 31 Assets
Cash
The commercial loan officer of Topeka National Bank requested financial reports for the last 2 fiscal years. plant expansion over the next 2 fiscal years through internally generated funds.
P24-3 (Ratio Computations and Additional Analysis) Bradburn Corporation was formed 5 years ago
Notes receivable
Name:
Date:
Instructor:
Course:
Intermediate Accounting
, 14th Edition by Kieso, Weygandt, and WarfieldSolution
Column 4 Column 5 2013 2012 77,500 1,796,250 1,714,500 1,688,500 1,740,500 1,852,000 1,530,000 1,425,000 403,000 320,000 164,000 158,500 130,000 130,000 102,500 100,000 1,470,000 1,275,000 610,000 495,000 244,000 198,000 50,000 105,000 366,000 297,000 860,000 780,000 3,000,000 2,700,000 $320,000 --- = 2.02 to 1 $158,500 $403,000 --- = 2.46 to 1 $164,000 $270,000 --- = 1.70 $158,500 to 1 $298,000 --- = 1.82 $164,000 to 1 $1,530,000 --- = 19.74 $77,500 to 1 (3) Inventory turnover for fiscal year 2013.2013 Inventory Turnover =
Cost of goods sold --- =
Average inventory 2013 Quick ratio =
Current assets - Inventories --- =
Current liabilities (2) Acid-test (quick) ratio for fiscal years 2012 and 2013.
2012 Quick ratio =
Current assets - Inventories --- = Current liabilities 2013 Current ratio = Current assets --- = Current liabilities (a) Compute the following items for Bradburn Corporation:
(1) Current ratio for fiscal years 2012 and 2013.
2012 Current ratio =
Current assets --- =
Current liabilities Net income after taxes
Operating expenses Sales Income taxes (40%) Inventories = EOY 2010 Inventories = EOY 2011 Depreciation Gross margin Income before taxes Current assets Current liabilities Dividends
Total Assets = Mar 31, 2010 Total Assets = Mar 31, 2011 Cost of goods sold
Average inventory - 2011 Average total assets Total Assets = Mar 31, 2009
Fill in the provided matrix and utilize it as the matrix for "VLOOKUP" formulas within the cells below.
Instructions:
Instructor:
Course:
Intermediate Accounting
, 14th Edition by Kieso, Weygandt, and Warfield $1,688,500$297,000 --- = 17.3% $1,714,500 $366,000 --- = 20.4% $1,796,250 2012 2013 Change $3,000 $2,700 $300 1,530 1,425 105 1,470 1,275 195 366 297 69
4 An examination as to the extent that leverage is being used by Bradburn.
2 Projected financial statements for 2014 including a projected statement of cash flows. In addition, a review of Bradburn’s comprehensive budgets might be useful. These items would present management’s estimates of operations for the coming year.
3 A closer examination of Bradburn’s liquidity by calculating some additional ratios, such as day’s sales in receivables, accounts receivable turnover, and day’s sales in inventory.
Other financial reports and financial analyses which might be helpful to the commercial loan officer of Topeka National Bank include:
1 The statement of cash flows would highlight the amount of cash provided by operating activities, the other sources of cash, and the uses of cash for the acquisition of term assets and long-term debt requirement.
Net income after taxes 23.23%
(b) Identify and explain what other financial reports and/or financial analyses might be helpful to the
commercial loan officer of Topeka National Bank in evaluating Daniel Brown’s request for a time extension on Bradburn’s notes.
Sales 11.11%
Cost of goods sold 7.37%
Gross margin 15.29%
(5) Percentage change in sales, cost of goods sold, gross margin, and net income after taxes from fiscal year 2012 to 2013. Omit "000" from the values.
Percent Change 2013 Return on assets =
Current assets --- =
Current liabilities
(4) Return on assets for fiscal years 2012 and 2013. (Assume total assets were at March 31, 2009.)
2012 Return on assets =
Net income --- = Average total assets
Name:
Date:
Instructor:
Course:
Intermediate Accounting
, 14th Edition by Kieso, Weygandt, and WarfieldSolution
2013 2014 2015 $3,000.0 $3,333.3 $3,703.7 1,530.0 1,642.7 1,763.8 1,470.0 1,690.6 1,939.9 860.0 948.2 1,045.5 610.0 742.4 894.5 244.0 297.0 357.8 $366.0 $445.4 $536.7 102.5 102.5 (260.0) (260.0) (6.0) 281.9 379.2 (150.0) (150.0) $131.9 $229.2 11.11%Cost of goods sold increases at rate of [($1,530,000 - $1,425,000) / $1,425,000] 7.37%
10.26% $102,500 $2.00
$150,000 Loan extension is granted.
(d) Should Topeka National Bank grant the extension on Bradburn’s notes considering Daniel Brown’s statement about financing the plant expansion through internally generated funds? Discuss.
Topeka National Bank should probably grant the extension of the loan, if it is really required, because the projected cash flows for 2014 and 2015 indicate that an adequate amount of cash will be generated from operations to finance the plant expansion and repay the loan. In actuality, there is some question whether Bradburn needs the extension because the excess funds generated from 2014 operations might cover the $70,000 loan repayment. However, Bradburn may want the loan extension to provide a cushion because its cash balance is low. The financial ratios indicate that Bradburn has a solid financial structure. If the bank wanted some extra protection, it could require Bradburn to appropriate retained earnings for the amount of the loan and/or restrict cash dividends for the next two years to the 2013 amount of $2.00 per share.
Depreciation remains constant at
Dividends remain at per share.
Plant expansion is financed equally over the two years( each year). Assumptions:
Sales increase at a rate of [($3,000,000 - $2,700,000) / $2,700,000] despite depreciation remaining constant.
Other operating expenses increase at the same rate experienced from 2010 to 2011; i.e., at [($860,000 - $780,000) / $780,000)
Plant expansion Excess funds Deduct: Dividends Note repayment
Funds available for plant expansion Net income
Add: Depreciation Operating expenses Income before taxes Income taxes (40%) Sales
Cost of goods sold Gross margin
Bradburn Corporation should be able to finance the plant expansion from internally generated funds as shown in the calculations presented below.
(c) Assume that the percentage changes experienced in fiscal year 2013 as compared with fiscal year 2012 for sales and cost of goods sold will be repeated in each of the next 2 years. Is Bradburn’s desire to finance the plant expansion from internally generated funds realistic? Discuss.
Instructor:
Course:
15% of the $35,000 $6,000 $300,000 2013 2012 $18,200 $12,500 148,000 132,000 131,800 125,500 105,000 50,000 1,449,000 1,420,500 $1,852,000 $1,740,500 $79,000 $91,000 76,000 61,500 9,000 6,000 1,300,000 1,300,000 388,000 282,000 $1,852,000 $1,740,500 2013 2012 $3,000,000 $2,700,000 1,530,000 1,425,000 1,470,000 1,275,000 860,000 780,000 610,000 495,000 244,000 198,000 $366,000 $297,000 $100,000 and $102,500Primer on Using Excel in Accounting
by Rex A SchildhouseIntermediate Accounting
, 14th Edition by Kieso, Weygandt, and Warfieldthrough a public subscription of common stock. Daniel Brown, who owns
common stock, was one of the organizers of Bradburn and is its current president. The company has been successful, but it currently is experiencing a shortage of funds. On June 10, Daniel Brown approached the Topeka National Bank, asking for a 24-month extension on two notes, which are due on June 30, 2013, and September 30, 2013. Another note of is due on March 31, 2014, but he
Net income after income taxes
Depreciation charges on the plant and equipment of
for the fiscal years ended March 31, 2012, and 2013, respectively, are included in cost of goods sold. Cost of goods sold
Gross margin Operating expenses
Income before income taxes Income taxes
SANDBURG CORPORATION Income Statement
For The Fiscal Year Ended March 31 Sales
Common stock (130,000 shares, $10 par) Retained earningsa
Total liabilities and owners' equity
aCash dividends were paid at the rate of $1.00 per share in fiscal year 2012 and $2.00 per share in fiscal
year 2013.
Liabilities and Owners' Equity Accounts payable
Notes payable Accrued liabilities Notes receivable
Accounts receivable (net) Inventories (at cost)
Plant & equipment (net of depreciation) Total assets
BRADBURN CORPORATION Statement of Financial Position
March 31 Assets
Cash
The commercial loan officer of Topeka National Bank requested financial reports for the last 2 fiscal years. expects no difficulty in paying this note on its due date. Brown explained that Bradburn’s cash flow problems are due primarily to the company’s desire to finance a plant expansion over the next 2 fiscal years through internally generated funds.
P24-3 (Ratio Computations and Additional Analysis) Bradburn Corporation was formed 5 years ago
Name:
Date:
Instructor:
Course:
Intermediate Accounting
, 14th Edition by Kieso, Weygandt, and WarfieldColumn 4 Column 5 2013 2012 Formula Formula Formula Formula Formula Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount Amount --- = Formula to 1 Amount Formula --- = Formula to 1 Formula Formula --- = Formula Formula to 1 Formula --- = Formula Formula to 1 Amount --- = Formula #N/A to 1
(3) Inventory turnover for fiscal year 2013.
2013 Inventory Turnover =
Cost of goods sold --- =
Average inventory 2013 Quick ratio =
Current assets - Inventories --- =
Current liabilities (2) Acid-test (quick) ratio for fiscal years 2012 and 2013.
2012 Quick ratio =
Current assets - Inventories --- = Current liabilities 2013 Current ratio = Current assets --- = Current liabilities (a) Compute the following items for Bradburn Corporation:
(1) Current ratio for fiscal years 2012 and 2013.
2012 Current ratio =
Current assets --- =
Current liabilities Net income after taxes
Operating expenses Sales Income taxes (40%) Inventories = EOY 2010 Inventories = EOY 2011 Depreciation Gross margin Income before taxes Current assets Current liabilities Dividends
Total Assets = Mar 31, 2010 Total Assets = Mar 31, 2011 Cost of goods sold
Average inventory - 2011 Average total assets Total Assets = Mar 31, 2009
Fill in the provided matrix and utilize it as the matrix for "VLOOKUP" formulas within the cells below.
Instructions:
Instructor:
Course:
Intermediate Accounting
, 14th Edition by Kieso, Weygandt, and Warfield $1,688,500Formula --- = Formula Formula Formula --- = Formula Formula 2012 2013 Change
Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula
4 Enter text answer as appropriate. 2 Enter text answer as appropriate.
3 Enter text answer as appropriate.
Other financial reports and financial analyses which might be helpful to the commercial loan officer of Spokane National Bank include:
1 Enter text answer as appropriate.
Net income after taxes Formula
Note: The formulas in some cell formulas are "live" and need values placed in their source cells. (b) Identify and explain what other financial reports and/or financial analyses might be helpful to the
commercial loan officer of Topeka National Bank in evaluating Daniel Brown’s request for a time extension on Bradburn’s notes.
Sales Formula
Cost of goods sold Formula
Gross margin Formula
(5) Percentage change in sales, cost of goods sold, gross margin, and net income after taxes from fiscal year 2012 to 2013. Omit "000" from the values.
Percent Change 2013 Return on assets =
Current assets --- =
Current liabilities
(4) Return on assets for fiscal years 2012 and 2013. (Assume total assets were at March 31, 2011.)
2012 Return on assets =
Net income --- = Average total assets
Name:
Date:
Instructor:
Course:
Intermediate Accounting
, 14th Edition by Kieso, Weygandt, and Warfield2013 2014 2015
Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula
Amount Amount Amount Amount Amount Formula Formula Amount Amount Formula Formula
Enter text answer here.
Plant expansion is financed equally over the two years( each year). Loan extension is granted.
(d) Should Topeka National Bank grant the extension on Bradburn’s notes considering Daniel Brown’s statement about financing the plant expansion through internally generated funds? Discuss.
Depreciation remains constant at
Dividends remain at per share.
Assumptions:
Sales increase at a rate of
Cost of goods sold increases at rate of despite depreciation remaining constant.
Other operating expenses increase at the same rate experienced from 2010 to 2011; i.e., at Plant expansion
Excess funds Deduct: Title Note repayment
Funds available for plant expansion Net income
Add: Title Title
Income before taxes Title
Sales Title
Gross margin Enter text answer as appropriate.
(c) Assume that the percentage changes experienced in fiscal year 2013 as compared with fiscal year 2012 for sales and cost of goods sold will be repeated in each of the next 2 years. Is Bradburn’s desire to finance the plant expansion from internally generated funds realistic? Discuss.
Instructor:
Course:
2013 2012 $180,000 $275,000 220,000 155,000 270,000 150,000 1,060,000 980,000 25,000 25,000 2,585,000 1,950,000 (1,000,000) (750,000) $3,340,000 $2,785,000 $50,000 $75,000 170,000 200,000 450,000 190,000 2,100,000 1,770,000 570,000 550,000 $3,340,000 $2,785,000 $180,000 5.39% $275,000 9.87% 220,000 6.59% 155,000 5.57% 270,000 8.08% 150,000 5.39% 1,060,000 31.74% 980,000 35.19% 25,000 0.75% 25,000 0.90% 2,585,000 77.40% 1,950,000 70.02% (1,000,000) -29.94% (750,000) -26.93% $3,340,000 100.00% $2,785,000 100.00% $50,000 1.50% $75,000 2.69% 170,000 5.09% 200,000 7.18% 450,000 13.47% 190,000 6.82% 2,100,000 62.87% 1,770,000 63.55% 570,000 17.07% 550,000 19.75% $3,340,000 100.00% $2,785,000 100.00% Accrued expenses Bonds payable Capital stock Retained earnings Total Accumulated depreciation TotalLiabilities and Stockholders’ Equity Accounts payable
Accounts receivable (net) Short-term investments Inventories Prepaid expense Fixed assets 2013 2012 Assets Cash GILMOUR COMPANY Comparative Balance Sheet December 31, 2013 and 2012
December 31 Retained earnings
Instructions: (Round to two decimal places.)
(a) Prepare a comparative balance sheet of Gilmour Company showing the percent each item is of the total assets or total liabilities and stockholders' equity.
Liabilities and Stockholders' Equity Accounts payable Accrued expenses Bonds payable Capital stock Prepaid expense Fixed assets Accumulated depreciation Assets Cash
Accounts receivable (net) Short-term investments Inventories
GILMOUR COMPANY Comparative Balance Sheet December 31, 2013 and 2012
P 24-4 (Horizontal and Vertical Analysis) Presented below are comparative balance sheets for the
Gilmour Company.
Primer on Using Excel in Accounting
by Rex A SchildhouseIntermediate Accounting
, 14th Edition by Kieso, Weygandt, and WarfieldName:
Date:
Instructor:
Course:
Solution
Intermediate Accounting
, 14th Edition by Kieso, Weygandt, and Warfield2013 2012 $ Change % Change $180,000 $275,000 ($95,000) -34.55% 220,000 155,000 65,000 41.94% 270,000 150,000 120,000 80.00% 1,060,000 980,000 80,000 8.16% 25,000 25,000 0 0.00% 2,585,000 1,950,000 635,000 32.56% (1,000,000) (750,000) (250,000) 33.33% $3,340,000 $2,785,000 $555,000 19.93% $50,000 $75,000 ($25,000) -33.33% 170,000 200,000 (30,000) -15.00% 450,000 190,000 260,000 136.84% 2,100,000 1,770,000 330,000 18.64% 570,000 550,000 20,000 3.64% $3,340,000 $2,785,000 $555,000 19.93%
(d) Of what value is the additional information provided in part (b)?
A statement such as that in part (b) is a good analysis and breakdown of the total change in assets and liabilities and stockholders’ equity. The statement breaks down the 19.93% increase and makes it easier for analysts to spot any unusual items. The increase is explained on the asset side by an increase in accounts receivable, short-term investments, and fixed assets and on the liability side by an increase in bonds payable and capital stock. This statement makes analysis of the year’s operations generally easier.
Total
(c) Of what value is the additional information provided in part (a)?
The component percentage (common-size) balance sheet makes easier analysis possible. It actually reduces total assets and total liabilities and stockholders’ equity to a common base. Thus, the statement is simplified into figures that can be more readily grasped. It can also show relationships that might be out of line. For example, management might believe that accounts receivable of 6.59% is rather low. Perhaps the company is not granting enough credit. The increased percentage of bonds payable from 6.82% to 13.47% indicates increased leverage which may reflect negatively on the company’s debt-paying ability and long-run solvency. These percentages can be compared with those of other successful firms to see how the firm stands and to see where possible improvements could be made.
Accounts payable Accrued expenses Bonds payable Capital stock Retained earnings Fixed assets Accumulated depreciation Total
Liabilities and Stockholders’ Equity Cash
Accounts receivable (net) Short-term investments Inventories
Prepaid expense
December 31, 2013 and 2012
December 31 Increase or (Decrease) Assets
(b) Prepare a comparative balance sheet of Gilmour Company showing the dollar change and the percentage change for each item.
GILMOUR COMPANY Comparative Balance Sheet
Instructor:
Course:
2013 2012 $180,000 $275,000 220,000 155,000 270,000 150,000 1,060,000 980,000 25,000 25,000 2,585,000 1,950,000 (1,000,000) (750,000) $3,340,000 $2,785,000 $50,000 $75,000 170,000 200,000 450,000 190,000 2,100,000 1,770,000 570,000 550,000 $3,340,000 $2,785,000Amount Formula Amount Formula
Amount Formula Amount Formula
Amount Formula Amount Formula
Amount Formula Amount Formula
Amount Formula Amount Formula
Amount Formula Amount Formula
Amount Formula Amount Formula
Formula Formula Formula Formula
Amount Formula Amount Formula
Amount Formula Amount Formula
Amount Formula Amount Formula
Amount Formula Amount Formula
Amount Formula Amount Formula
Formula Formula Formula Formula
Accrued expenses Bonds payable Capital stock Retained earnings Total Accumulated depreciation Total
Liabilities and Stockholders’ Equity Accounts payable
Accounts receivable (net) Short-term investments Inventories Prepaid expense Fixed assets 2013 2012 Assets Cash GILMOUR COMPANY Comparative Balance Sheet December 31, 2013 and 2012
December 31 Retained earnings
Instructions: (Round to two decimal places.)
(a) Prepare a comparative balance sheet of Gilmour Company showing the percent each item is of the total assets or total liabilities and stockholders' equity.
Liabilities and Stockholders' Equity Accounts payable Accrued expenses Bonds payable Capital stock Prepaid expense Fixed assets Accumulated depreciation Assets Cash
Accounts receivable (net) Short-term investments Inventories
GILMOUR COMPANY Comparative Balance Sheet December 31, 2013 and 2012
P 24-4 (Horizontal and Vertical Analysis) Presented below are comparative balance sheets for the
Gilmour Company.
Primer on Using Excel in Accounting
by Rex A SchildhouseIntermediate Accounting
, 14th Edition by Kieso, Weygandt, and WarfieldName:
Date:
Instructor:
Course:
Intermediate Accounting
, 14th Edition by Kieso, Weygandt, and Warfield2013 2012 $ Change % Change
Amount Amount Formula Formula
Amount Amount Formula Formula
Amount Amount Formula Formula
Amount Amount Formula Formula
Amount Amount Formula Formula
Amount Amount Formula Formula
Amount Amount Formula Formula
Formula Formula Formula Formula
Amount Amount Formula Formula
Amount Amount Formula Formula
Amount Amount Formula Formula
Amount Amount Formula Formula
Amount Amount Formula Formula
Formula Formula Formula Formula
(d) Of what value is the additional information provided in part (b)?
Enter text answer as appropriate. Total
(c) Of what value is the additional information provided in part (a)?
Enter text answer as appropriate. Accounts payable Accrued expenses Bonds payable Capital stock Retained earnings Fixed assets Accumulated depreciation Total
Liabilities and Stockholders’ Equity Cash
Accounts receivable (net) Short-term investments Inventories
Prepaid expense
December 31, 2013 and 2012
December 31 Increase or (Decrease) Assets
(b) Prepare a comparative balance sheet of Gilmour Company showing the dollar change and the percentage change for each item.
GILMOUR COMPANY Comparative Balance Sheet
