LightningWorks Accounts Payable - Outstanding Balance Report \begin{tabular}{|l|l|l|r|} \hline & & & \\ 8 & Vendor Name & Category & Ap \\ \hline 9 & RTF Electric & Ftilitied & FA \\ \hline 10 & Ross County Water \& Sewer & Utilities & FA \\ \hline 1 & YNC Trucking & Transportation & TF \\ \hline 12 & Italian Leather Group Ltd. & Raw Materials & TF \\ \hline 3 & Union Plastics & Raw Materials & TF \\ \hline 1 & Freight to Go & Transportation & FA \\ \hline 1 & Temps R'Us & Labor & FA \\ \hline 1 & Notworth Telephone & Telephone & TF \\ \hline 1 & & & \\ \hline 9 & & & \\ \hline \end{tabular} Lightning)Works' accounts payable team can sometimes be delinquent in paying ljghtnirg)Works' vendors in a timely manner. In some cases, this is a deliberate effort to hold off payment if possible; in others, it is simply an oversight. The accounts payable group has developed a worksheet listing some of the vendors to which tightringllorks owes past due balances, organized by past due categories of 30days past due, 60-days past due, and 90 -days past due. Recently, some vendors have started to apply different penalty and discount schemes to overdue accounts, like those being proposed by Lightning)Works' accounts receivable team. As a preemptive measure, you have been asked to help calculate some of these possible penalty scenarios that LightninglWorks 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. Keep in mind that you should use cell references in your formulas wherever possible.| Complete the following: 1. Open the workbook named Unpaid.xIsx and then save the file as Unpaid Invoice Penalties (your last name).xlsx. 2. Some vendors have agreed on an industry-standard penalty of $60 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: $60 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: a. For accounts with a 90-days past due balance of $75 or more, apply a fee of 10% of the 90-days past due balance. b. For all other accounts, no penalty is applied. 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: a. For accounts with a past due balance of $10,000 or more, apply a penalty of $800. b. For accounts with a past due balance of less than $10,000 but more than $3,000, apply a penalty of $350. c. 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: a. For vendors in the Labor category, apply a fee of 5% of the total past due balance. b. For vendors in the Utilities category, apply a fee of 8% of the total past due balance (column G). c. For vendors in all other categories, apply a fee of 11% of the total past due balance. 6. Format columns H through K to match column G. 7. Save and close the Unpaid Invoice Penalties.xlsx workbook. LightningWorks Accounts Payable - Outstanding Balance Report \begin{tabular}{|l|l|l|r|} \hline & & & \\ 8 & Vendor Name & Category & Ap \\ \hline 9 & RTF Electric & Ftilitied & FA \\ \hline 10 & Ross County Water \& Sewer & Utilities & FA \\ \hline 1 & YNC Trucking & Transportation & TF \\ \hline 12 & Italian Leather Group Ltd. & Raw Materials & TF \\ \hline 3 & Union Plastics & Raw Materials & TF \\ \hline 1 & Freight to Go & Transportation & FA \\ \hline 1 & Temps R'Us & Labor & FA \\ \hline 1 & Notworth Telephone & Telephone & TF \\ \hline 1 & & & \\ \hline 9 & & & \\ \hline \end{tabular} Lightning)Works' accounts payable team can sometimes be delinquent in paying ljghtnirg)Works' vendors in a timely manner. In some cases, this is a deliberate effort to hold off payment if possible; in others, it is simply an oversight. The accounts payable group has developed a worksheet listing some of the vendors to which tightringllorks owes past due balances, organized by past due categories of 30days past due, 60-days past due, and 90 -days past due. Recently, some vendors have started to apply different penalty and discount schemes to overdue accounts, like those being proposed by Lightning)Works' accounts receivable team. As a preemptive measure, you have been asked to help calculate some of these possible penalty scenarios that LightninglWorks 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. Keep in mind that you should use cell references in your formulas wherever possible.| Complete the following: 1. Open the workbook named Unpaid.xIsx and then save the file as Unpaid Invoice Penalties (your last name).xlsx. 2. Some vendors have agreed on an industry-standard penalty of $60 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: $60 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: a. For accounts with a 90-days past due balance of $75 or more, apply a fee of 10% of the 90-days past due balance. b. For all other accounts, no penalty is applied. 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: a. For accounts with a past due balance of $10,000 or more, apply a penalty of $800. b. For accounts with a past due balance of less than $10,000 but more than $3,000, apply a penalty of $350. c. 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: a. For vendors in the Labor category, apply a fee of 5% of the total past due balance. b. For vendors in the Utilities category, apply a fee of 8% of the total past due balance (column G). c. For vendors in all other categories, apply a fee of 11% of the total past due balance. 6. Format columns H through K to match column G. 7. Save and close the Unpaid Invoice Penalties.xlsx workbook