Microsoft Excel Projects

Here is an example of the attendance sheet and interval staffing sheet that I used to send reports. I also used information from the schedule file to send reports

This Excel file was created using Microsoft Excel. Kindly view it and download it by clicking this link  https://1drv.ms/x/s!AroJMieUeuj5frdXH5sEmBI94Cg?e=86OsIU

In the January Month Attendance sheet

In the attendance workbook, on the ‘January Month Attendance’ sheet, Conditional Formatting is used to change the words: ‘Present’, ‘Off’, ‘Late’, ‘Absent’, ‘Vacation Leave’, ‘Medical Leave’, and ‘Termed’ into different colours, and the background is also changed into different colours.

The COUNTIF function is used to calculate the Total Late, Total Absent, Total Vacation Leave, Total Medical Leave, and Total No Pay Leave.

Additionally, the COUNTIF function is used to calculate the number of persons late to work per day (tardiness), the number of persons termed per day (attrition), the number of persons absent per day, the number of persons on medical leave per day, the number of persons on vacation leave per day, and the number of persons on no pay leave per day.”

In the Interval staffing sheet

To send the hourly report, I check the requirements for that hour, which is from 23:00 to 00:00. I look at the number of people scheduled for that hour, the number of people actually working during that hour, and the number of people who are on Vacation Leave, Medical Leave, No Pay Leave, or Absent. I also check for any system issues during that hour.

After that, I enter the information in the table, take a screenshot and send it by email.

Here is an example of the schedule file that I used to send reports and manage break and lunch schedule.

In the Schedule workbook, Conditional Formatting is used to change the words Off and the background into red.

This Excel file was created using Microsoft Excel. Kindly view it and download it by clicking this link https://1drv.ms/x/s!AroJMieUeuj5gQEuSAorV4jbVXFm?e=acvU7D

Here are excel projects that I did to demonstrate somethings I can do in Excel.

Kindly view the excel file and download it by clicking this link https://1drv.ms/x/s!AroJMieUeuj5gSt6OMHJZbrYA5cr?e=RjLwql

Here are detail explanations of each sheet
In the Sales sheet, for the Sam fruits business sales in the month of January 2024

The SUM function is used to calculate the total sales that each salesperson made for the month January 2024 and the total sales for department.

The AVERAGE function is used to calculate the average sales for the department.

The IF function is used to determine if each salesperson met the goal of $35,000 and if each salesperson will get a 10% bonus.

The IF function and the AND function is used to determine if everyone will get a department bonus, or no one will get the department bonus. In order for everyone to get the department bonus, the Department as a whole must have total sales of at least $210,000 and an average sale of $10,000 or more per week.

In the Sales sheet, for the Tom Peanut business sales in the month of January 2024

For the Tom Peanut business sales, after each employee sales are entered in the sales per day, In the bonus column, the IF function and the VLOOKUP function are used to determine whether the employees get a Regular Bonus or Holiday Bonus.

From the dropdown menu, under Regular or Holiday Bonus?,  select regular, if the employees made sales on a regular pay day so that they will get the regular bonus. However, select holiday, if the employees made sales on a holiday so that they will get the holiday bonus.

In the Charts sheet

The Charts sheet shows a 2D and 3D column chart for the Sam fruits business sales.

In the Records sheet

In the Records sheet, there is a table with all the employees’ information. The VLOOKUP function is used in the Employee Lookup selection to find the employee’s last name, first name, department, and pay rate per hour.

However, if an employee ID that is not listed in the employee information table is entered, the IFERROR function is used, resulting in the display of ‘No Employee exists’.

The employee information table header includes filters for easy sorting by any column in the table. Additionally, there are location and department slicers, which are used to filter the location and department columns.

In the inventory sheet

In the inventory sheet, there is an Inventory table that contains all the information about the products that Jerry Company sells, as well as the quantity of these products available at different stores.

In the Inventory Status Check section, the HLOOKUP function is used to determine the quantity of each product available at each store. However, if you enter a product that is not listed in the ‘Inventory’ table, the IFERROR function is used to display ‘No Product Exists’.

The TODAY function is used to determine the current date. In the ‘Bills Information’ table, a formula is used to calculate the number of days overdue. Additionally, formulas are used to calculate the number of days and years since the company started.

In the SparkLines sheet

To demonstrate linking cells in the same sheet, cell K3, which contains the words ‘Using SparkLines’, is linked to cells L3 and M3.

In columns K, L, and M, different types of sparklines are displayed.  A sparkline is a tiny chart in a cell that provides a visual representation of data. In this example, Sparklines are inserted into cells to provide a visual representation of sales from June to December. They are used to show trends in a series of values, such as seasonal increases or decreases, to highlight maximum and minimum values, etc.

In column C, the CONCATENATE function is used to join the first name and last name together.

To demonstrate linking data from another sheet, the last and first names in the Sparkline sheet are linked to the employee’s last and first names from the sales team department in the Records sheet.

In the Q1 sheet

In the Q1 Expenses sheet, the SUMIFS function is used to calculate the total expenses for each category in each building. The SUMIF function is used to calculate the total expenses for each category. The AVERAGEIF function is used to calculate the average expenses for each category.

In the Soft drinks sheet

In the Soft Drinks sheet, the ‘Subtotal’ function from the ‘Data’ tab is used to calculate the sum of the total sales, margin, and quantity for both Orange Soda and Cream Soda. This information is displayed in three different views, which the user can select from the left-hand side of the worksheet.

  1. The first view is the Grand Total view, which displays the sum of all sales, margins, and quantities.
  2. The second view shows the Grand Total, as well as the sum of the total sales, margin, and quantity for Orange Soda and Cream Soda separately.
  3. The third view presents a table with all the information, the Grand Total, and the sum of the total sales, margin, and quantity for Orange Soda and Cream Soda separately.
In the Data Validation sheet

In the Data Validation sheet, data validation is applied to the ‘Banker’ column. This ensures that only the three bankers available in the dropdown can be selected. If a user types any name that is not in the dropdown list, an error message will be displayed.

Data validation is also used in the ‘Credit Limit’ column. This restricts the input to a credit limit ranging from 100 to 1000. If a number lower than 100 is entered, or a number greater than 1000 is entered, an error message will be displayed.

Lastly, data validation is applied to the ‘Date’ column. This restricts the input to a date range from 12/01/2023 to 12/24/2023. If a date before 12/01/2023 or after 12/24/2023 is entered, an error message will be displayed.

Balance sheet and Profit and loss statement

Kindly view the excel file and download it by clicking this link https://1drv.ms/x/s!AroJMieUeuj5gTIryvTdy9nOpmgI?e=eqmrL3

Note:​

When I embedded the code from the Excel files in OneDrive into this website for display, it either took too long to load or would not load at all, displaying an error message instead. As a result, I had to upload the files to Google Drive to display them on this website.

Also, please note that all the information in this file is made up. This is just an example of how the attendance sheet, interval staffing sheet and schedule is set up.

Microsoft PowerPoint

Here is an example of a PowerPoint Presentation of a fake Donut company

Kindly view it and download it by clicking this link https://1drv.ms/p/s!AroJMieUeuj5gS00ZcsZ1FObf7-M?e=W7gEbX

Google Slides does not have the Morph transition. That’s why there is no transition when the slides change. However, the Morph transition is displayed in the video below.

Here is the same PowerPoint Presentation of a fake Donut company save as a video in 4K.
Here is an example of a PowerPoint Presentation of a fake furniture store with 3D objects

Kindly view it and download it by clicking this link https://1drv.ms/p/s!AroJMieUeuj5gSWVXL7C5SibfFdZ?e=eAMLx5

Google Slides does not have the Morph transition. That’s why there is no transition when the slides change. However, the Morph transition is displayed in the video below.

Here is the same PowerPoint Presentation of the fake furniture store with 3D objects save as a video in 4K.

Microsoft Word

Here is the resume that I created using Microsoft Word.

Kindly view it and download it by clicking this link https://1drv.ms/w/s!AroJMieUeuj5gQ84lx6R68QtNzJp?e=41VJVs

Here is the Details about a fake furniture store that I created to demonstrate the use of Microsoft Word.

Kindly view it and download it by clicking this link https://1drv.ms/w/s!AroJMieUeuj5gSnbbqv4PLXy7jII?e=WDvj3X

Note:​

When I embedded the code from the Word file in OneDrive into this website for display, it either took too long to load or would not load at all, displaying an error message instead. As a result, I had to upload the file to Google Drive to display it on this website.

Microsoft Access

Unfortunately, when I tried to display this Microsoft Access file from one drive and Google Drive, I was unable to get the Microsoft Access file to display on this website. To see what I create in Microsoft Access, kindly download the file by clicking this link  https://drive.google.com/file/d/1io2emGv4O5tN43cV0VxSfP4NnsVojYoi/view?usp=sharing

Here is a video explaining the Microsoft Access file

Note

Please unmute the video to hear sound. The video was made by using Microsoft PowerPoint and Microsoft word.

Please note that all the information in this file is made up. This is just an example of demonstrating my use of Microsoft Access.​

Here is the explanation of the Microsoft access file

The Customer table includes the following fields: Customer Name, Phone Number, Email Address, Address, Region, and Country.

The Types of Furniture and Cost table lists the types of furniture sold by the store and the cost of each item.

The Orders table includes an Order ID field and a Customer Name field, which is populated from the Customer table using a dropdown list.

There is an Order Date field, which is filled by selecting a date from the calendar.

The Type of Furniture field is populated by selecting a furniture type from a dropdown list, which is sourced from the Types of Furniture and Cost table.

There is a Furniture Cost field in Guyana Dollars, which is entered manually. I cannot link the price from the furniture table to the order table because the lookup wizard does not allow currency linking.

The 10% Sales Tax In Guyana Dollars field automatically calculates 10% of the Furniture Cost once the cost of the furniture is entered.

There is a Payment Method field that has a dropdown menu with four different payment methods: Cash, Debit Card, Credit Card, and Voucher. If the customer pays with Cash, select Cash. If the customer pays with a Debit Card, select Debit Card. If the customer pays with a Credit Card, select Credit Card. If the customer pays with a Voucher, select Voucher.

The Order Fulfilled field is a yes/no checkbox. If the order is fulfilled, click the checkbox. If the order is not fulfilled, leave the checkbox empty.

The Order Fulfilled Date field is populated by selecting a date from the calendar. A validation rule ensures that the Order Fulfilled Date is the same as or later than the Order Date.

The Order Fulfillment Methods field has a dropdown menu with two different options: In-Store Pickup and Ship To Customer. If the customer visits the store to pick up the order, select In-Store Pickup. If the order is shipped to the customer, select Ship To Customer

The Shipping Cost in Guyana Dollars field has a formula that determines whether the customer has to pay shipping costs. If the Order Fulfillment Method is Ship To Customer, then the customer has to pay a shipping cost of 5,000.00 Guyana Dollars. However, if the Order Fulfillment Method is In-Store Pickup, the customer does not have to pay shipping costs since the customer visits the store to pick up the order.

The Total Cost In Guyana Dollars field uses a formula that adds the Furniture Cost, 10% Sales Tax, and Shipping Cost to determine the total cost of the furniture.

Here is a customer form that can be used to add more customers to the customer table.

Contact Me

Please feel free to contact me on WhatsApp:+592-658-1248, or by email: dramcomar@gmail.com