Store Inventory

You are to produce a spreadsheet that allows a small business owner to track the week's sales and maintain an ongoing inventory. As for all spreadsheet assignments that can handle different inputs, the input will be on worksheets that are not to have any computational work done on them and the final results will be on worksheets with specific names. You will be given two sets of sample data to work with, but your work will be graded with different data. It is therefore critical that you NOT do any work on the input sheet and that you not assume any of the data will stay the same. What WILL remain the same is the number of data entries and the location of all information.

You will be given two worksheets, This Week Sales and Last Week Inventory.

This Week Sales will have three columns: Day, Item, and Quantity. It captures the number of each item sold each day. You are to assume that the days of the week are the known seven days and may be coded explicitly. There will be 4 different items that the store sells. (I told you it was a small business!) There will be 15 rows of data on the worksheet.

Last Week Inventory will also have three columns: Item, Quantity and Sales Price. The 4 items that the store sells will be listed with the quantity at the end of last week and the price at which each item is sold.

You are to create two new worksheets titled Sales Summary and Current Inventory.

Sales Summary is to have a table with a row for each day of the week and a column for each item that the store sells. In addition, it is to have a total dollar sales for each day, total of the number of each item that is sold, and the total revenue generated by each item.

Current Inventory is to have the exact same format as Last Week Inventory with the quantity being the current quantity for each item (last week's inventory minus the number of that item sold). The sales price should be the same as last week's. (No inflation and no sales here.)

There may be errors in This Week Sales.

1. If the item listed is not in the inventory, it is to be counted in a separate column called "miscellaneous". You are not to compute Total Sales for miscellanous, but you are to compute Total Items Sold. Miscellaneous items do not contribute toward the Total Daily Sales. Ignore miscellaneous items for the Current Inventory.
2. If the Quantity is non-numeric or missing, you should treat it as a zero.

Two sample inputs and their expected output can be downloaded from Souvenir.xlsx and Souvenir.pdf and Food.xlsx and Food.pdf.

The web page that you are to build is to include the Sales Summary table and the two inventory tables -- but just the quantity, not the price. Your tables are to have have dates associated with each of the table and your page should explain the nature of the store. The data that you are to use for this page can be found at image_store.xlsx.

Grading Criteria: Grading for this assignment is based primarily on the correctness of the results. Simplicity of the solution and readability of the results will also be considered.