EXCEL ASSIGNMENT #4

profileGJbratt
Exp19_Excel_Ch07_CapAssessment_Shipping.xlsx
Home>Information Systems homework help>EXCEL ASSIGNMENT #4

Week

Input Area Cities Shipping
Order Total Threshold $ 1,000 Austin $ 24
Total Days Delivery Goal 10 Dallas-Forth Worth $ 20
Shipping Refund Rate 50% Houston $ 30
Date Ordered Date Shipped Date Arrived Total Days Airport Code Shipped From Order Total Shipping Cost Shipping Refund
4/5/21 4/6/21 4/9/21 AUS $ 975
4/5/21 4/7/21 4/15/21 AUS $ 1,055
4/5/21 4/7/21 4/16/21 IAH $ 1,075
4/5/21 4/7/21 4/12/21 DFW $ 1,000
4/5/21 4/9/21 4/16/21 IAH $ 2,535
4/5/21 4/5/21 4/12/21 DFW $ 1,890
4/5/21 4/8/21 4/16/21 AUS $ 950
4/6/21 4/8/21 4/15/21 DFW $ 1,485
4/6/21 4/6/21 4/9/21 DFW $ 550
4/6/21 4/8/21 4/19/21 AUS $ 1,250
4/6/21 4/8/21 4/14/21 IAH $ 1,600
4/6/21 4/9/21 4/19/21 DFW $ 2,500
4/7/21 4/12/21 4/16/21 IAH $ 1,425
4/7/21 4/9/21 4/16/21 AUS $ 800
4/7/21 4/9/21 4/14/21 DFW $ 1,000
4/7/21 4/24/21 4/30/21 DFW $ 1,125
4/7/21 4/12/21 4/16/21 IAH $ 1,625
4/8/21 4/9/21 4/14/21 IAH $ 1,345
4/8/21 4/9/21 4/16/21 DFW $ 2,100
4/8/21 4/8/21 4/12/21 DFW $ 1,475
4/8/21 4/9/21 4/13/21 IAH $ 1,825
4/8/21 4/12/21 4/16/21 AUS $ 2,055
4/8/21 4/13/21 4/19/21 IAH $ 1,625
4/8/21 4/12/21 4/19/21 DFW $ 1,000
4/9/21 4/12/21 4/19/21 AUS $ 1,005
4/9/21 4/12/21 4/16/21 IAH $ 1,745
4/9/21 4/12/21 4/16/21 DFW $ 2,100
4/9/21 4/9/21 4/20/21 AUS $ 1,005
4/9/21 4/9/21 4/20/21 IAH $ 960

Stats

Statistics AUS DFW IAH
# of Shipments
Total Order Value
Average # of Days
Houston (IAH) Orders Over $1,000
Number of Orders Shipped
Total of Orders Shipped
Highest Order Value
Date Ordered Total Days Airport Code Order Total
4/5/21 4 AUS $ 975
4/5/21 10 AUS $ 1,055
4/5/21 11 IAH $ 1,075
4/5/21 7 DFW $ 1,000
4/5/21 11 IAH $ 2,535
4/5/21 7 DFW $ 1,890
4/5/21 11 AUS $ 950
4/6/21 9 DFW $ 1,485
4/6/21 3 DFW $ 550
4/6/21 13 AUS $ 1,250
4/6/21 8 IAH $ 1,600
4/6/21 13 DFW $ 2,500
4/7/21 9 IAH $ 1,425
4/7/21 9 AUS $ 800
4/7/21 7 DFW $ 1,000
4/7/21 23 DFW $ 1,125
4/7/21 9 IAH $ 1,625
4/8/21 6 IAH $ 1,345
4/8/21 8 DFW $ 2,100
4/8/21 4 DFW $ 1,475
4/8/21 5 IAH $ 1,825
4/8/21 8 AUS $ 2,055
4/8/21 11 IAH $ 1,625
4/8/21 11 DFW $ 1,000
4/9/21 10 AUS $ 1,005
4/9/21 7 IAH $ 1,745
4/9/21 7 DFW $ 2,100
4/9/21 11 AUS $ 1,005
4/9/21 11 IAH $ 960

Map

State Revenue
California $ 55,945
Colorado $ 30,650
Texas $ 41,080

Loan

Loan Information Payment # Beg Balance Interest Principal End Balance
Loan (PV) $ 75,000.00 1 $ 75,000.00
APR 5.00% 2 $ - 0
Monthly Rate 0.42% 3 $ - 0
Years for Loan 6 4 $ - 0
NPER 72 5 $ - 0
Monthly Payment $1,207.87 6 $ - 0
7 $ - 0
Cumulative Totals after Year 2 8 $ - 0
Interest 9 $ - 0
Principal Paid 10 $ - 0
11 $ - 0
What if the goal is $1,150 monthly payment? 12 $ - 0
Monthly Payment $ 1,150.00 13 $ - 0
Monthly Rate Needed 14 $ - 0
APR Needed 15 $ - 0
16 $ - 0
17 $ - 0
18 $ - 0
19 $ - 0
20 $ - 0
21 $ - 0
22 $ - 0
23 $ - 0
24 $ - 0