Spread Sheet Excel

profilegloo1989
CH04Level2Figures.xlsx
Home>Business & Finance homework help>Accounting homework help>Spread Sheet Excel

CreditData

Accounts Receivable Department - Customer Credit Analysis
Customer Name Current Credit Limit Previous Year's Sales Current Year's Sales Past Due Balance Net Worth in (000) D&B Credit Rating Class D&B Composite Credit Appraisal (1 Best) D&B PAYDEX (100 Best) D&B Stress Risk Class (1 Best) Rule#1 Rule#2 Rule#3
Athletic Gear Corp. $ 9,000 $ 15,382 $ 11,952 $ 0 $ 450 BA 4 15 3 TRUE FALSE FALSE
Baltimore O's 39,000 10,033 7,789 - 1,950 3A 1 51 1 TRUE TRUE FALSE
Baseball & More 75,000 60,009 55,342 13,892 37,500 4A 2 70 1 FALSE FALSE FALSE
Canadian Ski Club 33,000 35,039 50,921 495 1,650 BA 2 43 1 TRUE FALSE FALSE
Concord Pro Shop 10,000 4A 1 91 1 TRUE TRUE
Everything Golf 25,000 15,221 9,483 2,899 1,250 3A 3 76 1 FALSE FALSE FALSE
Lake Pro Shops 42,000 80,498 81,126 - 2,100 3A 2 87 1 TRUE FALSE TRUE
Mars Dept. Store 27,000 35,354 20,666 - 213 BB 3 94 1 TRUE TRUE FALSE
RG Bradley 46,000 90,970 18,343 - 2,300 3A 1 21 1 TRUE TRUE FALSE
RX for Sports 15,000 5,663 3,014 - 750 2A 1 59 1 TRUE TRUE FALSE
School Sports Supply 45,000 50,278 32,338 - 2,250 3A 3 91 1 TRUE TRUE FALSE
Ski World 26,000 25,864 28,154 - 300 BA 2 82 1 TRUE FALSE FALSE
Sneaker Kingdom 45,000 40,157 25,379 - 2,250 3A 2 71 1 TRUE FALSE TRUE
Sports & Stuff 15,000 15,898 14,732 14,383 450 BA 1 67 1 FALSE TRUE FALSE
Toy Kingdom 22,000 10,073 1,047 - 1,100 3A 3 14 1 TRUE FALSE FALSE
Under the Sea 45,000 95,411 64,418 - 150 CB 4 79 2 TRUE FALSE FALSE
US Olympic Team 20,000 5,621 6,171 - 1,000 3A 1 87 1 TRUE TRUE TRUE
WWW Sports Inc. 100,000 60,009 60,354 - 500,000 5A 2 97 1 TRUE TRUE TRUE
Zip & Sons 10,000 15,490 22,760 - 620 1A 2 96 1 TRUE TRUE TRUE
Do any meet the rule? TRUE TRUE TRUE
Do none meet the rule? FALSE FALSE FALSE

Status

TheZone Customer Accounts - Credit & Payment Status
Customer Name Customer Type Current Balance Due 30-Days Past Due 60-Days Past Due 90-Days Past Due Total Past Due Balance Current Credit Status
Athletic Gear Corp. A $ 8,612 $ 0 $ 0 $ 0 $ 0 TRUE
Baltimore O's B 0 0 0 0 0 TRUE
Baseball & More A 2,345 3,473 5,557 4,862 13,892 TRUE
Canadian Ski Club C 0 345 0 150 495 TRUE
Everything Golf A 0 0 2,000 899 2,899 FALSE
Sports & Stuff A 0 14,000 383 0 14,383 FALSE

Figure4.25

TheZone Customer Accounts - Credit & Payment Status
Customer Name Customer Type Current Balance Due 30-Days Past Due 60-Days Past Due 90-Days Past Due Total Past Due Balance Current Credit Status
Athletic Gear Corp. A $ 8,612 $ 0 $ 0 $ 0 $ 0 TRUE
Baltimore O's B 0 0 0 0 0 TRUE
Baseball & More A 2,345 3,473 5,557 4,862 13,892 TRUE
Canadian Ski Club C 0 345 0 150 495 TRUE
Everything Golf A 0 0 2,000 899 2,899 FALSE
Sports & Stuff A 0 14,000 383 0 14,383 FALSE

Figure4.26

TheZone Customer Accounts - Credit & Payment Status
Customer Name Customer Type Current Balance Due 30-Days Past Due 60-Days Past Due 90-Days Past Due Total Past Due Balance Current Credit Status Current Credit Status Detail
Athletic Gear Corp. A $ 8,612 $ 0 $ 0 $ 0 $ 0 TRUE credit approved
Baltimore O's B 0 0 0 0 0 TRUE credit approved
Baseball & More A 2,345 3,473 5,557 4,862 13,892 TRUE credit approved
Canadian Ski Club C 0 345 0 150 495 TRUE credit approved
Everything Golf A 0 0 2,000 899 2,899 FALSE credit denied
Sports & Stuff A 0 14,000 383 0 14,383 FALSE credit denied

Figure4.28

Data Inputs:
Base Penalty Fee $ 25
TheZone Customer Accounts - Credit & Payment Status
Customer Name Customer Type Current Balance Due 30-Days Past Due 60-Days Past Due 90-Days Past Due Total Past Due Balance Current Credit Status Current Credit Status Detail Total Balance Including Finance Charge
Athletic Gear Corp. A $ 8,612 $ 0 $ 0 $ 0 $ 0 TRUE credit approved $ 8,612
Baltimore O's B 0 0 0 0 0 TRUE credit approved 0
Baseball & More A 2,345 3,473 5,557 4,862 13,892 TRUE credit approved 16,262
Canadian Ski Club C 0 345 0 150 495 TRUE credit approved 520
Everything Golf A 0 0 2,000 899 2,899 FALSE credit denied 2,924
Sports & Stuff A 0 14,000 383 0 14,383 FALSE credit denied 14,408

Figure4.29

Data Inputs:
Base Penalty Fee $ 25
Minimum 90-Day Balance for Surcharge $ 200
Minimum Surcharge $ 100
Surcharge Percentage 10%
TheZone Customer Accounts - Credit & Payment Status
Customer Name Customer Type Current Balance Due 30-Days Past Due 60-Days Past Due 90-Days Past Due Total Past Due Balance Current Credit Status Current Credit Status Detail Total Balance Including Finance Charge
Athletic Gear Corp. A $ 8,612 $ 0 $ 0 $ 0 $ 0 TRUE credit approved $ 8,612
Baltimore O's B 0 0 0 0 0 TRUE credit approved 0
Baseball & More A 2,345 3,473 5,557 4,862 13,892 TRUE credit approved 16,262
Canadian Ski Club C 0 345 0 150 495 TRUE credit approved 520
Everything Golf A 0 0 2,000 899 2,899 FALSE credit denied 2,924
Sports & Stuff A 0 14,000 383 0 14,383 FALSE credit denied 14,408

Figure4.31

Data Inputs:
Base Penalty Fee $ 25
Minimum 90-Day Balance for Surcharge $ 200
Minimum Surcharge $ 100
Surcharge Percentage 10%
TheZone Customer Accounts - Credit & Payment Status
Customer Name Customer Type Current Balance Due 30-Days Past Due 60-Days Past Due 90-Days Past Due Total Past Due Balance Current Credit Status Current Credit Status Detail Total Balance Including Finance Charge 90-Days Past Due Surcharge
Athletic Gear Corp. A $ 8,612 $ 0 $ 0 $ 0 $ 0 TRUE credit approved $ 8,612 $ 0
Baltimore O's B 0 0 0 0 0 TRUE credit approved 0 0
Baseball & More A 2,345 3,473 5,557 4,862 13,892 TRUE credit approved 16,262 486
Canadian Ski Club C 0 345 0 150 495 TRUE credit approved 520 0
Everything Golf A 0 0 2,000 899 2,899 FALSE credit denied 2,924 100
Sports & Stuff A 0 14,000 383 0 14,383 FALSE credit denied 14,408 0

Figure4.33

Data Inputs: Alternate Scheme Data Inputs:
Base Penalty Fee $ 25 90-Day Past Due penalty $ 100
Minimum 90-Day Balance for Surcharge $ 200 60-Day Past Due penalty $ 50
Minimum Surcharge $ 100 30-Day Past Due penalty $ 25
Surcharge Percentage 10%
TheZone Customer Accounts - Credit & Payment Status
Customer Name Customer Type Current Balance Due 30-Days Past Due 60-Days Past Due 90-Days Past Due Total Past Due Balance Current Credit Status Current Credit Status Detail Total Balance Including Finance Charge 90-Days Past Due Surcharge Alternate Penalty Scheme
Athletic Gear Corp. A $ 8,612 $ 0 $ 0 $ 0 $ 0 TRUE credit approved $ 8,612 $ 0 $ 0
Baltimore O's B 0 0 0 0 0 TRUE credit approved 0 0 0
Baseball & More A 2,345 3,473 5,557 4,862 13,892 TRUE credit approved 16,262 486 100
Canadian Ski Club C 0 345 0 150 495 TRUE credit approved 520 0 100
Everything Golf A 0 0 2,000 899 2,899 FALSE credit denied 2,924 100 100
Sports & Stuff A 0 14,000 383 0 14,383 FALSE credit denied 14,408 0 50

1

2

3

4

5

6

A

B

Customer Name

Customer

Type

Athletic Gear Corp.

A

Baltimore O's

B

Baseball & More

A

Canadian Ski Club

C

TheZone Customer Accounts - Credit & Payment Status