70.2K

Verified Solution

Link Copied!

Big Data Project

Professor Burnside has a side mail order business called Burnside Storage which sells storage kits and other items used in backyards. Professor Burnside has been keeping the inventory figures manually. He has decided that a spreadsheet application can help him keep better tabs on the inventory and allow him to analyze how will the company is doing selling each unit.

Burnsides Storage sells the following items:

Inventory Number	Description	Type	Cost	Price	On Hand Quantity	Sales per year
B003	8' Picnic Table	Table	211	350	44	157
B007	8' x 4' Steel Shed	Shed	425	700.75	50	215
B008	6' x 4' Steel Shed	Shed	310	520.99	50	241
B009	Bending Trellis	Trellis	50	57	20	100
B010	12' Wishing Fount	Fount	131	195.99	25	147
B011	10' x 16' Aluminum Outbuilding	Outbuilding	810	1075.99	10	67
B012	Burnsides Better Trellis	Trellis	770	850	25	70
B014	8' x 6' Steel Shed	Shed	150	200	50	500
B016	8' x 10' Picnic Table	Table	270.99	399.99	50	299
B017	10' x 14' Steel Shed	Shed	650	1200	20	250
B018	8' x 10' Aluminum Outbuilding	Outbuilding	540	805	50	600
B019	4' Aerogenerator	Aerogenerator	31	46	300	201
B022	8' x 10' Pine Shed	Shed	1140	1800	72	100
B023	6' x 20 Covered Span	Span	1250	1401	42	75
B025	12' Square Pine Gazebo	Gazebo	2500	3000	71	144
B026	12' x 20' Steel Shed	Shed	1393.56	1950.99	32	65
B031	12' Pine Rotunda	Rotunda	2970	3500	15	39
B033	8' x 10' Steel Outbuilding	Outbuilding	840.47	1050.99	80	210
B034	6' x 6' Pine Garden Shack	Shack	880	951	120	330

Download the workbook called Burnside Storage.xlsx from D2L. Professor Burnside needs the worksheet to provide the following analysis:

1. Professor Burnside needs to know the cost of the inventory, the annual sales, cost of goods sold, annual gross profit and markup percentage for each item.

2. Professor Burnside wants to know the sum, average, maximum and minimum for cost of the inventory, annual sales, cost of goods sold, and annual gross profit.

3. Professor Burnside wants the worksheet to flag any item that has a markup of 25% or less so he can decide if the price needs readjustment. The formula for Markup is =(Price-Cost)/Cost

4. Professor Burnside wants to identify the top five best selling items.

5. Professor Burnside wants to see a pie chart showing the percentages each item makes up of gross profit margin.

6. Professor Burnside wants a line chart comparing the inventory costs of the items.

7. Professor Burnside needs a bar chart that compares Cost of Goods Sold with Annual Sales.

8. Professor Burnside wants a pivot table of the data. The column heading is type. The value to be averaged is Annual Gross Profit.

9. Upload the completed file to the Excel Project Dropbox.

Note: A pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. A pivot-table can automatically sort, count total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data.

All dollar values are to be formatted to currency. All columns that have percentages are to be formatted using percentage with two decimal places. The header of the worksheet must include the business name and be date/time stamped. The footer must contain the name of the creator of the workbook.

Answer & Explanation Solved by verified expert

See Answer