Spreadsheet Models Due to the recent economic slowdown, some vendors have started to apply different penalty schemes to overdue accounts. A local company has several outstanding balances on their unpaid invoices. You have been asked to help in calculating some of these possible penalties that the company might incur.

Please provide numerical values as whole numbers.

a. Some vendors have agreed on an industry standard penalty of $75 all past due accounts regardless of the past due amount. These vendors are identified by the value TRUE under Applies Fixed Penalty. In column H, for each vendor, use the appropriate formula to calculate whether the company must pay the fixed penalty. The total fixed penalty amount that the company might have to pay is _.

b. Some vendors charge penalty on accounts with 90-days past due balances. In column I, use the appropriate formula to calculate the possible penalty of an 18% on the balance of $125 or more on the accounts with 90-days past due. The total 90-days penalty that the company might have to pay is _. (Answer: 3151)

c. Some vendors use a scheme called graduated penalty where the amount of the past due balance is taken into account. In column J, use the appropriate formula to calculate the possible penalty based on the amount of the past due balance the company owes by applying the graduated penalty table (cells F2:H4). The total graduated penalty that the company might have to pay is: _.

d. Overall, including the past invoice balances and the possible penalties, the amount that the company owes in total is: _.

The answer for b is 3151. Help me solve parts a, c, and d.

If possible, please show your work using Excel functions.

Fixed Fee \& Penalties Fixed Penalty 90-days min amt for penalty 90-days penalty percentage B \begin{tabular}{|rr|} \hline$ & 75 \\ $ & 125 \\ & 18% \\ \hline \end{tabular} \begin{tabular}{|rr|} \hline$ & 75 \\ $ & 125 \\ & 18% \\ \hline \end{tabular} C D Graduated Penalties - Range Graduated penalty Graduated penalty Graduated penalty > = 501 501 5,001 L Accounts Payable - Outstanding Balance Report Vendor Name RTF Electric Ross County Water \& Sewer YNC Trucking Italian Leather Group Ltd. Union Plastics Freight to Go Temps R'Us Notworth Telephone Total \begin{tabular}{|c|c|c|c|c|c|} \hline Category & \begin{tabular}{l} Applies \\ Fixed \\ Penalty \end{tabular} & \begin{tabular}{l} 30-Days \\ Past Due \end{tabular} & \begin{tabular}{l} 60-Days \\ Past Due \end{tabular} & \begin{tabular}{l} 90-Days \\ Past Due \end{tabular} & \begin{tabular}{c} Total Past \\ Due \\ Balance \end{tabular} \\ \hline Utilities & FALSE & $19,700 & $ & $ & 19,700 \\ \hline Utilities & FALSE & 500 & 0 & 0 & 500 \\ \hline Transportation & TRUE & 0 & 0 & 3,100 & 3,100 \\ \hline Raw Materials & TRUE & 0 & 850 & 5,674 & 6,524 \\ \hline Raw Materials & FALSE & 7,250 & 436 & 0 & 7,686 \\ \hline Transportation & TRUE & 0 & 0 & 8,730 & 8,730 \\ \hline Labor & FALSE & 2,700 & 0 & 0 & 2,700 \\ \hline Telephone & TRUE & 0 & 100 & 0 & 100 \\ \hline \end{tabular}