Information Systems

Questions

  1. What is the average and total employee count across all offices?

 

Please document the use of the average and/or sum formulas in your supporting excel data on the ‘Q2 Other Expense Data’ worksheet (cells B10 and B11).

 

Please also provide answers below and provide a chart (pie chart) showing breakdown of employees per office. Create your chart in your supporting excel data as a new worksheet named ‘Question 1’. The chart should be linked to the data in the ‘Q2 Other Expense Data’ worksheet.

 

Average Employee Count =

Total Employee Count (all offices) =

 

INSERT PIE CHART HERE

 

 

 

  1. What are three highest selling products according to the Q2 Sales Data?

 

Please create a pivot table using the ‘Q2 Sales Data’ worksheet showing Product ID and Units Sold in your supporting excel data. Use a new worksheet named “Question 2” to document the pivot table.

 

Please also input your answers below in order of highest selling.

 

Product ID Units Sold
   
   
   

 

 

 

 

  1. What are the three most profitable products according to the Q2 Sales Data?

 

Please create a pivot table using the ‘Q2 Sales Data’ worksheet showing Product ID and Profit/unit in your supporting excel data sheet.  Use a new worksheet named “Question 3” to document the pivot table.

 

Please also input your answers below.

 

Product ID Profit/unit
   
   
   

 

 

 

 

  1. What are the three most profitable offices according to the Q2 Sales Data?

 

Please create a pivot table using the ‘Q2 Sales Data’ worksheet showing Sales Office and Total Profit in your supporting excel data sheet and label the new worksheet “Question 4”. Please input your answers below.

 

Sales Office Total Profit
   
   
   

 

 

 

 

 

 

 

 

 

 

  1. Which three offices have the highest level of expenses?

 

Please use the sum formula in your supporting excel data (cells F2, F3, F4, F5, and F6) to document total expenses by office. Please also provide the three offices with the highest expenses below.

 

Please also provide a chart (100% Stacked Column in 3-D) showing the breakdown of expenses for each office (5 offices total with expenses across 4 categories). Create new worksheet called ‘Question 5’ for the chart. The chart should be linked to the data in the ‘Q2 Sales Data’ worksheet.

Three most expensive Sales Offices Total Expenses
   
   
   

 

For the office with the highest expenses, what are the two biggest drivers (which of the 4 expense categories is highest)?

 

Fill in the blanks below to identify the two largest expenses contributing to the most expensive office.

 

The two biggest expense drivers for the _                            _ office are _                         _ and _                        _.

 

INSERT CHART HERE

 

  1. What is the average salary per employee in each respective office (H2, H3, H4, H5, and H6)?

 

What is the average salary per employee across all offices (Cell J10)?

 

Please document the use of the basic math formula in your excel data. HINT: You should not use the ‘Average’ formula but instead use a basic math formula. Please also document the average salary by office in the table below. Also indicate the average salary across all offices below.

 

Sales Office Average Salary
   
   
   
   
   

 

Average Salary across all offices =

 

 

 

 

 

 

 

  1. If one product had to be eliminated, which one and why?

 

Create a pivot table showing Product ID and Profit/unit in your supporting excel data sheet on a new worksheet called “Question 7”. Please also input your answers below.

 

Please also provide a chart (Bar chart) based upon the pivot table results. The chart should be linked to the data in the ‘Q2 Sales Data’ worksheet.

 

Product ID Profit/unit
   
   
   

 

Fill in the blanks below to indicate which product ID should be eliminated and why.

 

Product ID _             _ should be eliminated because _                              _.

 

 

 

 

 

 

 

  1. What is the least profitable office based on ratio of revenues to expenses (i.e., revenue/expenses)?

 

Please create a pivot table in your supporting excel data on a new worksheet called “Question 8” using Sales Office, Revenue, and Expenses. Manually add a final column labeled “Ratio” and document the use of the appropriate excel function in your excel data sheet to calculate the ratio by Sales Office. Please also input your ratios below.

 

Sales Office Ratio
   
   
   
   
   

 

Please indicate which office has the best and worst profitability.

Office with best profitability =

Office with worst profitability =

 

Order from us and get better grades. We are the service you have been looking for.