Reference no: EM133647605

Control stock

Assessment

Complete the following assessment tasks following the instructions provided for each task.

Task 1 - Stocktake - Calculate and submit the excel spread sheet file for Stocktake in soft copy and print and submit a hard copy, write your name, date, unit code and unit title on the print out.

Task 2 - Stocktake - Discrepancies - Calculate and submit the excel spread sheet file for Stocktake Discrepancies in soft copy and print and submit a hard copy, write your name, date, unit code and unit title on the print out. Also submit your summary report on the discrepancies and how to control these.

Task 3 - Stock Re-Ordering - Calculate and submit the excel spread sheet file for Stocktake Discrepancies in soft copy and print and submit a hard copy, write your name, date, unit code and unit title on the print out.

Task 1

Stock Control

You have been recruited as a new assistant to the purchasing officer at the Hotel Futura. As a part of your new role you are required to perform calculations to check the accuracy of the stock records and the costs, analyse stock records and identify any discrepancies and report to your supervisor.

For this task you are provided with the following file - SITXINV004 Assessment 2 Task 1 Stocktake spread sheet file that provides you with the data on the stock.

You are provided with the following figures (highlighted in red) for the stock on hand for items. You need to enter these figures into your stocktake sheet (refer and use the above excel spread sheet) to perform calculations so that you can establish the value of the individual stock items as well as provide a total value of all stock items on hand.

Items	Specification	Weight	/kg/l/unit	Price	Stock on hand
Dry Goods					SOH
Salt	Rock	1.000	kg	$     0.80	2
Pasta	Fettuccine	0.375	Packet	$     1.10	5
Tomatoes	Canned	0.375	Can	$     0.78	10
Soy	Sauce	0.500	L	$     2.80	0
Dairy
Cream	Thickened	0.600	L	$     1.52	2
Milk	Full cream	1.000	L	$     1.18	5
Butter	Salted	0.500	kg	$     1.82	0
Butter	Unsalted	0.500	kg	$     2.23	1
Cheese	Cheddar	1.000	kg	$     8.55	4
Seafood
Trout	Whole - 300g	1.000	each	$     3.20	6
Prawns	Cooked Medium	1.000	kg	$  18.50	2
Oysters	Pacific shucked	1.000	Dozen	$     6.80	5
Poultry & Meat
Lamb	Rack - 6 rib	1.000	each	$     4.80	2
Beef	Tenderloin	1.000	kg	$  18.00	5
Pork	Cutlet - 250g	1.000	each	$     2.60	0
Kangaroo	Rump - denuded	1.000	kg	$  14.00	3
Fruit & Vegetables
Lettuce	Butter	1.000	each	$     1.60	2
Tomatoes	Roma	1.000	kg	$     2.80	4
Potatoes	Desiree	1.000	kg	$     1.25	5
Apples	Granny Smith	1.000	kg	$     1.89	2
Mangoes	Fresh	1.000	each	$     1.80	3

Task 2

For this task you are provided with the spreadsheet file "SITXINV004 Assessment 2 Task 2"

The figures in these reports are different to the figures in the actual calculation spread sheet. Therefore your task is to identify and correct these discrepancies in the spread sheet.

You are required to check the data that has been entered in this spread sheet based on the latest stock report, because your supervisor has expressed concerns that there are discrepancies. These could be related to the price of the stock item or the quantity of the stock item. You need to identify the correct stock discrepancy and correct the figures so that all the figures tally with the final totals in each report.

You need to write a brief summary report to explain the potential reasons for these discrepancies and how impact on the total figures as well as the availability of these stock items in relation to maintaining and controlling stock.

Open the spreadsheet and identify all missing values in the opening stock column in each tab (Fridge, Freezer and Dry Store).

Complete the opening stock values based on any movements (Invoice = delivered, Out = used) or based on the closing stock values, i.e. if the closing value shows 5 and there were no movements, then enter 5 in the opening stock column where the value was missing. If there was a stock movement this needs to be calculated before entering the missing value in the opening stock column, i.e. if closing shows 6 and invoice shows 5, then the opening stock would have been 1. You will now notice that most entries you made have corrected the values in the discrepancy columns to 0

List 3 factors you would investigate where a value in the discrepancy column shows a negative.

Go to the tab "Fridge" and investigate the reasons for the discrepancies in Rows 8, 38, 83 and 89. Go to the tab "Dry Store" and investigate Row 65.

What are the likely problems that have resulted in these faults? Correct these faults according to your findings by adjusting the relevant values, if possible.

Go to the tab "Consolidated Total" and calculate how your adjustments from Question 5 above have affected the totals for each storage area and the total sum.

Key points to consider are:

Error in data entry (wrong numbers, decimals in wrong place, opening balance not entered, stock issued but it was actually invoice)

The following examples will assist you in your task.

Example 1

For example an error could relate to the fact that the original opening stock was not entered as highlighted [in yellow]. No stock was received (invoice) or issued (out), therefore you would enter the value of the closing stock in the opening stock column.

Item Description	Unit	Unit $	Supplier	Opening	Invoice	Out	Sub-Total	Closing	Closing $	Discrepancy
LEMON GRASS	100G	$     6.00					0	1	$       6.00	1

Example 2

An item shows in the opening balance 0.5 boxes and 0.5 boxes in the sub-total, but 1.5 boxes in the closing balance. This would require you to check whether there was an invoice. If not then you could check on the last stocktake and if that shows that the value there showed 0.5 boxes, then the value 1.5 likely is to be 0.5 boxes. As you have no prior documentation you would highlight this but make no changes.

Item Description	Unit	Unit $	Supplier	Opening	Invoice	Out	Sub-Total	Closing	Closing $	Discrepancy
BOCCONCINI	BOX	$    35.00	PETERS	0.5			0.5	1.5	$     52.50	1

Example 3

Any items which show a negative discrepancy need to be investigated whether a theft has occurred (consider chocolates, alcohol, prime cuts etc.), the opening balance was incorrect or an item was actually invoice rather than out or vice versa.

Item Description	Unit	Unit $	Supplier	Opening	Invoice	Out	Sub-Total	Closing	Closing $	Discrepancy
MINTS 9KG	BOX	$    69.00	EWH	0.5			0.5	0.2	$     13.80	-0.3

Task 3 - Stocktake - Stock Re-OrderingFor this task you are provided with the spreadsheet file "SITXINV004 Assessment 2 Task 3"

You are required to enter the stock data below in to the spreadsheet and calculate the stock on hand in order to calculate the total cost that you need to spend to re-order these stock items.

Items	Specification	Weight	/kg/l/unit	Price	Stock on hand	Minimum Level	Maximum Level	Order required
Dry Goods					SOH
Salt	Rock	1.000	kg	$     0.80	2	3	6	3
Pasta	Fettuccine	0.375	Packet	$     1.10	3	10	24	20
Tomatoes	Canned	0.375	Can	$     0.78	16	24	48	20
Soy	Sauce	0.500	L	$     2.80	20	2	6	0
Dairy
Cream	Thickened	0.600	L	$     1.52	20	10	40	10
Milk	Full cream	1.000	L	$     1.18	4	6	20	10
Butter	Salted	0.500	kg	$     1.82	6	10	40	10
Butter	Unsalted	0.500	kg	$     2.23	10	6	12	0
Cheese	Cheddar	1.000	kg	$     8.55	8	1	4	0
Seafood
Trout	Whole - 300g	1.000	each	$     3.20	16	15	30	10
Prawns	Cooked Medium	1.000	kg	$  18.50	12	3	8	0
Oysters	Pacific shucked	1.000	Dozen	$     6.80	24	24	48	10

Poultry & Meat
Lamb	Rack - 6 rib	1.000	each	$     4.80	15	20	40	15
Beef	Tenderloin	1.000	kg	$  18.00	20	10	25	0
Pork	Cutlet - 250g	1.000	each	$     2.60	25	20	40	0
Kangaroo	Rump - denuded	1.000	kg	$  14.00	16	10	30	10
Fruit & Vegetables
Lettuce	Butter	1.000	each	$     1.60	36	6	24	0
Tomatoes	Roma	1.000	kg	$     2.80	4	4	12	6
Potatoes	Desiree	1.000	kg	$     1.25	40	20	50	0
Apples	Granny Smith	1.000	kg	$     1.89	5	4	10	5
Mangoes	Fresh	1.000	each	$     1.80	16	20	60	20