Problem 9-2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | * represents an incorrect N answer =COUNTIF(A14:H27,"~*") |
Name: | | SOLUTION | | | | | | | | | | | | | | | | | | | | | | | | | | | 0 |
Section: | | | | | | | | | | | | | | | | | | | | | | | | | | | | | " " represents an unanswered N box - counts as an incorrect. =COUNTIF(A14:H27," ") |
Score: | | See student sheet for student's score | | | | | | | | | | | | | | | | | | | | | | | | | | | 0 |
Scoring: | | ON | | | | | | | | | | | | | | | | | | | | | | | | | | | " " represents a correct blank answer or N answer =COUNTIF(A14:H27," ") |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | 1 |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Total SUM(AV13:AV15) |
Instructions | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 1 |
Answers are entered in the cells with gray backgrounds. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Percentage =AD6/AD8 |
Cells with non-gray backgrounds are protected and cannot be edited. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 100% |
A red asterisk (*) will appear in the column to the right of an incorrect answer. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Notes: |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | " " represents an unanswered N box - counts as an incorrect. |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | " " represents a correct blank answer or N answer |
1. | BLUE BUFFALO COMPANY | | | | | | | | | | | | | | | | | | | | | | | | | | | | Total number of answers = sum of above |
| Comparative Income Statement | | | | | | | | | | | | | | | | | | | | | | | | | | | | Conditional formatting might be used but wasn't here, to hide some of the error check return symbols. If A1 = "~*", then font = red, if something else, then font = background color. |
| For the Years Ended December 31, 2012 and 2011 |
| | | | 2012 | | | | 2011 | | | | | | | | | | | | | | | | | | | | | Steps: |
| | | | Amount | | Percent | | Amount | | Percent | | | | | | | | | | | | | | | | | | | Open this sheet and macro sheet |
| | Sales | | $ 1,545,000 | | 103.0% | | $ 1,224,000 | | 102.0% | | | | | | | | | | | | | | | | | | | Open old templated, then change color palet to this sheet's |
| | Sales returns and allowances | | 45,000 | | 3.0% | | 24,000 | | 2.0% |
| | Net sales | | $ 1,500,000 | | 100.0% | | $ 1,200,000 | | 100.0% |
| | Cost of goods sold | | 960,000 | | 64.0% | | 780,000 | | 65.0% | | | | | | | | | | | | | | | | | | | Insert new header - change problem number and reformat |
| | Gross profit | | $ 540,000 | | 36.0% | | $ 420,000 | | 35.0% | | | | | | | | | | | | | | | | | | | Copy these formulas (column AD) to new sheet. |
| | Selling expenses | | $ 285,000 | | 19.0% | | $ 216,000 | | 18.0% | | | | | | | | | | | | | | | | | | | Update to new edition's names and numbers |
| | Administrative expenses | | 90,000 | | 6.0% | | 96,000 | | 8.0% | | | | | | | | | | | | | | | | | | | Copy new error check formulas. For N-boxes |
| | Total operating expenses | | $ 375,000 | | 25.0% | | $ 312,000 | | 26.0% | | | | | | | | | | | | | | | | | | | =IF(sol.!$C$5="OFF","",IF(AC25=""," ",IF(AC25<>sol.!AC25,"*"," "))) |
| | Income from operations | | $ 165,000 | | 11.0% | | $ 108,000 | | 9.0% |
| | Other income | | 36,000 | | 2.4% | | 36,000 | | 3.0% |
| | Income before income tax | | $ 201,000 | | 13.4% | | $ 144,000 | | 12.0% |
| | Income tax expense (benefit) | | 42,000 | | 2.8% | | 28,800 | | 2.4% | | | | | | | | | | | | | | | | | | | For B-Boxes |
| | Net income (loss) | | $ 159,000 | | 10.6% | | $ 115,200 | | 9.6% | | | | | | | | | | | | | | | | | | | =IF(sol.!$C$5="OFF","",IF(AC29<>sol.!AC29,"*"," ")) |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Copy Score formula from this template to new sheet. |
2. | The analysis indicates that sales, cost of goods sold, selling expenses, and net income all increased as a result of a successful sales promotion. | | | | | | | | | | | | | | | The analysis indicates that cost of goods sold and operating expenses improved, but net income did not. | | | | | | | | | | | | | =IF(sol.!$C$5="OFF","","Score:") | =IF(sol.!$C$5="OFF","",AD10) |
| | | | | | | | | | | | | | | | The analysis indicates that sales, cost of goods sold, selling expenses, and net income all increased as a result of a successful sales promotion. |
| | | | | | | | | | | | | | | | The analysis indicates that cost of goods sold and selling expenses increased more than net sales, reducing net income. |