PREPARATION	Read all instructions thoroughly before attempting this assignment
Read an understand the data dictionary before attempting this assignment
Do not add or remove any columns or rows
Do not move any cells
Only use cells/ranges specified in the instructions
Download and install the Code39 barcode font from https://www.idautomation.com/free-barcode-products/code39-font/#:~:text=To%20generate%20a%20Code%2039,data%2C%20i.e.%20(12345).
Restart your computer after font installation
Follow all instructions precisely
Use names (e.g. worksheet names) specified in these instructions exactly as written.
Data	B13#	Create a unique list of categories using the UNIQUE function, spilling from B9 to B16, using an appropriate range for the categories
A1	Format the heading bold, Arial 14 pt.
Stocktake	B3	Using data validation, create a drop-down list containing categories from the Data worksheet. Data > Data Validation > Allow: List > Source:
B3	Select \"Other frozen meats\" from the drop-down list
B4	Use one of the COUNT functions to count the number of product lines shown in the Stocktake A column for the category selected in B3. Remember to use the appropriate reference type to ensure that all items are counted, regardless of the category selected).
A7#	Use the FILTER function to show a list of all food UPC barcode numbers for the category selected in B3. Results will \"spill\" down the page as far as required.
B7#	Use XLOOKUP and the concatenation operator to build the names of the products in the format Brand - Name (productsize) (eg 34183000045, ADVANCEPIERRE FOODS - THINLY SLICED SIRLOIN BEEF (1 STEAK)) Capture any #-type errors using IFERROR. Replacing the error with the empty string (NOTE: Empty string NOT a space).
A7:A4000	Format the range in Column A using the newly-installed IDAutomationHC39 font. This will allow employees to scan the barcodes using their handheld scanners during stocktake.
A7:A4000	Resize all row heights (for row 7 to the bottom of the worksheet) to 64 pixels, and align the cells using Middle Align (not centre!)
B7:B4000	Wordwrap the text. Align to Middle & Left
Products	A:M	Add filter buttons to all columns.
1:1	Freeze the top row
1:1	Apply bold format to the first row
A:M	Sort the table by foodID, smallest to largest
L2:L9359	Use VLOOKUP to obtain the stocktake count from the Import sheet. Use IFERROR to replace any #N/A errors with 0
M2:M9359	Calculate the difference using the following method: Stocktake count minus on-hand
Import		Create a new worksheet called Import
A1	Enter \"UPC\"
B1	Enter \"Physical count\"
A2:B9359	Import the stocktake data from the scanner.csv file. Use the text import feature in Windows, or open the CSV as a text file in Mac and copy/paste
Allergen audit	Explanation	Management is considering re-configuring the freezer facilities to group product with allergens together so the facility will comply with new food safety standards. Prepare a report showing products containing any ingredient with \"nut\", \"fish\", or \"egg\" in the ingredient list, so that this report can be used by freezer facility staff planning the reconfiguration project.
A1	Apply the font Arial 14pt bold to the heading
B3	Use data validation to create a drop-down list containing the allergens listed on the Data worksheet.
A7#	Write a single formula in this cell using FILTER to show all columns from the Products worksheet where the ingredients column contains the allergen in B3. Use the FIND function in your formula to check the entire Ingredients column for the allergen in B3, and if a #VALUE error occurs, use the appropriate function to convert it to 0. Note: * The ingredients list in the Products worksheet is written entirely in upper-case letters * The FIND function is case sensitive * The allergen in B3 will need to be converted to upper-case in your formula using the appropriate function. Your formula in this cell must spill the results to the appropriate range in the table
B4	Concatenate a sentence explaining the number of products found for the currently selected allergen in B3 in the format: \"279 products containing fish-related allergens\" Ensure you use the appropriate (text-related) function to use lower-case letters for the allergen listed in B3. The component of your formula counting the number of products must use the column reference for the entire A column.
Inventory report	A1	Apply the font Arial 14pt bold to the heading
B5:B12	Apply conditional formatting - blue data bars (solid colour)
C5:C12	Apply conditional formatting - green data bars (solid colour)
F8:F12	Calculate the percentage of the top 5 products\' items on-hand as a percentage of the total number of items on-hand. Format the number using percentage format with 4 decimal places.
G8:G12	Concatenate a sentence showing each top product\'s name in the format: \"Large Green Peas (165 items on-hand)\" Ensure the entire string is presented in proper-case using the PROPER function. Hint: Use XLOOKUP inside a VLOOKUP
F3	Calculate the number of product lines in the product sheet. Apply the thousand separator with no decimal places
F4	Calculate the number of items on-hand for all products in the freezer facility. Apply the thousands separator, with no decimal places.
A3:A4	Merge and centre the CATEGORY heading
B3:C3	Merge and centre the COUNTS heading
B4:C4	Apply italic format and align the cell contents to the centre
A5	Write a single formula in this cell using the UNIQUE function to show all the unique categories from column H in the Products sheet. Use the SORT function to arrange these in alphabetical order. Ensure all unique categories spill down column A as far as needed.
B5:C12	Format numbers using thousands separators and no decimal places
Shrinkage report	A1	Write a formula to produce the heading \"SHRINKAGE REPORT FOR MARCH, 2023\" Ensure all upper-case letters, and the month and year names change based on today\'s date.
A1	Format the heading bold, Arial 14 pt.
A4	Write a formula using the FILTER function to list the foodID, UPC, Brand and Product name for all products having a negative difference on the Products sheet.
E4	Using VLOOKUP, write a formula to obtain the difference amounts from the Product sheet, ensuring you use MATCH to match the column heading on this sheet with the correct field name in the named range \"productFields\" (which refers to the headings on the Products sheet). Use the appropriate reference types.
B:B	Format the whole column as \"number\" with zero decimal places

Introducing Winter is Cumin Frozen Foods, the distributor that will send shivers down your spine. Our products are so frozen, they\'ll make your blood run cold. From raw ingredience to ready made meals that have been put on ice, our selection will leave you feeling like you\'ve been stuck in a meat locker. With our icy wit and frosty personalities, we\'ll make sure your frozen veggies, meats, space pirates and treats arrive on time and in perfect condition. Our secret weapon? Our delivery trucks are equipped with built-in snow machines, so you know we mean business when it comes to keeping things icy! Look no further than Winter is Cumin. We\'ll freeze your taste buds and leave you begging for mercy, however no-one will hear you as our lockers are also sound proof. Choose us and embrace the chill! NOTE: All data has been generated. No real data has been supplied. Carbon freezing not recommended.

Permissible functions (in addition to those contained in existing formulas)
AND	FIND	MID	STDEV.P
AVERAGE	HLOOKUP	MIN	SUBSTITUTE
AVERAGEIF	HOUR	MOD	SUM
CHAR	IF	MONTH	SUMIF
CHOOSE	IFERROR	OR	TEXT
CONCAT / &	LARGE	PROPER	TODAY
COUNT	LEFT	RANK.EQ	UNIQUE
COUNTA	LEN	REPLACE	UPPER
COUNTIF	LOWER	RIGHT	VLOOKUP
DATE	MATCH	ROUNDDOWN	WEEKDAY
DAY	MAX	SMALL	XLOOKUP
FILTER	MEDIAN	SORT	YEAR
			YEARFRAC
NOTE 1: This is a list of permissible functions. You do not need to use them all - choose wisely. Do not use other functions not on this list.
NOTE 2: If you do not know a function and we have not covered it in class or LinkedIn Learning videos, you are expected to teach yourself.

Attachments:

bism1201-part....xlsx