excel

UnPaid-23FallB

1

WEEK # 1 - EXERCISES

Week # 1 - Exercise 2-A – Unpaid Invoice Penalty

TheZone accounts payable group can sometimes be delinquent in paying The Zone’s vendors in a timely manner. In some cases, this is a deliberate effort to hold off payment as long as possible; in others, it is simply an oversight. The accounts payable group has developed a worksheet listing some of the vendors to which TheZone owes past due balances, organized by past due categories of 30-days past due, 60-days past due, and 90-days past due. Figure 4.35 shows this worksheet.

Recently, some vendors have started to apply different penalty and discount schemes to overdue accounts, similar to those being proposed by The Zone’s accounts receivable group. As a preemptive measure, you have been asked to help calculate some of these possible penalty scenarios that TheZone might incur based on its current outstanding balances. A list of these past due balances is provided in the workbook named Unpaid.xlsx. This file also contains the data input values that you need to calculate the penalties in the top portion of the worksheet (similar to the spreadsheet in Figure 4.35). Keep in mind that you should use cell references in your formulas wherever possible. Complete the following: 1. Open the workbook named Unpaid-1.xlsx attached with the data of the assignment, and then

save the file as 1-Ex2A-Unpaid-Invoice-Penalties-YourName.xlsx 2. Some vendors have agreed on an industry-standard penalty of $45 on all past due accounts regardless of the past due amount or number of days past due. These vendors are identified by the value TRUE in the corresponding row of column C. Write a formula in column H, which can be copied down the column, listing the penalty for the corresponding account: $45 for vendors that are participating in this standard penalty and $0 for all other vendors. Only vendors that are owed past due balances are listed on this sheet. 3. Calculate another possible penalty whereby only those accounts with 90-days past due balances are owed a fee. In column I, write a formula that can be copied down the column to calculate the penalty based on the following criteria:

• For accounts with a 90-days past due balance of $50 or more, apply a fee of 12% of the 90-days past due balance. • For all other accounts, no penalty is applied.

2

WEEK # 1 - EXERCISES

4. Another penalty scheme being used by vendors is a graduated method based on the total past due balances (column G). In column J, write a formula that can be copied down the column to calculate the penalty based on the following criteria:

• For accounts with a past due balance of $10,000 or more, apply a penalty of $600. • For accounts with a past due balance of less than $10,000 but more than $3,000, apply a penalty of $250. • For accounts with a total past due balance of less than or equal to $3,000, do not apply a penalty.

5. Penalties can sometimes be specific to vendor category. In column K, write a formula that can be copied down the column to calculate the penalty based on the following criteria:

• For vendors in the Labor category, apply a fee of 6% of the total past due balance. • For vendors in the Utilities category, apply a fee of 9% of the total past due balance (column G). • For vendors in all other categories, apply a fee of 12% of the total past due balance.

6. Format columns H through K to match column G. 7. Add your name and date at the end of the exercise 8. Save and close the 1-EX2A-Unpaid-Invoice-Penalties-YourName.xlsx workbook.

UnPaid-23FallB