Question
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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started