how i can calculate these sheet please on excel

1. Use a formula to find the appropriate hire category in the above table      
2. Find basic rate for number of days hired            
3. Find weekend rate              
You may need to modify the lay-out of the spreadsheet          
4. Total Hire Charge: If the number of Days hired are equal to W the weekend rate will apply.    
    Otherwise – Total Hire Charge: If weekend rate does not apply then total charge is basic rate     
5. There is an insurance charge of 5% of of any Excess over €25 on the Hire Charge. This insurance only applies 
to Week-end rates.              
6. There is a Delivery Charge  based on the distance of the customer from the Hire Co. as follows:    
IF the Distance is:              
    < 20 Km Rate is €2.0 per Km.        
    20-30 Km Rate is €1.20 per Km.        
    > 30 Km Rate is 80C per KM over 30 Km and 75C for each Km up to 30 Km
This Delivery charge does not apply to Category A  or Category C items      
7. VAT is Charged at 21% on The Hire Charge+Delivery Charge and Insurance      
8: The shaded area contains formulae             
9: Save the Formulae to another sheet            
10: Enhance the Layout as you see fit            
11: Produce an appropriate graph.            
Customer No Customer Name Item Hire Category Days Hired Basic Rate Weekend Rate Hire Charge Delivery Charge Insurance Charge VAT Total
11   Mixer   1              
12   Digger   3              
14   Drill   W              
17   Mixer   5              
19   Chain Saw   7              
15   Hammer   2              
13   Drill   6              
18   Drill   4              
20   Tile Cutter   W              
17   Lawn Mower   8              
Category       Chain Saw C
A 17.50 38.00   Hammer D
B 12.50 24.00   Drill B
C 17.50 28.50   Mixer D
D 7.00 14.50   Digger B
E 17.50 25.50   Tile Cutter A
Vat rate 21%          
  Category   Cust. No. Customer Details Distance KM
Chain Saw C   11 Dunnes 15  
Hammer D   12 Frescos 25  
Drill B   13 Marks and Sparks 14  
Mixer D   14 Quinns 16  
Digger B   15 O’Briens 24  
Tile Cutter A   16 Duffs 25  
Lawn Mower C   17 Keanes 27  
      18 Carlo Ponti 12  
      19 Sven Stonson 10  
      20 Browns 27  
      Lawn Mower C

By: noor

Leave a Reply

Your email address will not be published. Required fields are marked *