Module 1 Mid-Level Exercise: LightningWorks Accounts Payable (MO-1.2, 1.4) LightningWorks' accounts payable team can sometimes be delinquent in paying LightningWorks' 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 LightningWorks owes past due balances, organized by past due categories of 30-days 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 LightningWorks' accounts receivable team. As a preemptive measure, you have been asked to help calculate some of these possible penalty scenarios that LightningWorks 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: Open the workbook named Unpaid.xlsx and then save the file as Unpaid Invoice Penalties (your last name).xlsx. 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. 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 $75 or more, apply a fee of 10% of the 90-days past due balance. For all other accounts, no penalty is applied. 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 $800. For accounts with a past due balance of less than $10,000 but more than $3,000, apply a penalty of $350. For accounts with a total past due balance of less than or equal to $3,000, do not apply a penalty. 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 5% of the total past due balance. For vendors in the Utilities category, apply a fee of 8% of the total past due balance (column G). For vendors in all other categories, apply a fee of 11% of the total past due balance. Format columns H through K to match column G. Save and close the Unpaid Invoice Penalties.xlsx workbook. Fixed Fee & 90 Day Penalties Graduated Penalties - Range Penalty Category Penalties Fixed Fee Penalty $60 Graduated penalty <= $3,000 $0 Labor 5% 90-day min amt for penalty $75 Graduated penalty <10,000but > 3,000 350 Utilities 8% 90-day penalty percentage 10% Graduated penalty >= 10,000 800 Other 11% LightningWorks Accounts Payable - Outstanding Balance Report Vendor Name Category Applies Fixed Penalty 30-Days Past Due 60- Expert Answer This solution was written by a subject matter expert. It's designed to help students like you learn core concepts. Step-by-step 1st step All steps Answer only Step 1/2 Based on the instructions provided, here are the formulas that need to be entered in the specified columns of the Unpaid Invoice Penalties.xlsx workbook: Explanation: Column H: =IF(C2=TRUE,60,0) Column I: =IF(AND(D2="90-Days Past Due",G2>=75),G2*0.1,0) Column J: =IF(G2>=10000,800,IF(AND(G2>=3000,G2<10000),350,0)) Column K: =IF(E2="Labor",G20.05,IF(E2="Utilities",G20.08,G2*0.11)) Can you give me the formula for each line for columns I and K because i have not right answer when i copied it down