Problem 10-4 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | * 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," ") |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | 15 |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Total SUM(AV13:AV15) |
Instructions | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 15 |
Answers are entered in the cells with gray backgrounds. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Percentage =AD6/AD8 |
Cells with non-gray backgrounds are protected and cannot be edited. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | * represents an incorrect N answer =COUNTIF(A14:H27,"~*") |
A red asterisk (*) will appear in the row immediately to the right of an incorrect answer. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 0 |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | " " represents an unanswered N box - counts as an incorrect. =COUNTIF(A14:H27," ") |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | 0 |
1. | Supporting calculations: | | | | | | | | | | | | | | | | | | | | | | | | | | | | " " represents a correct blank answer or N answer =COUNTIF(A14:H27," ") |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | 15 |
| | | | | | May 1 | | | | | | | | | | | | | | Cost of | | | | | | | | | Total SUM(AV13:AV15) |
| | | | | | Work in | | Direct | | Direct | | Factory | | Total | | Unit | | Units | | Goods | | | | | | | | | 15 |
| | Job. No. | | Quantity | | Process | | Materials | | Labor | | Overhead | | Cost | | Cost | | Sold | | Sold | | | | | | | | | Percentage =AD6/AD8 |
| | No. 0521 | | 100 | | $ 1,500 | | $5,000 | | $15,000 | | $ 18,000 | | $ 39,500 | | $395.00 | | 80 | | $ 31,600 | | | | | | | | | 100% |
| | No. 0522 | | 200 | | 4,000 | | 8,500 | | 26,000 | | 31,200 | | 69,700 | | $348.50 | | 160 | | 55,760 | | | | | | | | | Notes: |
| | No. 0523 | | 100 | | | | 3,500 | | 8,000 | | 9,600 | | 21,100 | | | | 0 | | 0 | | | | | | | | | " " represents an unanswered N box - counts as an incorrect. |
| | No. 0524 | | 125 | | | | 7,500 | | 25,000 | | 30,000 | | 62,500 | | $500.00 | | 105 | | 52,500 | | | | | | | | | " " represents a correct blank answer or N answer |
| | No. 0525 | | 90 | | | | 5,600 | | 17,500 | | 21,000 | | 44,100 | | $490.00 | | 75 | | 36,750 | | | | | | | | | Total number of answers = sum of above |
| | No. 0526 | | 70 | | | | 2,000 | | 4,500 | | 5,400 | | 11,900 | | | | 0 | | 0 | | | | | | | | | 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. |
| | Total | | 685 | | $ 5,500 | | $32,100 | | $96,000 | | $ 115,200 | | $ 248,800 | | | | | | $ 176,610 |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Steps: |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | Open this sheet and macro sheet |
| | | | Materials | | | | | | | | | | | | | | | | | | | | | | | | | Open old templated, then change color palet to this sheet's |
| | May 1 | | Beginning balance | | | | 9,000 | | | | | | | | | | | | | | | | | | | | | Insert new header - change problem number and reformat |
| | 31 | | Purchases | | | | 40,000 | | | | | | | | | | | | | | | | | | | | | Copy these formulas (column AD) to new sheet. |
| | 31 | | Requisitions | | | (A) | (34,600) | | | | | | | | | | | | | | | | | | | | | Update to new edition's names and numbers |
| | 31 | | Ending balance | | | 2. | 14,400 | | | | | | | | | | | | | | | | | | | | | Copy new error check formulas. For N-boxes |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | =IF(sol.!$C$5="OFF","",IF(AC25=""," ",IF(AC25<>sol.!AC25,"*"," "))) |
| | | | Work in Process |
| | May 1 | | Beginning balance | | | (B) | 5,500 | |
| | 31 | | Materials | | | (C) | 32,100 | |
| | 31 | | Direct labor | | | (D) | 96,000 | | | | | | | | | | | | | | | | | | | | | For B-Boxes |
| | 31 | | Factory overhead appl. | | | (E) | 115,200 | | | | | | | | | | | | | | | | | | | | | =IF(sol.!$C$5="OFF","",IF(AC30<>sol.!AC30,"*"," ")) |
| | 31 | | Completed jobs | | | (F) | (215,800) | |
| | 31 | | Ending balance | | | 2. | $33,000 | | | | | | | | | | | | | | | | | | | | | Copy Score formula from this template to new sheet. |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | =IF(sol.!$C$5="OFF","","Score:") | | =IF(sol.!$C$5="OFF","",AD10) |
| | | | Finished Goods |
| | May 1 | | Beginning balance | | | | 0 | | | | | | | | | | | | | | | | | | | | | =IF(sol.!$C$5="OFF","","*Since some answer boxes are correct when left blank, the beginning score is greater than 0%.") |
| | 31 | | Completed jobs | | | (F) | 215,800 | |
| | 31 | | Cost of goods sold | | | (G) | (176,610) | |
| | 31 | | Ending balance | | | 2. | 39,190 | |
| | | | Wages Payable |
| | May 31 | | Wages incurred | | | | 110,000 |
| | | | | | | | | | |
| | | | Factory Overhead |
| | May 1 | | Beginning balance | | | | (3,000) |
| | 31 | | Indirect labor | | | (H) | 14,000 | |
| | 31 | | Indirect materials | | | | 2,500 |
| | 31 | | Other overhead | | | | 102,900 |
| | 31 | | Factory overhead appl. | | | (E) | (115,200) | |
| | 31 | | Ending balance | | | 2. | $1,200 | |