Overview: You work in a small local convenience store in your locality, your manager is very old fashioned and everything is done manually. You are recommending computerizing the system and your manager has asked to see a sample set-up.
You want to create a set of spreadsheets and workbooks that include
1. Create a workbook called The Local Shop and your login, initials and class.
2. Using the attached list of VAT rates, in Sheet 1 list the VAT rates and create a code for each. Then list relevant product categories for each VAT rate. (Examples of product categories are: Fresh food, frozen food, other foods, cleaning products, toiletries, drinks, confectionery (sweets and chocolates), newspapers/magazines, etc.).
3. Rename Sheet 1 VAT Rates and Codes.
4. In sheet 2 lists 21 products to include only 4 VAT rates, and at least 7 product categories. Display the information as follows:
5. Rename Sheet 2 as Stocklist.
6. In Sheet 3 create another worksheet named Week’s Sales.
7. Copy all the headings from Stocklist into the Week’s Sales sheet
8. Then copy the Product Code column from the Stocklist into column A in the Week’s Sales.
9. Use a Lookup formula using the Product code to link to the Stocklist sheet for the following columns:
Select the appropriate group of cells from sheet 2 (Stocklist) and give it an appropriate name
10. Add three columns:
11. Show the VAT rate using the VAT Rates and Codes worksheet (sheet 1) with a nested IF function that depends on the VAT Code. Use absolute cell references.
12. Calculate the amount of VAT for each product.
13. Calculate the Retail price including VAT.
14. List the number of items sold for each product for 6 days (the shop is closed on Sunday) in additional columns.
15. Create a new worksheet named Totals, and use linking formulas to copy the list of product codes and product names from Stocklist into columns A and B, and the VAT codes into column C.
16. Use linking formulas to calculate the Total Number of products sold for each day.
17. For each product, use linking formulas to calculate the Total Sales for each day excluding VAT
18. For each product, use linking formulas to calculate the Total Sales for each day including VAT.
19. Calculate the Total Income for the week.
20. Use linking formulas to calculate the Average Sales of each product; show the result with one decimal point.
21. Use linking formulas to calculate the Minimum Sales of each product
22. Use linking formulas to calculate the Maximum Sales of each product
23. Use Conditional Formatting to highlight the Total Sales including VAT using criteria of your choice
24. Use SUMIF to create to calculate the total number of products sold per VAT Code.
25. Create a chart in a separate worksheet to show the total number of products sold per VAT Code. Use appropriate headings and labels.
26. Create another chart using the data of your choice; save it in a separate worksheet.
27. Delete any unused worksheets.
28. Prepare the Week’s Sales worksheet for printing, so that it will print on one page only and in landscape orientation. Save but do not print.
29. Prepare the Stocklist worksheet for printing, showing the rows and column headings and in portrait orientation. Save but do not print.
30. Prepare the VAT and Totals worksheets to print on one page in a suitable orientation
1. Create a new workbook called Depreciation your login initials and class and enter the data shown below.
2. Calculate the depreciation on all the equipment. Calculate the total amount of depreciation
3. Calculate the resulting value
4. If the equipment is worth less than the disposal price at the end of the depreciation period, then show the text “Ready to Scrap” in the last row, otherwise show the text “Still useable”.
5. Name the worksheet Asset Depreciation.
6. Prepare the worksheet to print the table without the last 2 rows (Resulting Value and Scrap?), in portrait orientation
Save the Workbook The Local Shop as Adjusted Local Shop and your Login
In the adjusted workbook:
1. Sort the Week’s Sales list using two criteria
2. Delete one product of your choice and save
Please indicate under the information which product you deleted
3. Insert 2 extra rows at the top of the Stocklist sheet and using a formula enter today’s date in cell A1.
4. In row 2, type Stock List use a heading style and merge and center the heading across the top of the information
5. Insert a new column in the Week’s Sales worksheet and Type Week beginning and change the direction of the text to vertical (if you do not have the facility for changing text direction on your version of Excel, please attach a word document explaining how you would do it)
6. Save the workbook
Most food including tea, coffee, milk, bread, butter, cheese, milk, vegetables, meat, etc. (not when supplied from a vending machine or in the course of catering and not food supplements such as slimming or sports supplements and protein supplements)
Books, children’s clothes and children’s shoes, oral medicine for humans and animals, vegetable seeds and fruit trees, fertilizers, large animal feed, disability aids such as wheelchairs, crutches, and hearing aids, and all exports.
It applies to livestock (excluding chickens), greyhounds and the hire of horses.
All goods and services that do not fall into the reduced rate categories listed above are charged at this rate. Chocolates and sweets, toilets rolls, tissues and cleaning products are examples.