Instructions - Excel Assignment 1-1

.docx

School

Utah State University *

*We aren’t endorsed by this school

Course

1500

Subject

Accounting

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by MajorTapirMaster74

Excel Assignment 1 CollinIke’s Nut and Bolt Co IMPORTANT NOTES : In order to complete this assignment, you’ll have to enable macros. Once you are done with this assignment, you can disable all macros. ALSO, when you go to Canvas to retrieve the assignment, you have to save the document rather than simply opening it in order for the macro to work. Assignment: Collin Villalobos, CFO of CollinIke’s Nut and Bolt, has come to Bryan Renoir, (CollinIke’s Staff Accountant) to discuss accounts receivables and invoicing. CollinIke’s 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 client’s total account balance is more than 60 days past due. Additionally, over the weekend, CollinIke’s 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 company’s accounts receivable details as of March 31, 2018. Note: You must click on “Generate” in ExcelAssignment1.xlsm to generate CollinIke’s Nut and Bolt account balances. Also note that when you create the “if” and “lookup” functions, and when you create totals on the invoice described below, you must include the appropriate functions in the appropriate cells in your file. 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 . (See Exhibit 3.1) 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 read “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 similar to Exhibit 1.1 (see the next page). The invoice should include list drop-down menus. 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. When you save your assignment , please use the following format: LastNameFirstNameExcelAssignment1. For example, if I were to save the file I would save it as SimonChadExcelExcelAssignment1.

Exhibit 1.1 CollinIke’s Nut and Bolt Temporary Invoice CollinIke's Nut And Bolt Co Date: 4/13/2011 1 CollinIke Way Las Vegas, NV 89169 Bill To: Mott, Aiden Qty Item Unit Price Total 2 1/2 Steel Washer - 150 count 3.55 $ 7.10 $ 3 3/4 Plastic Nut - 50 count 2.47 7.41 4 3/4 Plastic Nut - 50 count 2.47 9.88 5 1/2 Aluminum Hex Bolt - 100 count 2.43 12.16 Amount Due: 36.54 $ Drop-down Menus

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version