Collin Villalobos, CFO of CollinIkes Nut and Bolt, has come to Bryan Renoir, (CollinIkes Staff Accountant) to discuss accounts receivables and invoicing. CollinIkes liquidity ratios have been underperforming. As a result, Collin wants

Bryan to send letters to each CollinIke client who has a balance that is over $450 and to each clients where 50% of the

clients total account balance is more than 60 days past due.

Additionally, over the weekend, CollinIkes invoicing system files became corrupted from hackers and the company is

unable to print invoices. As a result, Collin has also asked Bryan to make a basic invoice in Excel that can be used until

the system can be fixed.

In the attached Excel Spreadsheet, ExcelAssignment1.xlsm, Collin has provided Bryan with the companys accounts

receivable details as of March 31, 2017.

Part I - First, Bryan wants to filter the Aged A/R trial balance for accounts that are over $450. In Excel,

create a new column that uses an if statement to indicate YES if the account is over $450 and no if the

account is less than or equal to $450. Also, conditionally format the cell so any YES response is

highlighted in red.

Part II Next, Bryan wants to filter the Aged A/R trial balance for accounts where 50% of the total

balance is more than 60 days past due. In Excel, create a new column that uses an if statement to

indicate OVER 60 DAYS if 50% of the total balance is more than 60 days past due and CURRENT if

50% of the total balance is not more than 60 days past due. Also, conditionally format the cells so any

OVER 60 DAYS responses are highlighted in red and any CURRENT cells are highlighted in blue.

Part III Finally, Bryan wants to create a temporary invoice in Excel. The invoice

should include list drop-down boxes. The first list box will contain all of the customer names. The

remaining list boxes will contain all of the Items. The invoice should automatically lookup the price of

the item that is selected from the Items drop-down box and the Total for each row and the invoice total

should also be updated automatically when a quantity is input into the invoice.

A4odAccount Receivable Trial Balanc Dependable Mochanier ##| $ 144.00| $ *7.001$ Quiek Air andHeatinaCo. Excellont Heatinand Air C Superior Elestric, LLC A4odAccount Receivable Trial Balanc Dependable Mochanier ##| $ 144.00| $ *7.001$ Quiek Air andHeatinaCo. Excellont Heatinand Air C Superior Elestric, LLC