Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Filled out please Miller-Nobles (6e/12e) - Using Excel Ch08/Ch09 v2 Description: ) - V2 created o Perform: Instructions P Download and open the excel spreadsheet,
Filled out please
Miller-Nobles (6e/12e) - Using Excel Ch08/Ch09 v2 Description: ) - V2 created o Perform: Instructions P Download and open the excel spreadsheet, Miller-Nobles_6e-12e-Using Excel Chos- Ch09_Start.xlsx. This spreadsheet includes 2 tabs, you will enter all answers on the second tab, ENTERANSWERS 2 (All answers should be entered in the blue highlighted cells.) 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 table for calculation of the percentage uncollectible is provided After 365 days, the account is written off Calculate the number of days each receivable is outstanding (12 pts) (Always use cell references and formulas where appropriate to receive full credit. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values.) Complete the Schedule of Accounts Receivable (45 pts) (Always use cell references and formulas where appropriate to receive full credit. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values ) 4 Journalize the adjusting entry for Bad Debts Expense (4 pts) (Always use cell references and formulas where appropriate to receive full credit Enter all amounts as positive values. Do not use a minus sign or parentheses for any values ) Once completed, save the project to the desktop File name can be anything, but DO NOT include spaces. Return to auto graded excel activity window, and progress to Step 3. Follow on screen instructions 5 0 (To review score, navigate to Results in the Mylab and click on Review link.) Total Points 61 C D E F H K G Formula returns the uncollectible rate in column 2 13 Column 1 Column 2 Lookup Value Formula Data 1,245 3,600 150 Uncollectible % 0 0% 1000 5% 2000 10% 3000 12% Formula Evaluation: Look up the value of Cell F44 in the range of Cells from C45 to D than F44, continue in the same row until you get to the secondo second column. The last number used in the formula (2) refers to comes from. Since the uncollectible percent is found in Column formula. If the uncollectible percent would be found in Column formula instead of 2. 5% =VLOOKUP(F44,$C$45:$D$48,2) 12% =VLOOKUP(F45,$C$45:$D$48,2) 0% =VLOOKUP(F46,$C$45:$D$48,2) 44 45 46 47 48 49 50 51 Hint: Use the F4 key to lock in the absolute cell reference. Second Value Formula Amount Formula Returns: Formula evaluation First Value $1,700 52 IF function 1,500 2,500 1700-IF(D52>E52, D52,"") If the value in Cell D52 is greater than the value in Cell E52, disple display nothing. The double quotes enclose nothing between the If the value in Cell D53 is greater than the value in Cell E53, displa display nothing. The double quotes enclose nothing between the $250 is not greater than $1,500. $250 1,500 2,500 =IF(D53>E53, D53,"") 61-90 Days 53 54 55 56 57 58 IF and AND functions 159 60 Days Formula Returns: 31-60 Days 500 Formula 31-60 Days 61-90 Days =IF(AND(E58>=31, E58=31, E59=61, E58=61, E59=31 and E58=61 and E58=31 and E59=61 and E59E52, D52,") Formula evaluation + 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. 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. =IF(D53>E53, D53,"") Formula 31-60 Days 61-90 Days ays =IF(AND(E58>=31, E58=31, E59=61, E58=61, E59=31 and E58=61 and E58=-31 and E59=61 and E59
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started