attached
UnPaid-23SprB
| Fixed Fee & 9 | 0 | Graduated Penalties – Range | Penalty | Category | ||||||||||||||||||||||||||||||
| Fixed Fee Penalty | $ 45 | Graduated penalty <= | $ | 3,000 | $ 0 | Labor | 6% | |||||||||||||||||||||||||||
| 90-day min amt for penalty | $ 50 | Graduated penalty < | 10,000 | 250 | Utilities | 9% | ||||||||||||||||||||||||||||
| 90-day penalty percentage | 12% | Graduated penalty >= | 600 | Other | ||||||||||||||||||||||||||||||
| TheZone Accounts Payable – Outstanding Balance Report | ||||||||||||||||||||||||||||||||||
| Vendor Name | Applies | Fixed Penalty | 30-Days Past Due | 60-Days Past Due | 90-Days Past Due | Total Past Due Balance | 90-Days Penalty | Graduated Penalty | Category Penalty | |||||||||||||||||||||||||
| RTF Electric | FALSE | $ 18,100 | ||||||||||||||||||||||||||||||||
| Ross County Water & Sewer | 500 | |||||||||||||||||||||||||||||||||
| YNC Trucking | Transportation | TRUE | 3,100 | |||||||||||||||||||||||||||||||
| Italian Leather Group Ltd. | Raw Materials | 850 | 5,674 | 6,524 | ||||||||||||||||||||||||||||||
| Union Plastics | 7,250 | 436 | 7,686 | |||||||||||||||||||||||||||||||
| Freight to Go | 8,730 | |||||||||||||||||||||||||||||||||
| Temps R’Us | 2,700 | |||||||||||||||||||||||||||||||||
| Notworth | Telephone | |||||||||||||||||||||||||||||||||
| FNU-1 |
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.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
