account 2301
Mainu
ThisisyourthirdExcelProjectassignmentexcel3all.docxThis is your third Excel Project assignment. For those of you who have not looked at the Beginner's Guide to Excel Module, I suggest you review the information. There are also several helpful videos located on Youtube that will get you up to speed on Excel and its features. This is the first assignment where you will be able to use Excel Functions to fill in the cells. Within the Beginner's Guide to Excel Module there are several videos on how to properly use these functions. It is possible to complete the assignment without the use of these functions, but it will take significantly more time to complete.
Please download the Excel and Word Doc file below. You will complete the worksheet based on the instructions provided within the Excel/Word File. Be mindful that there is more than one tab located within the excel file. The first tab contains instruction on what formulas to use and where to use them when populating the second tab.
Unit 3 - Excel Project Download Unit 3 - Excel Project
Unit 3 - Additional Info Download Unit 3 - Additional Info
Once complete, please reupload the excel file back to this assignment location prior to the due date. When received, I will work on manually grading your submissions. Your patience during this process is greatly appreciated.
Please feel free to reach out with any questions. I know some of you have no background in excel and that these assignments will be difficult. Microsoft Excel is universally used in all business industries, which is why I find it important for students to develop basic knowledge of excel.
Instruction
|
Receivables |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Using Excel for Aging Accounts Receivable |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The Lake Lucerne Company uses the allowance method of estimating bad debts expense. An aging schedule is prepared in order to calculate the balance in the allowance account. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The percentage uncollectible is calculated as follows: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1-30 Days |
1% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
31-60 Days |
2% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
61-90 Days |
5% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
91-365 Days |
50% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
After 365 days, the account is written off. |
|
|
|
|
|
|
|
|
|
|
|
|
|
Use the blue shaded areas on the ENTER-ANSWERS tab for inputs. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Requirements |
|
|
|
|
|
|
Possible Points |
|
|
|
|
|
|
|
|
1 |
Calculate the number of days each receivable is outstanding. |
|
|
|
|
|
12 |
|
|
|
|
|
|
|
|
2 |
Complete the Schedule of Accounts Receivable. |
|
|
|
|
|
45 |
|
|
|
|
|
|
|
|
|
*For all requirements, enter all amounts as positive values. Do not use a minus sign or parentheses for any values. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Excel Skills |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
Use the DAYS function to calculate the number of days between the invoice date and the date of the aging schedule. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
Use the VLOOKUP function to provide the uncollectible accounts percentage based on the days outstanding. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
Use IF and AND functions to determine in which column of the Accounts Receivable Schedule to place the amount for each customer. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4 |
Format cells using number and percentage formats. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5 |
Use formulas to calculate totals. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
Use the Increase Indent button to indent the credit account for the journal entry. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
Use Absolute references to refer to a table in the VLOOKUP formula. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Excel Tips |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DAYS function |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
End Date |
Start Date |
Formula returns the number of days between the two dates |
Formula =DAYS(enddate, startdate) |
|
|
|
|
|
|
|
|
|
|
|
|
3/1/18 |
1/15/18 |
45 |
=DAYS(C36,D36) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
VLOOKUP function |
|
|
Given a table with amounts and uncollectible percentages, you can use VLOOKUP to determine the uncollectible rate for a specific number. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table Array |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Column 1 |
Column 2 |
|
Lookup Value |
Formula returns the uncollectible rate in column 2 |
Formula |
Formula Evaluation: |
|
|
|
|
|
|
|
|
|
Data |
Uncollectible % |
|
1,245 |
5% |
=VLOOKUP(F44,$C$45:$D$48,2) |
Look up the value of Cell F44 in the range of Cells from C45 to D48. Once you find the value that is no larger than F44, continue in the same row until you get to the second column in the range. Return the value in the second column. The last number used in the formula (2) refers to the column in the table where the answer comes from. Since the uncollectible percent is found in Column 2 of the table, 2 is used in the example formula. If the uncollectible percent would be found in Column 3 of the table used, 3 would be used in the formula instead of 2. |
||||||
|
|
|
0 |
0% |
|
3,600 |
12% |
=VLOOKUP(F45,$C$45:$D$48,2) |
|
||||||
|
|
|
1000 |
5% |
|
150 |
0% |
=VLOOKUP(F46,$C$45:$D$48,2) |
|
||||||
|
|
|
2000 |
10% |
|
|
|
|
|
||||||
|
|
|
3000 |
12% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Hint: Use the F4 key to lock in the absolute cell reference. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Amount |
First Value |
Second Value |
Formula Returns: |
Formula |
Formula evaluation |
|
|
|
|
|
|
|
|
|
IF function |
$1,700 |
1,500 |
2,500 |
$1,700 |
=IF(D52>E52, D52, "") |
If the value in Cell D52 is greater than the value in Cell E52, display the value in D52, otherwise display nothing. The double quotes enclose nothing between them. |
|
|||||
|
|
|
|
$250 |
1,500 |
2,500 |
|
=IF(D53>E53, D53, "") |
If the value in Cell D53 is greater than the value in Cell E53, display the value in D53, otherwise display nothing. The double quotes enclose nothing between them. Nothing is displayed because $250 is not greater than $1,500. |
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Formula |
|
|
|
|
|
|
|
|
|
|
|
|
|
Formula Returns: |
|
31-60 Days |
61-90 Days |
|
|
|
|
|
|
|
|
|
|
Amount |
Days |
31-60 Days |
61-90 Days |
|
|
|
|
|
|
|
|
|
|
|
IF and AND functions |
$500 |
35 |
500 |
|
=IF(AND(E58>=31, E58<=60), D58,"") |
=IF(AND(E58>=61, E58<=90), D58,"") |
|
|
|
|
|
|
|
|
|
|
$600 |
67 |
|
600 |
=IF(AND(E59>=31, E59<=60), D59,"") |
=IF(AND(E59>=61, E59<=90), D59,"") |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Formula evaluation |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
IF (E58>=31 and E58<=60), display D58, otherwise display nothing. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
IF (E58>=61 and E58<=90), display D58, otherwise, display nothing. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
IF (E59>=31 and E59<=60), display D59, otherwise display nothing. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
IF (E59>=61 and E59<=90), display D59, otherwise, display nothing. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Saving & Submitting Solution |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
Save file to desktop. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
a. |
Create folder on desktop, and label COMPLETED EXCEL PROJECTS |
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
Upload and submit your file to Canvas to be graded. |
|
|
|
|
|
|
|
|
|
|
|
|
|
DATA
|
|
Accounts |
|
|
|
|
|
Allowance for Bad Debts |
|
|
|
|
|
Bad Debts Expense |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Enteranswer
|
|
Use the following table for your calculations. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Minimum Days |
Maximum Days |
% Uncollectible |
|
|
|
|
|
1 |
30 |
1% |
|
|
|
|
|
31 |
60 |
2% |
|
|
|
|
|
61 |
90 |
5% |
|
|
|
|
|
91 |
365 |
50% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Requirement 1 |
|
|
|
|
|
|
|
Calculate the number of days each receivable is outstanding. (See hints below.) |
|
|
|
|
|
|
|
(Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) |
|||||
|
|
|
|
|
|
|
|
|
|
DATA |
|||||
|
|
Customer Name |
Amount |
Today's Date |
Invoice Date |
Days Outstanding |
Uncollectible % |
|
|
Bonneville |
$ 900 |
7/31/18 |
6/28/2018 |
|
|
|
|
Chinook |
$ 1,100 |
7/31/18 |
7/28/2018 |
|
|
|
|
Duwamish |
$ 500 |
7/31/18 |
5/15/2018 |
|
|
|
|
Foss |
$ 350 |
7/31/18 |
6/5/2018 |
|
|
|
|
Nisqually |
$ 3,245 |
7/31/18 |
7/15/2018 |
|
|
|
|
Worden |
$ 250 |
7/31/18 |
4/12/2018 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Requirement 2 |
|
|
|
|
|
|
|
Complete the Schedule of Accounts Receivable. (See hints below. Use =SUM() for totals. Complete all blue highlighted cells.) |
|
|
|
|
|
|
|
(Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) |
|||||
|
|
|
|
|
|
|
|
|
|
Schedule of Accounts Receivable |
|||||
|
|
Customer Name |
1-30 Days |
31-60 Days |
61-90 Days |
91-365 Days |
Total Balance |
|
|
Bonneville |
|
|
|
|
|
|
|
Chinook |
|
|
|
|
|
|
|
Duwamish |
|
|
|
|
|
|
|
Foss |
|
|
|
|
|
|
|
Nisqually |
|
|
|
|
|
|
|
Worden |
|
|
|
|
|
|
|
Totals |
|
|
|
|
|
|
|
Estimated % uncollectible |
|
|
|
|
|
|
|
Estimated total uncollectible |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
HINTS |
||||||
|
Cell | Hint: |
|
|||||
|
F16:G21 | Use the functions =DAYS( ) and =VLOOKUP() to calculate the days outstanding and corresponding uncollectible % values. Do not enter any value for the RANGE_LOOKUP argument of the VLOOKUP function. |
||||||
|
G16:G21 | Use absolute cell references for the TABLE_ARRAY argument of the VLOOKUP() function. (Absolute cell reference uses $, example $A$1. Use the F4 key to lock in the absolute cell reference.) |
||||||
|
C30:F35 | Use the functions =IF( ) and =AND() to calculate the amounts of accounts receivable for each customer for each period, if any. Follow the examples provided in cells C51:I58 in the Instructions tab. Different logic will not be graded as correct one. |
||||||
|
G30:G36, C36:G36 | Use the function =SUM( ) to calculate the Totals and Total Balance values in the Schedule of Accounts Receivable. |
||||||
|
C37:F37 | Refer to the data table in cells C4:E7 for the corresponding values of the Estimated % uncollectible. |
||||||
|
G37 | Leave this cell blank. |
||||||
|
**When entering account names, click the arrow on the right side of the cell to select the appropriate account. |
|
|
|
|
|
|
