5N1977: You Work in a Small Local Convenience Store in Your Locality: Spreadsheet Methods Assignment, BCFE,

Learning Outcomes:

• Explore key spreadsheet elements including cells, cell references, numeric, alpha, and alphanumeric data, formulae, functions, graphs
• Demonstrate common spreadsheet usability features to include the use of toolbars, window management, sorting
• Use spreadsheet design features involving data and cell formatting techniques which enhance understanding and legibility.
• Print complete or partial sections of a spreadsheet, formatted fit for presentation.
• Use advanced spreadsheet features including absolute and relative cell references, conditional IF statements, statistical, financial, and date and time functions.
• Generate a variety of types of graphs, with appropriate titles and labels, from spreadsheet data.
• Demonstrate personal initiative and resourcefulness in editing and amending spreadsheets to ensure they are fit for purpose.

Brief:

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. VAT rates and codes
2. A Stocklist with different products
3. A week’s Record of Sales
4. Depreciation of the Company Van, tills and shelving

A. The Local Shop

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

B. Depreciation.

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

C. Adjusting your work book

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

VAT rates in Ireland 2020

0% (Zero) VAT

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.

4.8% is a rate of VAT specifically for agriculture

It applies to livestock (excluding chickens), greyhounds and the hire of horses.

9% is a special reduced rate of VAT for:

• Newspapers
• Facilities for taking part in sporting activities including green fees charged for golf and subscriptions charged by non-member-owned golf clubs.
• Electronically supplied publications

13.5% : This lower rate of VAT is charged on items including

• Fuel (coal, heating oil, gas), electricity,
• Vet fees,
• Building and building services,
• Flowers,
• Agricultural contracting services,
• Short-term car hire,
• Cleaning and maintenance services,
• Catering and restaurant supplies, including vending machines and take-away food (excluding alcohol and soft drinks sold as part of the meal),
• Food Supplements,
• Hotel lettings, including guesthouses, caravan parks, camping sites etc,
• Short term accommodation,
• Cinemas, theatres, certain musical performances, museums, art gallery exhibitions,
• Fairgrounds or amusement park services,
• Hairdressing services.

23% is the standard rate of VAT.

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.