Notifications
Clear all
Office365
1
Posts
1
Users
0
Reactions
39
Views
Topic starter
22/07/2024 7:47 am
Create an Excel sheet with 20 agricultural items, showing payments, products bought, and include questions to practice various Excel formulas and conditional formatting.
Agricultural Inventory and Payments
Excel Sheet Structure:
ID | Product | Quantity Bought | Price per Unit ($) | Total Cost ($) | Payment Status | Discount (%) | Final Cost ($) | Payment Date | Supplier |
---|---|---|---|---|---|---|---|---|---|
1 | Wheat | 500 | 1.50 | =C2*D2 | Paid | 5 | =E2-(E2*F2/100) | 2024-01-10 | Supplier A |
2 | Corn | 300 | 2.00 | =C3*D3 | Unpaid | 10 | =E3-(E3*F3/100) | 2024-01-15 | Supplier B |
3 | Soybeans | 400 | 1.75 | =C4*D4 | Paid | 0 | =E4-(E4*F4/100) | 2024-01-20 | Supplier A |
4 | Rice | 600 | 1.60 | =C5*D5 | Paid | 15 | =E5-(E5*F5/100) | 2024-01-25 | Supplier C |
5 | Barley | 450 | 1.40 | =C6*D6 | Unpaid | 5 | =E6-(E6*F6/100) | 2024-01-30 | Supplier A |
6 | Oats | 350 | 1.55 | =C7*D7 | Paid | 8 | =E7-(E7*F7/100) | 2024-02-05 | Supplier B |
7 | Canola | 200 | 2.20 | =C8*D8 | Paid | 12 | =E8-(E8*F8/100) | 2024-02-10 | Supplier C |
8 | Cotton | 150 | 1.80 | =C9*D9 | Unpaid | 10 | =E9-(E9*F9/100) | 2024-02-15 | Supplier A |
9 | Sorghum | 100 | 1.65 | =C10*D10 | Paid | 5 | =E10-(E10*F10/100) | 2024-02-20 | Supplier B |
10 | Sunflower | 250 | 2.50 | =C11*D11 | Unpaid | 20 | =E11-(E11*F11/100) | 2024-02-25 | Supplier C |
11 | Sugarcane | 300 | 1.90 | =C12*D12 | Paid | 7 | =E12-(E12*F12/100) | 2024-03-01 | Supplier A |
12 | Peanuts | 220 | 2.10 | =C13*D13 | Unpaid | 15 | =E13-(E13*F13/100) | 2024-03-05 | Supplier B |
13 | Tobacco | 180 | 2.30 | =C14*D14 | Paid | 10 | =E14-(E14*F14/100) | 2024-03-10 | Supplier C |
14 | Alfalfa | 400 | 1.50 | =C15*D15 | Paid | 5 | =E15-(E15*F15/100) | 2024-03-15 | Supplier A |
15 | Hay | 500 | 1.60 | =C16*D16 | Unpaid | 10 | =E16-(E16*F16/100) | 2024-03-20 | Supplier B |
16 | Flaxseed | 280 | 1.70 | =C17*D17 | Paid | 12 | =E17-(E17*F17/100) | 2024-03-25 | Supplier C |
17 | Rye | 320 | 1.45 | =C18*D18 | Unpaid | 8 | =E18-(E18*F18/100) | 2024-03-30 | Supplier A |
18 | Hops | 150 | 2.40 | =C19*D19 | Paid | 10 | =E19-(E19*F19/100) | 2024-04-05 | Supplier B |
19 | Millet | 400 | 1.35 | =C20*D20 | Paid | 5 | =E20-(E20*F20/100) | 2024-04-10 | Supplier C |
20 | Lentils | 300 | 1.85 | =C21*D21 | Unpaid | 10 | =E21-(E21*F21/100) | 2024-04-15 | Supplier A |
Key Features for Practice:
-
Basic Formulas:
Total Cost
:=C2*D2
(for all rows)Final Cost
:=E2-(E2*F2/100)
(for all rows)
-
Conditional Formatting:
- Highlight cells in the
Payment Status
column that are "Unpaid". - Color-code
Final Cost
based on high (> $700), medium ($300-$700), and low (< $300) values.
- Highlight cells in the
-
Data Validation:
- Restrict entries in the
Discount (%)
column to be between 0 and 20. - Create a dropdown list for the
Supplier
column with predefined suppliers (e.g., Supplier A, Supplier B, Supplier C).
- Restrict entries in the
-
VLOOKUP/HLOOKUP:
- Add a separate table listing
Supplier
andContact Info
. UseVLOOKUP
to fill in theContact Info
for each item in the main table.
- Add a separate table listing
-
SUMIF/SUMIFS:
- Calculate the total
Final Cost
for each supplier usingSUMIF
.
- Calculate the total
-
Pivot Tables:
- Create a Pivot Table to summarize total
Quantity Bought
,Total Cost
, andFinal Cost
bySupplier
.
- Create a Pivot Table to summarize total
-
Charts:
- Create a bar chart to visualize the total
Quantity Bought
of each product. - Create a pie chart to show the distribution of
Total Cost
bySupplier
.
- Create a bar chart to visualize the total
-
Sorting and Filtering:
- Sort the table by
Final Cost
in descending order. - Apply filters to show only products with
Quantity Bought
greater than 300.
- Sort the table by
Questions for Practice:
-
Basic Formulas:
- Calculate the
Total Cost
for each product. - Calculate the
Final Cost
for each product after applying the discount.
- Calculate the
-
Conditional Formatting:
- Highlight the rows where the
Payment Status
is "Unpaid". - Apply a three-color scale to the
Final Cost
column to differentiate high, medium, and low values.
- Highlight the rows where the
-
Data Validation:
- Set a validation rule to allow only numbers between 0 and 20 in the
Discount (%)
column. - Create a dropdown list for the
Supplier
column with the options: Supplier A, Supplier B, Supplier C.
- Set a validation rule to allow only numbers between 0 and 20 in the
-
VLOOKUP:
- Use
VLOOKUP
to populate theContact Info
for each supplier in a new column based on a separate table of supplier contact details.
- Use
-
SUMIF/SUMIFS:
- Calculate the total
Final Cost
for each supplier using theSUMIF
function.
- Calculate the total
-
Pivot Tables:
- Create a Pivot Table to summarize total
Quantity Bought
,Total Cost
, andFinal Cost
bySupplier
.
- Create a Pivot Table to summarize total
-
Charts:
- Create a bar chart to visualize the total
Quantity Bought
for each product. - Create a pie chart to show the distribution of
Total Cost
bySupplier
.
- Create a bar chart to visualize the total
-
Sorting and Filtering:
- Sort the table by
Final Cost
in descending order. - Filter the table to show only products with
Quantity Bought
greater than 300.
- Sort the table by