Excel Test for Accounting & Bookkeeping

In double-entry bookkeeping, which Excel formula correctly evaluates whether Total Debits in cell B12 equal Total Credits in cell C12, returning "Balanced" if they match and "Unbalanced" if they differ?

In target cell B6, calculate the sum of all debit balances from B2 to B5 using the SUM function.

An invoice subtotal of $1,425.85 is subject to a 7.25% sales tax rate. Which formula ensures the calculated tax is rounded to standard currency cents (2 decimal places)?

Which of the following practices prevent cumulative penny rounding errors when calculating sales taxes or discounts across hundreds of ledger line items? (Select all that apply)

In target cell C2, calculate the 8.5% sales tax on the subtotal in B2, rounded to 2 decimal places using the ROUND function.

A cost center budgeted $5,000 in cell B2 and incurred $5,500 actual expense in cell C2. What is the standard formula to calculate the variance dollar amount (Actual minus Budget)?

In which financial reporting scenarios is the IFERROR function commonly used? (Select all that apply)

In target cell D2, calculate the variance percentage (Actual minus Budget divided by Budget) using cells B2 and C2, wrapped in IFERROR to return 0 if a division error occurs.

An auditor wants to verify the absolute dollar difference between Total Debits in cell B10 ($120,450) and Total Credits in cell C10 ($120,400) without returning a negative number regardless of which column is larger. Which formula should be used?

In accounts receivable management, which rules are standard practice when constructing an AR aging schedule? (Select all that apply)

In target cell D2, calculate the number of overdue days by subtracting the Due Date in B2 from the As-Of Date in C2 (=C2-B2).

If the number of overdue days is in cell D2, which formula assigns the label "90+ Days Overdue" if D2 is greater than 90, and "Under 90 Days" otherwise?

Why is SUMIFS generally preferred over SUMIF when designing monthly financial reports? (Select all that apply)

In target cell F2, calculate total expenses for account code "5100" from the transaction journal in A2:C5 using the SUMIF function.

When writing a =SUMIFS(...) formula to aggregate general ledger amounts, where does the sum_range argument sit in the function syntax?

A junior bookkeeper uses =VLOOKUP(A2, Accounts!A:C, 2, FALSE) to retrieve account names. What potential issues should they be cautious about? (Select all that apply)

In target cell G2, calculate the total spend for Account "5100" in Department "HR" using the SUMIFS function on table A2:D5.

An accounts payable clerk needs to look up payment terms (column 3) for Vendor ID "V-204" from table A2:C20. Which formula correctly retrieves the exact payment terms?

According to corporate financial governance and spreadsheet audit standards, which practices reduce error risks in accounting workbooks? (Select all that apply)

In target cell E2, look up the payment terms for the Vendor ID in D2 ("VEN-102") from the vendor directory table in A2:B4 using the XLOOKUP function.

A supplier offers terms of "2/10 Net 30" (a 2% cash discount if paid within 10 days). Cell C2 contains the days taken to pay (8 days), and cell B2 contains the invoice gross amount ($4,000). Which formula calculates the net amount payable?

Which of the following are native Excel functions used to compute fixed asset depreciation schedules? (Select all that apply)

In target cell D2, evaluate payment days in C2. If payment days are less than or equal to 10, calculate the discounted amount B2*0.98, otherwise return the full gross amount B2.

A commercial equipment balance of $14,250.30 is split into 12 equal monthly installments. Company policy mandates rounding up each monthly invoice to the nearest whole dollar so the balance is never underpaid. Which formula accomplishes this?

Scenario Background: The senior accountant asks you to complete the monthly preliminary trial balance. You need to calculate the net reconciliation difference between Total Debits and Total Credits to verify the ledger balances before closing.

In target cell D6, calculate the net reconciliation difference between Total Debits in B6 and Total Credits in C6 using =B6-C6.

Scenario Background: Credit management is auditing customer accounts. Management needs to identify the total exposure of overdue accounts that have exceeded their 30-day grace period.

In target cell C6, calculate the total overdue receivable balance for invoices whose Overdue Days in column B are greater than 30 using SUMIF.

During monthly bank reconciliation, how should outstanding checks (checks written by the company that have not yet cleared the bank) be treated?

Scenario Background: The Corporate Controller needs an executive variance report showing total actual spending across all operational departments for Q3.

In target cell C6, calculate the total actual expenditure across all three departments in C2:C4 using the SUM function.

Scenario Background: The AP supervisor is preparing Friday's check run. You need to look up the approved invoice amount for vendor "Apex Logistics" (ID: V-105) from the pending invoices schedule so the payment voucher can be authorized.

In target cell E2, retrieve the pending invoice amount for Vendor ID in D2 ("V-105") from the AP ledger in A2:C4 using VLOOKUP.

Scenario Background: At month-end, the accounting custodian reconciles the $500 imprest petty cash fund. Total receipts in the box plus remaining physical cash must equal the imprest float. Management needs to know the exact cash replenishment amount required.

In target cell B6, calculate the cash replenishment amount needed by subtracting Remaining Physical Cash in B5 from the Imprest Fund Total in B1 (=B1-B5).