Fluent Essays

complete  Excel spreadsheet 6B, 7-3B, 7-4B.  Spreadsheets and Data for each are attached. DATE TRANSACTION NUMBER OF UNITS PER UNIT TOTAL Apr. 3 Inventory 25 $1,200 $ 30,000 8 Purchase 75 1,240 93,000 11 Sale 40 2,000 80,000 30 Sale 30 2,000 60,000 May 8 Purchase 60 1,260 75,600 10 Sale 50 2,000 100,000 19 Sale 20 2,000 40,000 28 Purchase 80 1,260 100,800 June 5 Sale 40 2,250 90,000 16 Sale 25 2,250 56,250 21 Purchase 35 1,264 44,240 28 Sale 44 2,250 99,000 Gross profit from sales for the period. Answer $214,474 cost of goods sold, and the gross profit from sales for the period. Answer Gross profit, $213,170 Pr. 6-2B Problem 6-2B # Incorrect N-box and B-box entries COUNTIF(B15:G24,"~*") Name: 0 Section: # N-box Incorrects due to blanks COUNTIF(B15:AT24," ") 29 Score: 0% # N-box +B-box corrects COUNTIF(B15:AT24," ") 0 Key Code: [Key code here] Total SUM(AD13:AD15) Instructions 29 Answers are entered in the cells with gray backgrounds. Percentage =(AD16-AD13-AD14)/AD16 Cells with non-gray backgrounds are protected and cannot be edited. 0% An asterisk (*) will appear to the right of an incorrect entry. In part 1, only final inventory cost - Column K - will be graded. Notes: 1. Date Purchases Cost of Goods Sold Inventory If number-entry or blank-entry box is incorrect, returns "*" Quantity Unit Cost Total Cost Quantity Unit Cost Total Cost Quantity Unit Cost Total Cost If number-entry or blank-entry box is correct, returns single space, " " Use data verification to set data entry to whole number >= 0, and use drop-downs for lables and names, so that students can't enter a space in a box and have it counted as correct. Apr. 3 25 $1,200 $ 30,000 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. 8 11 30 May 8 cpence: Place the earliest units first, above the units that were received later. 10 19 28 June 5 16 21 28 Sales 30 Balances cpence: Enter the total of column H, the total cost of merchandise sold. cpence: Place the earliest units first, above the units that were received later. cpence: Place the latest units first, above the units that were received earlier. cpence: Enter the ending inventory balance. cpence: Place the earliest units first, above the units that were received later. cpence: Place the earliest units first, above the units that were received later. Accounts Receivable Merchandise Inventory Cost of Merchandise Sold 2. Total sales Total cost of goods sold Gross profit 3. Ending inventory cost Mark Sears: This should equal the ending inventory balance in part 1. cpence: Place the earliest units first, above the units that were received later. Sol Problem 6-2B Name: Solution Section: Scoring: ON Instructions Answers are entered in the cells with gray backgrounds. Cells with non-gray backgrounds are protected and cannot be edited. An asterisk (*) will appear to the right of an incorrect entry. In part 1, only final inventory cost - Column K - will be graded. 1. Date Purchases Cost of Goods Sold Inventory Quantity Unit Cost Total Cost Quantity Unit Cost Total Cost Quantity Unit Cost Total Cost Apr. 3 25 $1,200 $ 30,000 8 75 $1,240 $ 93,000 25 $1,200 $ 30,000 cpence: Place the earliest units first, above the units that were received later. 75 $1,240 93,000 11 40 $1,240 $ 49,600 25 $1,200 $ 30,000 cpence: Place the earliest units first, above the units that were received later. 35 $1,240 43,400 30 30 $1,240 $ 37,200 25 $1,200 $ 30,000 cpence: Place the earliest units first, above the units that were received later. 5 $1,240 6,200 May 8 60 $1,260 $ 75,600 25 $1,200 $ 30,000 cpence: Place the earliest units first, above the units that were received later. 5 $1,240 6,200 60 $1,260 75,600 10 50 $1,260 $ 63,000 25 $1,200 $ 30,000 cpence: Place the earliest units first, above the units that were received later. 5 $1,240 6,200 10 $1,260 12,600 19 10 $1,260 $ 12,600 cpence: Place the latest units first, above the units that were received earlier. 5 $1,240 6,200 5 $1,200 6,000 20 $1,200 $ 24,000 28 80 $1,260 $ 100,800 20 $1,200 $ 24,000 80 $1,260 100,800 June 5 40 $1,260 $ 50,400 20 $1,200 $ 24,000 40 $1,260 50,400 16 25 $1,260 $ 31,500 20 $1,200 $ 24,000 15 $1,260 18,900 21 35 $1,264 $ 44,240 20 $1,200 $ 24,000 15 $1,260 18,900 35 $1,264 44,240 28 35 $1,264 $ 44,240 20 $1,200 $ 24,000 9 $1,260 11,340 6 $1,260 7,560 30 Balances $ 312,080 cpence: Enter the total of column H, the total cost of merchandise sold. cpence: Place the earliest units first, above the units that were received later. cpence: Place the latest units first, above the units that were received earlier. $ 31,560 cpence: Enter the ending inventory balance. cpence: Place the earliest units first, above the units that were received later. cpence: Place the earliest units first, above the units that were received later. 2. Sales Total sales $ 525,250 Accounts Receivable Total cost of goods sold 312,080 Merchandise Inventory Gross profit $ 213,170 Cost of Merchandise Sold 3. Ending inventory cost $31,560 Mark Sears: This should equal the ending inventory balance in part 1. cpence: Place the earliest units first, above the units that were received later. PROBLEM DATA: PR 7-3B Bank reconciliation and entries The cash account for Stone Systems at July 31, 20Y5, indicated a balance of $17,750. The bank statement indicated a balance of $33,650 on July 31, 20Y5. Comparing the bank statement and the accompanying canceled checks and memos with the records reveals the following reconciling items: 1. Checks outstanding totaled $17,865. 2. A deposit of $9,150, representing receipts of July 31, had been made too late to appear on the bank statement. 3. The bank had collected $6,095 on a note left for collection. The face of the note was $5,750. 4. A check for $390 returned with the statement had been incorrectly recorded by Stone Systems as $930. The check was for the payment of an obligation to Holland Co. for the purchase of office supplies on account. 5. A check drawn for $1,810 had been incorrectly charged by the bank as $1,180. 6. Bank service charges for July amounted to $80.  Adjusted balance: $24,305 PR 7-4B Bank reconciliation and entries The cash account for Collegiate Sports Co. on November 1, 20Y9, indicated a balance of $81,145. During November, the total cash deposited was $293,150, and checks written totaled $307,360. The bank statement indicated a balance of $112,675 on November 30, 20Y9. Comparing the bank statement, the canceled checks, and the accompanying memos with the records revealed the following reconciling items: 1. Checks outstanding totaled $41,840. 2. A deposit of $12,200, representing receipts of November 30, had been made too late to appear on the bank statement. 3. A check for $7,250 had been incorrectly charged by the bank as $2,750. 4. A check for $760 returned with the statement had been recorded by Collegiate Sports Co. as $7,600. The check was for the payment of an obligation to Ramirez Co. on account. 5. The bank had collected for Collegiate Sports Co. $7,385 on a note left for collection. The face of the note was $7,000. 6. Bank service charges for November amounted to $125. 7. A check for $2,500 from Hallen Academy was returned by the bank because of insufficient funds. Adjusted balance: $78,535 Pr. 7-3B Problem 7-3B # Incorrect N-box and B-box entries COUNTIF(B15:G24,"~*") Name: 0 Section: # N-box Incorrects due to blanks COUNTIF(B15:AT24," ") 31 Score: 0% # N-box +B-box corrects COUNTIF(B15:AT24," ") 0 Key Code: [Key code here] Total SUM(AD13:AD15) Instructions 31 Answers are entered in the cells with gray backgrounds. Percentage =(AD16-AD13-AD14)/AD16 Cells with non-gray backgrounds are protected and cannot be edited. 0% An asterisk (*) will appear to the left of an incorrect entry. Notes: If number-entry box is blank (this would be an incorrect answer for N-boxes), error check returns two spaces, " " If number-entry or blank-entry box is incorrect, returns "*" 1. STONE SYSTEMS If number-entry or blank-entry box is correct, returns single space, " " Bank Reconciliation Use data verification to set data entry to whole number >= 0, and use drop-downs for lables and names, so that students can't enter a space in a box and have it counted as correct. July 31, 20Y5 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. Cash balance according to bank statement Adjustments: Bank error in charging check as $1,180 instead of $1,810 Bank service charges Total adjustments Deposit of July 31, not recorded by bank Adjusted balance Error in recording check Outstanding checks Cash balance according to company's records Proceeds of note collected by bank, including $345 interest Adjustments: cpence: Enter positive adjustments first, larger before smaller. Enter a deduction from cash balance as a negative amount. Total adjustments Adjusted balance 2. July 31 cpence: Record increases to cash in this entry. Accounts Payable - Holland Co. cpence: Enter credits in descending order, largest to smallest. cpence: Enter positive adjustment first. Enter deductions from cash balance as negative amounts, larger adjustment first. Cash Interest Revenue Miscellaneous Expense Notes Receivable 31 3. Amount to be reported on July 31, 20Y5, balance sheet Sol Problem 7-3B Name: Solution Section: Scoring: ON Instructions Answers are entered in the cells with gray backgrounds. Cells with non-gray backgrounds are protected and cannot be edited. An asterisk (*) will appear to the left of an incorrect entry. 1. STONE SYSTEMS Bank Reconciliation July 31, 20Y5 Cash balance according to bank statement $ 33,650 Adjustments: Deposit of July 31, not recorded by bank $ 9,150 cpence: Enter positive adjustment first, larger before smaller. Enter a deduction from cash balance as a negative amount. Outstanding checks (17,865) Bank error in charging check as $1,180 instead of $1,810 Bank error in charging check as $1,180 instead of $1,810 (630) Bank service charges Total adjustments (9,345) Deposit of July 31, not recorded by bank Adjusted balance $ 24,305 Error in recording check Outstanding checks Cash balance according to company's records $ 17,750 Proceeds of note collected by bank, including $345 interest Adjustments: Proceeds of note collected by bank, including $345 interest $ 6,095 cpence: Enter positive adjustments first, larger before smaller. Enter a deduction from cash balance as a negative amount. Error in recording check 540 Bank service charges (80) Total adjustments 6,555 Adjusted balance $ 24,305 2. July 31 Cash cpence: Record increases to cash in this entry. 6,635 Accounts Payable - Holland Co. Notes Receivable cpence: Enter credits in descending order, largest to smallest. cpence: Enter positive adjustment first, larger before smaller. Enter a deduction from cash balance as a negative amount. 5,750 Cash Accounts Payable - Holland Co. 540 Interest Revenue Interest Revenue 345 Miscellaneous Expense Notes Receivable 31 Miscellaneous Expense 80 Cash 80 3. Amount to be reported on July 31, 20Y5, balance sheet $ 24,305 Pr. 7-3B Problem 7-3B # Incorrect N-box and B-box entries COUNTIF(B15:G24,"~*") Name: 0 Section: # N-box Incorrects due to blanks COUNTIF(B15:AT24," ") 31 Score: 0% # N-box +B-box corrects COUNTIF(B15:AT24," ") 0 Key Code: [Key code here] Total SUM(AD13:AD15) Instructions 31 Answers are entered in the cells with gray backgrounds. Percentage =(AD16-AD13-AD14)/AD16 Cells with non-gray backgrounds are protected and cannot be edited. 0% An asterisk (*) will appear to the left of an incorrect entry. Notes: If number-entry box is blank (this would be an incorrect answer for N-boxes), error check returns two spaces, " " If number-entry or blank-entry box is incorrect, returns "*" 1. STONE SYSTEMS If number-entry or blank-entry box is correct, returns single space, " " Bank Reconciliation Use data verification to set data entry to whole number >= 0, and use drop-downs for lables and names, so that students can't enter a space in a box and have it counted as correct. July 31, 20Y5 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. Cash balance according to bank statement Adjustments: Bank error in charging check as $1,180 instead of $1,810 Bank service charges Total adjustments Deposit of July 31, not recorded by bank Adjusted balance Error in recording check Outstanding checks Cash balance according to company's records Proceeds of note collected by bank, including $345 interest Adjustments: cpence: Enter positive adjustments first, larger before smaller. Enter a deduction from cash balance as a negative amount. Total adjustments Adjusted balance 2. July 31 cpence: Record increases to cash in this entry. Accounts Payable - Holland Co. cpence: Enter credits in descending order, largest to smallest. cpence: Enter positive adjustment first. Enter deductions from cash balance as negative amounts, larger adjustment first. Cash Interest Revenue Miscellaneous Expense Notes Receivable 31 3. Amount to be reported on July 31, 20Y5, balance sheet Sol Problem 7-3B Name: Solution Section: Scoring: ON Instructions Answers are entered in the cells with gray backgrounds. Cells with non-gray backgrounds are protected and cannot be edited. An asterisk (*) will appear to the left of an incorrect entry. 1. STONE SYSTEMS Bank Reconciliation July 31, 20Y5 Cash balance according to bank statement $ 33,650 Adjustments: Deposit of July 31, not recorded by bank $ 9,150 cpence: Enter positive adjustment first, larger before smaller. Enter a deduction from cash balance as a negative amount. Outstanding checks (17,865) Bank error in charging check as $1,180 instead of $1,810 Bank error in charging check as $1,180 instead of $1,810 (630) Bank service charges Total adjustments (9,345) Deposit of July 31, not recorded by bank Adjusted balance $ 24,305 Error in recording check Outstanding checks Cash balance according to company's records $ 17,750 Proceeds of note collected by bank, including $345 interest Adjustments: Proceeds of note collected by bank, including $345 interest $ 6,095 cpence: Enter positive adjustments first, larger before smaller. Enter a deduction from cash balance as a negative amount. Error in recording check 540 Bank service charges (80) Total adjustments 6,555 Adjusted balance $ 24,305 2. July 31 Cash cpence: Record increases to cash in this entry. 6,635 Accounts Payable - Holland Co. Notes Receivable cpence: Enter credits in descending order, largest to smallest. cpence: Enter positive adjustment first, larger before smaller. Enter a deduction from cash balance as a negative amount. 5,750 Cash Accounts Payable - Holland Co. 540 Interest Revenue Interest Revenue 345 Miscellaneous Expense Notes Receivable 31 Miscellaneous Expense 80 Cash 80 3. Amount to be reported on July 31, 20Y5, balance sheet $ 24,305