Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Can you please help me solve this spreadsheet with the tennis balls and credit analysis. I think I solved the tennis balls tab, but would
Can you please help me solve this spreadsheet with the tennis balls and credit analysis. I think I solved the tennis balls tab, but would like it double checked. Thank you
Qty Unit Pricing Tennis Balls 0 60 120 240 Order# 1 2 3 4 5 6 Shipping Costs $/each $ $ $ $ Method Truck Rail Ship Customer 2.45 2.27 2.12 2.00 Cust ID Shipping A0243 MH394 R903 X271 N271 CD031 Rail Rail Ship Truck Truck Customer Quantity 240 30 160 215 90 15 Add formulas in cells highlighted in blue, and perform basic lookups to calculate & evaluate data Requirements: 1. Lookup "Unit Price", "Unit Shipping" &" Shipping Discount". 2. Calculate "Total Price", "Total Shipping" & "Grand Total" based on the information retrieved Tips: 1. Use correct range_lookup value (i.e. TRUE vs. FALSE) 2. Be careful with the reference formats (i.e. absolute, relative, or mixed). In other words, make s Pricing Information for Tennis Balls Shipping Costs $/each $ $ $ $ Shipping D 0.25 0.20 0.15 - Total Price: Shipping Discount Unit Price $ $ $ $ $ $ Total Price 2.00 2.45 2.12 2.12 2.27 2.45 $ $ $ $ $ $ 480.00 73.50 339.20 455.80 204.30 36.75 Unit Shipping $ $ $ $ $ Total Shipping 0.20 0.20 0.15 0.25 0.25 #N/A $ $ $ $ $ he information retrieved ed). In other words, make sure the reference is correct when using fill handle to automatically generate formulas. 48.00 6.00 24.00 53.75 22.50 #N/A Shipping Discount $ 0 $ 0% Shipping Discount $ $ $ $ $ (4.80) (2.40) (5.38) #N/A 300 $ 10% Grand Total $ $ $ $ $ 523.20 79.50 360.80 504.18 226.80 #N/A 1,000 15% Accounts Receivable Department - Customer Credit Analysis Customer Name Athletic Gear Corp. Current Credit Limit $ Previous Year's Sales 9,000 $ 15,382 $ Current Year's Sales 11,952 Baltimore O's 39,000 10,033 7,789 Baseball & More 75,000 60,009 55,342 Canadian Ski Club 33,000 35,039 50,921 Everything Golf 25,000 15,221 9,483 Lake Pro Shops 42,000 80,498 81,126 Mars Dept. Store 27,000 35,354 20,666 RG Bradley 46,000 90,970 18,343 RX for Sports 15,000 5,663 3,014 School Sports Supply 45,000 50,278 32,338 Ski World 26,000 25,864 28,154 Sneaker Kingdom 45,000 40,157 25,379 Sports & Stuff 15,000 15,898 14,732 Toy Kingdom 22,000 10,073 1,047 Under the Sea 45,000 95,411 64,418 US Olympic Team 20,000 5,621 6,171 WWW Sports Inc. 100,000 60,009 60,354 10,000 15,490 22,760 Concord Pro Shop Zip & Sons Add formulas in cells highlighted in blue Rule #1: Accept a customer that has a past due balance that is less than 10% of this year's total sales. Rule #2: Accept a customer that has either a composite credit appraisal value of 1or a PAYDEX score over 90. Rule #3: Accept a customer that has all of the following: a net worth of at least $500,000; a composite credit appraisal value For Final Recommendation (col N): 1. Automatically reject if none of the approval rules are met 2. Suggest a further evaluation if one of the following rules are met: i 3. Automatically accept if TRUE for Rule #1 and TRUE for Rule #2 an D&B Credit Rating Class D&B Composite Credit Appraisal (1 Best) 450 BA 4 0 1,950 3A 1 13,892 37,500 4A 2 495 1,650 BA 2 10,000 4A 1 2,899 1,250 3A 3 0 2,100 3A 2 0 213 BB 3 0 2,300 3A 1 0 750 2A 1 0 2,250 3A 3 0 300 BA 2 0 2,250 3A 2 14,383 450 BA 1 0 1,100 3A 3 0 150 CB 4 0 1,000 3A 1 0 500,000 5A 2 0 620 1A 2 Past Due Balance $ Net Worth in (000) 0 $ Do any meet the rule? Do none meet the rule? core over 90. osite credit appraisal value of 2 or lower; a PAYDEX score over 70; and a stress risk class of 1. val rules are met e following rules are met: i) TRUE for only Rule #1; ii) TRUE for only Rule #2 and/or Rule #3, and a FALSE value for Rule #1. 1 and TRUE for Rule #2 and/or Rule #3. D&B PAYDEX (100 Best) D&B Stress Risk Class (1 Best) 15 3 51 1 70 1 43 1 91 1 76 1 87 1 94 1 21 1 59 1 91 1 82 1 71 1 67 1 14 1 79 2 87 1 97 1 96 1 Do any meet the rule? Do none meet the rule? lue for Rule #1. Rule#1 Rule#2 Rule#3 Final RecommendationStep 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