Farm Performance Management Dashboard | Microsoft Excel without VBA | Video Tutorial #1
Farm Performance Management Dashboard | Microsoft Excel without VBA | Video Tutorial #1
Download our Templates / Get Free Datasets
Visit our online store https://www.other-levels.com
Contact us for business:
Comment down below
Customer services https://www.other-levels.com
Have a question about this dashboard?
Comment down below after joining our channel Membership,
/ @otherlevels
0:00, Dashboard overview
0:49, Annual and monthly key status and statistics overview
0:54, Monthly working capital expenditures
0:58, Body weight chart
01:12, Feed Inventory quantity and prices
01:15, Daily farm records
01:21, Flock in the Beginning and the End of stocking
01:27, Egg production, Cumulative eggs, and Trays
01:31, Feed intake eggs cost and weights
01:34, Stocking date, Hatched date, and bird’s age
01:38, Daily egg register showing sales and stock status
01:42, Comments and notice bar
01:44, Color Codes \u0026 Fonts
02:00, Enhance the data tables
02:00, Enhance the data tables
03:15, Insert buttons with Hyperlinks
09:21, Design the Dashboard Background
10:55, Company Logo and Main title
12:24, Key status and statistics overview
20:05, Monthly working capital expenditures
28:07, Explaining Pen Houses report
29:53, use Index Formula
29:54, Merge data for three Pen Houses
32:58, Insert Line chart \u0026 Slicer
36:23, Highlight the lowest, highest, and average weights
43:12, Quantity and prices for Feed stock
Subtitles are available in 14 Languages, Spanish, Hindi, Arabic, Japanese, Filipino, Chinese, English, Indonesia, French, Korian, Russian, Urdo, Bengali, and Portuguese.
Playlists
Multiple Dynamic Excel Dashboards • How to build Dynamic \u0026 Interactive Da…
Single Dynamic Excel Dashboard • Excel Real Estate and Property Manage…
Modern Excel Reports • Modern Excel Reports
PowerPoint Slides Playlist • How to Design a Modern Flow Process C…
Dashboard data Contents:
*Daily Bird’s wight record For three Pen Houses:
Starting from week 12 until week number 90, showing the actual weight, count of birds, and average weight.
*Daily record for the following:
Full Date
Stocking Date
Flock Number
Pen House #
Breed Name
Hatched Date
Birds Age Days
Birds Age Weeks
Beginning Stock #Flock
Total Mortality #
End Stock #Flock
Cumulative Mortality
Cumulative Eggs
Cumulative Egg Trays
Egg Production %
Total Feed Intake (Kg)
Feed Conversion g/egg
Feed Conversion cost (N)/egg
*Egg Register:
Total Stock (Trays)
Sales (Trays)
Closing Stock (Trays)
*Income Details:
Yearly Revenue
Monthly Revenue
Yearly Operational Profit
Monthly Operational Profit
Yearly Expenditure
Monthly Expenditure
Yearly Net Profit
Monthly Net Profit
*Total Monthly Working Capital Expenditure:
Monthly Working Capital Expenditure
Workers Salary
PAYE
Utility
Transportation
Maintenance
Veterinary Service Fee
Security
*Feed Inventory
Feeds Name
Unit Price
Quantity in Stock
Copyright © 2023 Other Level’s. All rights reserved.
“Any illegal reproduction of this content in any form will result in immediate action against the person concerned.”
#Farm_Performance_Management #Excel #Dashboard
Content
5.279 -> Welcome to Other Level's
8.32 -> in today's video we will learn how to create
10.16 -> this farm Performance Management Dashboard
12.639 -> using Microsoft Excel without VBA codes
31.679 -> you can get our templates and make full modifications to them
33.68 -> by visiting our Online store
35.84 -> other-levels.com
39.2 -> and for training and practicing you can
41.6 -> now download the dataset for this dashboard
43.44 -> for free from our website
46.399 -> this dashboard contains several analytics
49.76 -> annual and monthly key status and statistics overview
51.84 -> total monthly working capital expenditures
54.559 -> and their breakdown
56.719 -> weekly body weight chart
59.28 -> showing the current record compared to the standards weights
63.28 -> also it highlights the "lowest", "highest", and "average" weights
66.159 -> and we can choose
68.56 -> the displayed data according the "Pen House" number
70.32 -> quantity and prices for "Feed stock"
73.6 -> Many daily farm records
77.04 -> we can choose the data displayed by Pen House number and dates
81.119 -> total flock in the Beginning and the End of stocking
83.119 -> plus the total Mortality and percentage
87.28 -> Egg production, Cumulative eggs, and Trays
90.72 -> total feed intake eggs cost and weights
94.24 -> Stocking date, Hatched date, and bird's age
97.2 -> daily egg register showing sales and stock status
100.4 -> Finally, comments and notice bar
104.479 -> these are the color codes used in the design
106.32 -> and the font type as Arial and Calibri
109.92 -> all our dashboards template features are working in all versions of Excel
115.04 -> Mac and Windows OS
121.2 -> let's start by enhance the spreadsheets first
128.56 -> we will leave the column A, and column B Empty for now
130.72 -> to add hyperlinks buttons here
136.959 -> we have separate reports
139.2 -> so you will learn how to combine multiple reports
141.44 -> into one beautiful dashboard
144 -> and how to connect the slicer with a specific part
145.84 -> than the other
149.36 -> as usual, we'll show the formats used for every part
152.239 -> must unify the overall appearance of reports
171.04 -> and another worksheet as the Pen Houses report
173.28 -> we prefer to keep it in separate worksheet
176.16 -> because it's have a large data
201.2 -> we finished the reports
203.519 -> now we will insert the buttons
206.72 -> and to set exact column size in both worksheets
209.12 -> we must use the cell size tool
213.44 -> set 5.5' width for column A
218.92 -> and 0.27' for column B
281.84 -> group the similar icons
284.479 -> then add a hyperlink to each of them
324.32 -> we will use the "Selection Pane"
326.639 -> for easy control and modification the icons
334.56 -> cell reference will be "A1"
337.6 -> adding screentip text is important
346.8 -> repeat previously steps to the remaining icons
412.319 -> no space above the data table to add the title
414.639 -> so we will place it on the left side
416.96 -> with a 90 degree rotation
433.919 -> group all the elements then "copy"
436.88 -> and paste into the other worksheets
438.88 -> and change the data table title
451.599 -> delete the unused icons
454.24 -> and change the color of the correct used icon to "Yellow"
543.36 -> Next, a very necessary step to avoid moving icons
546.24 -> when updating the data
549.12 -> must choose this option "do not to move or size with cells"
557.76 -> we're done with it, now we're going to start designing
559.76 -> the main dashboard
571.519 -> we'll use the "cell size" menu to adjust the widths
581.12 -> the first background color will be Dark Gray
583.36 -> appears as an outer frame
602.959 -> set light gray color the main background for the dashboard
612.959 -> as usual we have to hide the (cell selection border)
615.279 -> using a rectangle having the same background color
651.2 -> the background is ready now we will
653.68 -> adjust the right side later
656.399 -> let's start by adding a title to this dashboard
658.64 -> and the company logo
723.92 -> diversity in the font formats gives a better look
745.92 -> let's begin the first analysis
748.639 -> annual and monthly key stats and statistics overview
752.32 -> we will distinguish this part by adding a Light Gray color
754.399 -> on the right side of the background
782.959 -> i don't like the Gray and White "gradient"
785.6 -> so i'll use shape to cover this gradient color
788.32 -> to make it appear sharp instead
858.56 -> write statistics overview in the first title
861.04 -> will add shortly the second "dynamic" title
871.76 -> this table contains financial values amounts
874.16 -> for the current month and year
877.279 -> insert pivot table, in the pivot table worksheet
879.76 -> add particulars in "Row" field
882.8 -> and the amounts in "Value" field
901.44 -> copy the financial values, and paste them beside
903.839 -> we will use "Vlookup" formula
906.88 -> to add the amounts
942.72 -> add slicer, to switch between the monthly and annual amounts
952.72 -> to know how to modify slicers
955.279 -> please watch the video above
962.079 -> i will add the "duration" in the filter field
964.079 -> to be used in the second dynamic title
979.759 -> we don't want "empty data" or the "header" to appear
988.959 -> Next, we start by adding
991.6 -> the financial values names and their value
994.079 -> plus the currency symbol down below
1061.12 -> our currency is the Egyptian Pound
1063.76 -> and its abbreviation is the letter E and the
1066.16 -> Pound symbol "￡"
1078.32 -> find good icons from excel online images
1104.48 -> it remains only to add the second dynamic title
1106.4 -> the idea is simple
1109.679 -> but it makes a big difference
1115.679 -> i will use "IF formula"
1118.72 -> the explanation of the formula is:
1120.64 -> if the value in the "filter" equal = to word "Years"
1123.76 -> then write the "Current year's performance"
1126.24 -> if the result is different
1128.96 -> write "Current month's performance"
1170.96 -> we are done with this analysis
1173.28 -> let's complete the dashboard right side background
1202.4 -> we will now move to total "monthly working capital expenditures "
1204.32 -> and their breakdown
1212.32 -> the "White" color as enough
1223.36 -> the data table with the total are simple
1226 -> let's create a pivot table
1240.08 -> we will repeat the same previous steps
1274.32 -> but we will add the "IF Error" formula
1276.559 -> to avoid errors,
1279.039 -> and we will go back to adding it in the previous formula
1304.96 -> we need 6 text boxes
1344.559 -> add a picture of workers while they work
1347.2 -> it is better to avoid adding
1349.28 -> pictures that do not belong to your farm
1351.28 -> or the real workers
1352.96 -> Crop of a picture using a square shape
1355.44 -> with rounded corners
1366.4 -> we only need to show the right corners
1368.48 -> and the left corners will be hidden
1370.24 -> using a white rectangle
1400.96 -> here we add the total working capital expenditures
1414.88 -> transparency 20%
1460.48 -> adjust spacing between text boxes using the Align tool
1512.24 -> align the text from right to left
1514.559 -> so as not to overlap with the currency symbol
1516.799 -> that we will add on the right side
1519.6 -> now we connect each text box to its correct value
1682.559 -> Gorgeous! we finished the top part
1685.76 -> now we move on to the most important analyzes in Poultry Farms
1690 -> the weekly body weight chart
1692.24 -> showing the weekly weight record,
1694.159 -> compared to the "Standard weights"
1708.96 -> i will explain to you the contents of this large spreadsheet
1710.96 -> here is the "number of weeks"
1713.36 -> and the weight record starts from the week number 12
1715.44 -> until the week number 90.
1719.6 -> in this column showing numbering for "100 birds"
1722.72 -> for example: in the first cell
1726.159 -> it shows the grams weight for the first bird
1728.08 -> in the first week
1730.24 -> in the second cell: it shows the grams weight
1732.64 -> for the second bird in the first week
1734.559 -> and so on... until the 100
1737.679 -> in here we used a formula to find the "weight Average"
1741.84 -> and this will be the "Actual weight"
1747.44 -> as for the count row it is the total number of birds
1749.76 -> which is 100 as we explained before
1754.159 -> this farm contains 3 Pen Houses
1757.279 -> the first report is for the Pen House number One
1759.52 -> and this is the report for the second Pen House
1769.52 -> and here the third house
1771.52 -> alright, now this is the table
1773.76 -> that we will use to create the "line chart"
1776.32 -> it's having three columns: week number,
1779.52 -> Minimum weight as per the standards,
1782.08 -> and highest weight as per the standards
1784.72 -> now we will add the "Actual weight"
1786.88 -> to compare it with the "Standard weights"
1792 -> we will use the "INDEX" formula
1794.96 -> Equals sign "="
1799.44 -> INDEX, choose the row that containing the data
1817.52 -> type "2" commas ","
1821.84 -> then row A1 and close the bracket )
1834.399 -> and with that we have finished adding actual weights data
1836.559 -> for Pen House number 1
1840.159 -> now we want to add the actual weights
1842.08 -> for Pen House number two and three
1844.799 -> copy the "standard weights" and "weeks Numbers"
1846.72 -> and paste them in the bottom of the table
1885.279 -> use the same formula but change the "data source"
1887.76 -> replace row number 8 with row number 111
1930 -> as for the third Pen House will be row number 313
1950.399 -> now your data table is completely ready!
1952.72 -> so let's insert a pivot table
1958.559 -> add the week numbers in the "row" field
1963.44 -> and the three weights in the "value" field
1980.88 -> we will now create a line chart
1982.799 -> and insert a pen's house's number slicer
2046.88 -> the actual weight appears in Blue line
2049.28 -> but to be more special than others
2051.28 -> we will add a gradient shade in below
2054 -> by re-adding the "Actual weight" to the pivot table
2062.56 -> then select the "line" and change it to "Area" chart
2084.24 -> remove borders, set White and Green gradients colors
2104.56 -> for the Green, keep it transparent in 70
2108.079 -> and 100 for the White
2131.04 -> and yellow is the lowest weights as per the standards
2136.4 -> the red line is the highest weights as per the standards
2161.28 -> Beautiful! now we need a title for this analysis
2182.72 -> in addition it's better if we highlight
2185.2 -> in which week was the highest weight reached?
2187.04 -> and in which week was the lowest weight reached?
2190.88 -> Finally, the actual average weights during weeks
2193.359 -> first to find the lowest value
2196.4 -> write equal sign "="
2198.8 -> MIN, then choose the range you want to search in
2202.88 -> Second, to find the "highest value"
2205.76 -> write equal sign, "maximum"
2207.119 -> comma, then choose the range you want to search in
2318.4 -> to find the weak number for the lowest and highest weight
2320.48 -> we cannot use Vlookup formula
2324.72 -> as you can see the weak number column
2327.28 -> is before the actual weight column
2329.68 -> and the vlookup formula searches from left to right Only!
2333.52 -> for this we will use the "X lookup" formula
2335.839 -> equal sign, "X lookup" select the value to search for
2339.92 -> then select its range
2342.48 -> then the targeted values range
2349.92 -> as you can see now the lowest weight was in "week number 16"
2364.4 -> let's connect the text box with the week number cell
2375.839 -> but as you see adding week number only
2378.4 -> is not so good
2380.24 -> so we will add a "text sentence"
2382.8 -> using "concatenate" formula
2391.04 -> double quotes, then enter the text
2394.48 -> "week number #" double quote comma,
2398.4 -> then close the brackets ) ,and press Enter
2401.2 -> now as you can see the text has been merged
2403.839 -> with the week number
2405.44 -> which extracted from the x lookup formula
2443.359 -> at the bottom we'll place the average weights
2481.04 -> now we want to add a button to move to the detailed report
2484.96 -> we will use an arrow and add a hyperlink to it
2516.56 -> it is important to add a "Legend"
2518.48 -> that shows the meaning of these three lines
2584.48 -> we are done with this complicated analysis
2587.68 -> next quantity and prices for feedstock
2595.44 -> the data table contains feeds names,
2598.24 -> their prices, and quantities
2600.96 -> we do not need to create a pivot table
2603.52 -> we just insert a bar chart directly from
2605.92 -> this table
2624.72 -> add adjustments on the fonts and bars
2714.079 -> use the sum formula
2716.4 -> to find the total price and quantity
2762.16 -> so far we did better good,
2765.52 -> next video we will complete the rest
2767.52 -> and it will be ready by next week
2769.599 -> i hope you learned something useful today
2771.44 -> Thank you and see you in the next video
Source: https://www.youtube.com/watch?v=95_QYxvOkwg