Assume we make 25% profit on each Laptop, 10% on each MS Office Software and Laptop Case and 5% on each Wireless Mouse, how much profit did we make this quarter? What is our total profit margin (total profit / total sales)?

Management
Paper, Order, or Assignment Requirements

Effective information systems add value to data to create meaningful information for management decisions that improve business performance. In this assignment, you will be given some raw sales data for one quarter. You should analyze this data via Excel adding information to answer the questions given below. You will also create a PowerPoint presentation for your management describing your analysis.

1. The sales data is given below:

1,Southeast,1978,MS Office Software,199.99,28,10/27/2009
1,Southeast,1978,MS Office Software,199.99,30,11/24/2009
1,Southeast,1978,MS Office Software,199.99,9,12/29/2009
1,Southeast,2020,Laptop Case,24.99,30,10/27/2009
1,Southeast,2020,Laptop Case,24.99,35,11/24/2009
1,Southeast,2020,Laptop Case,24.99,39,12/29/2009
1,Southeast,5020,Wireless Mouse,26.99,28,10/27/2009
1,Southeast,5020,Wireless Mouse,26.99,3,11/24/2009
1,Southeast,5020,Wireless Mouse,26.99,38,12/29/2009
1,Southeast,5448,Laptop,799.99,25,10/27/2009
1,Southeast,5448,Laptop,799.99,27,11/24/2009
1,Southeast,5448,Laptop,799.99,33,12/29/2009
2,Southeast,1978,MS Office Software,199.99,8,10/27/2009
2,Southeast,1978,MS Office Software,199.99,8,11/24/2009
2,Southeast,1978,MS Office Software,199.99,10,12/29/2009
2,Southeast,2020,Laptop Case,24.99,8,10/27/2009
2,Southeast,2020,Laptop Case,24.99,8,11/24/2009
2,Southeast,2020,Laptop Case,24.99,8,12/29/2009
2,Southeast,5020,Wireless Mouse,26.99,9,10/27/2009
2,Southeast,5020,Wireless Mouse,26.99,9,11/24/2009
2,Southeast,5020,Wireless Mouse,26.99,8,12/29/2009
2,Southeast,5448,Laptop,799.99,18,10/27/2009
2,Southeast,5448,Laptop,799.99,18,11/24/2009
2,Southeast,5448,Laptop,799.99,20,12/29/2009
3,Southeast,1978,MS OfficeSoftware,199.99,38,10/27/2009
3,Southeast,1978,MS OfficeSoftware,199.99,30,11/24/2009
3,Southeast,1978,MS Office Software,199.99,3,12/29/2009
3,Southeast,2020,Laptop Case,24.99,30,10/27/2009
3,Southeast,2020,Laptop Case,24.99,32,11/24/2009
3,Southeast,2020,Laptop Case,24.99,33,12/29/2009
3,Southeast,5020,Wireless Mouse,26.99,25,10/27/2009
3,Southeast,5020,Wireless Mouse,26.99,5,11/24/2009
3,Southeast,5020,Wireless Mouse,26.99,26,12/29/2009
3,Southeast,5448,Laptop,799.99,28,10/27/2009
3,Southeast,5448,Laptop,799.99,27,11/24/2009
3,Southeast,5448,Laptop,799.99,29,12/29/2009
4,Midwest,1978,MS Office Software,199.99,18,10/27/2009
4,Midwest,1978,MS Office Software,199.99,20,11/24/2009
4,Midwest,1978,MS Office Software,199.99,4,12/29/2009
4,Midwest,2020,Laptop Case,24.99,12,10/27/2009
4,Midwest,2020,Laptop Case,24.99,24,11/24/2009
4,Midwest,2020,Laptop Case,24.99,36,12/29/2009
4,Midwest,5020,Wireless Mouse,26.99,29,10/27/2009
4,Midwest,5020,Wireless Mouse,26.99,11,11/24/2009
4,Midwest,5020,Wireless Mouse,26.99,38,12/29/2009
4,Midwest,5448,Laptop,799.99,21,10/27/2009
4,Midwest,5448,Laptop,799.99,24,11/24/2009
4,Midwest,5448,Laptop,799.99,30,12/29/2009
5,Midwest,1978,MS Office Software,199.99,27,10/27/2009
5,Midwest,1978,MS Office Software,199.99,25,11/24/2009
5,Midwest,1978,MS Office Software,199.99,23,12/29/2009
5,Midwest,2020,Laptop Case,24.99,80,10/27/2009
5,Midwest,2020,Laptop Case,24.99,82,11/24/2009
5,Midwest,2020,Laptop Case,24.99,75,12/29/2009
5,Midwest,5020,Wireless Mouse,26.99,65,10/27/2009
5,Midwest,5020,Wireless Mouse,26.99,24,11/24/2009
5,Midwest,5020,Wireless Mouse,26.99,55,12/29/2009
5,Midwest,5448,Laptop,799.99,55,10/27/2009
5,Midwest,5448,Laptop,799.99,57,11/24/2009
5,Midwest,5448,Laptop,799.99,47,12/29/2009
6,Northeast,1978,MS Office Software,199.99,24,10/27/2009
6,Northeast,1978,MS Office Software,199.99,85,11/24/2009
6,Northeast,1978,MS Office Software,199.99,56,12/29/2009
6,Northeast,2020,Laptop Case,24.99,52,10/27/2009
6,Northeast,2020,Laptop Case,24.99,58,11/24/2009
6,Northeast,2020,Laptop Case,24.99,69,12/29/2009
6,Northeast,5020,Wireless Mouse,26.99,35,10/27/2009
6,Northeast,5020,Wireless Mouse,26.99,39,11/24/2009
6,Northeast,5020,Wireless Mouse,26.99,44,12/29/2009
6,Northeast,5448,Laptop,799.99,78,10/27/2009
6,Northeast,5448,Laptop,799.99,88,11/24/2009
6,Northeast,5448,Laptop,799.99,99,12/29/2009
7,Northeast,1978,MS Office Software,199.99,34,10/27/2009
7,Northeast,1978,MS Office Software,199.99,36,11/24/2009
7,Northeast,1978,MS Office Software,199.99,35,12/29/2009
7,Northeast,2020,Laptop Case,24.99,49,10/27/2009
7,Northeast,2020,Laptop Case,24.99,47,11/24/2009
7,Northeast,2020,Laptop Case,24.99,48,12/29/2009
7,Northeast,5020,Wireless Mouse,26.99,45,10/27/2009
7,Northeast,5020,Wireless Mouse,26.99,42,11/24/2009
7,Northeast,5020,Wireless Mouse,26.99,45,12/29/2009
7,Northeast,5448,Laptop,799.99,55,10/27/2009
7,Northeast,5448,Laptop,799.99,57,11/24/2009
7,Northeast,5448,Laptop,799.99,55,12/29/2009
8,Northeast,1978,MS Office Software,199.99,18,10/27/2009
8,Northeast,1978,MS Office Software,199.99,17,11/24/2009
8,Northeast,1978,MS Office Software,199.99,23,12/29/2009
8,Northeast,2020,Laptop Case,24.99,22,10/27/2009
8,Northeast,2020,Laptop Case,24.99,18,11/24/2009
8,Northeast,2020,Laptop Case,24.99,22,12/29/2009
8,Northeast,5020,Wireless Mouse,26.99,14,10/27/2009
8,Northeast,5020,Wireless Mouse,26.99,16,11/24/2009
8,Northeast,5020,Wireless Mouse,26.99,17,12/29/2009
8,Northeast,5448,Laptop,799.99,32,10/27/2009
8,Northeast,5448,Laptop,799.99,28,11/24/2009
8,Northeast,5448,Laptop,799.99,30,12/29/2009

2. Copy this text file to your machine.

3. Start Excel and, using Excel’s text import wizard, read the text file into a spreadsheet. (Data → Get External Data → Import Text File)

Commas delimit the text file. The fields, in order, are: store number, sales region, item number, item description, unit price, units sold and month. For example, here is one sales record:

8,Northeast,5448,Laptop,799.99,30,12/29/2009

There are 96 records on the file.

Name the spreadsheet (.xls file) yourname where you replace yourname with your last name, for example witt.xls

4. You will need to add information to the spreadsheet to supply answers to the following questions:

a. What are the total sales for the quarter?

b. Assume we make 25% profit on each Laptop, 10% on each MS Office Software and Laptop Case and 5% on each Wireless Mouse, how much profit did we make this quarter? What is our total profit margin (total profit / total sales)?

c. Based on total quarterly sales, which is the best performing region and what were the quarterly sales for that region?

d. Based on total quarterly sales, which is the best performing store and what were the quarterly sales for that store?

e. Based on total quarterly sales, what percentage of total sales does each region contribute?

f. Based on total quarterly sales, what percentage of total sales does each store contribute?

g. What are the total sales by month?

h. If we increase our profit margin on each Laptop by 1% and by 5% on the other items, how much does our total profit increase?

You will need to know how to sort in Excel; knowing how to subtotal would also be helpful.

5. Develop a PowerPoint presentation for your management. Name the presentation (.ppt file) yourname where you replace yourname with your last name, for example witt.ppt

a. Make the presentation business-like. Review the suggestions in the Effective PowerPoint handout.

b. Make sure all numbers are formatted properly.

c. Provide a business-like background

d. The first slide should be a title slide. Include the name of your department, the title of your analysis and your name. Include a clipart logo for your company. You can use clipart from the library that comes with PowerPoint.

e. The second slide should contain give the answers to questions 4a, 4b, 4c and 4d. Use bullet points.

f. The third slide should give the answer to 4e. This should be shown as a pie chart showing the region’s name and the sales percentage. Create the pie chart in Excel and copy it to your PowerPoint presentation. Make sure the region and its percent is shown on the chart.

g. The fourth slide should give the answer to 4f. This should be shown as a table in decreasing percentage order (highest percentage should be on top).

h. The fifth slide should give the answer to 4g. This should be shown as a line chart with a linear regression line. Create the chart in Excel and copy it to your PowerPoint presentation.

i. The last slide should give the answers to 4h. Use bullet points. Show both numbers (total profit and total profit margin) before and after the increases.

Submit to the dropbox the .xls and.ppt files. Keep copies for yourself in case something happens to the ones you turn in.

The spreadsheet is worth 50 points. It must contain at least three workbooks:

1. The data that you have manipulated. It should contain any additional data columns you have added (like item sales)

2. The pie chart for regional sales

3. The line chart for monthly sales.

It will be graded on the correctness of the calculations and your use of Excel. I expect you to make calculations using Excel. Your spreadsheet should show some of this work.

The PowerPoint presentation is worth 50 points. It will be graded on its completeness and business like appearance

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"