Elex - formation rapport et tableau excel

Introduction

This tutorial provides a step-by-step guide on how to create and format reports in Excel, specifically tailored for use in Elex. It covers essential tasks such as converting units, modifying tables, and integrating data into reports. Whether you are a beginner or looking to refine your skills, this guide will help you streamline your workflow in Excel.

Step 1: Prepare Your Report Layout

• Display Gridlines: Ensure gridlines are visible for clarity.
• Remove Extra Pages: Delete any unnecessary second pages from your report.
• Convert Kilowatts to Horsepower:
  • Use the conversion factor: 1 kW = 1.34102 HP.
  • For example, to convert 5 kW, multiply:

    5 kW * 1.34102 = 6.7051 HP 
    

• Add Signature: Include the signature of Cédric DUSSERRE on the last page for authenticity.

Step 2: Working with Excel Tables

• Familiarize Yourself: Get comfortable navigating Excel and its features.
• Add Letter Lists:
  • Create a list in the format A/, B/, C/, etc., to categorize data easily.

Step 3: Fill Out the Verification Table

• Input Data: Enter necessary verification data into the designated table.
• Check for Accuracy: Ensure all entries are correct to avoid errors later.

Step 4: Pointing Out Cost Elements

• Identify Key Costs: Clearly mark which items are associated with costs in your report.

Step 5: Enter Positions in the Table

• Input Positions: Fill in various job positions related to the project in the table.
• Common Omissions: Pay special attention to frequently overlooked positions to ensure completeness.

Step 6: Adding and Removing Rows

• Insert Rows: Add extra rows as needed to accommodate additional data.
• Delete Unused Rows: Remove any unnecessary rows at the end of your cost calculation for a cleaner look.

Step 7: Convert Total Amounts

• Convert TTC to HT:
  • To convert from Total Tax Included (TTC) to Total Tax Excluded (HT), use:

    Amount HT = Amount TTC / (1 + VAT rate)
    

  • For example, if the VAT rate is 20%:

    Amount HT = Amount TTC / 1.2
    

Step 8: Handling VAT Exceptions

• Exclude VAT from Specific Items: Identify and mark items that should not have VAT applied to them.

Step 9: Table Management

• Delete Unnecessary Sections: Remove entire chapters that are not required.
• Hide Columns: Conceal columns that may clutter the report, such as those for depreciation.

Step 10: Reinsert Excel Tables into Word Reports

• Integrate with Word: Copy and paste your completed Excel table back into your Word report seamlessly.

Step 11: Splitting Tables

• Divide Between Chapters: If needed, split the table into two distinct sections to align with chapter breaks.
• Split Within Chapters: You can also split a table in the middle of a chapter to enhance readability.

Step 12: Input Percentage for Non-Tracked Positions

• Enter Percentages: For positions not included in the verification table, ensure to enter their respective percentages accurately.

Conclusion

In this tutorial, we've covered essential steps to create and manage reports in Excel for Elex. From preparing your layout to managing tables and ensuring data accuracy, these skills will enhance your efficiency in report preparation. Next, consider practicing these techniques on actual projects to solidify your understanding and improve your proficiency.