Excel Word Problems Part 1

The 49^th Street Cafe

1. Create a worksheet that includes the following information. Do not use a Microsoft Style for the table you are going to create.

1. You are selling 5 items.
2. The time period is for the week ending August 31, 2019.
3. You need to enter the daily sales for each item for each day of the week.
4. Using the sum function find the total sales for each day, and for each item for the week.
5. Using the average function find the average daily sales for each item.
6. Find the sum of step e. This is what we would call the sales for a typical day. Use this information to find what percent of sales is sold on a typical day. Enter a formula that you would use to answer this question and in an adjoining cell identify the meaning of the value on the worksheet. Dont leave a number off on its own.
7. Create a pie chart that illustrates the sales for each item for the week. Make sure the title includes who, what, when, and the unit of measure of the data. The chart needs data labels and a legend.

Three Friends

1. Create a worksheet that includes the following information. Do not use a Microsoft Style for the table you are going to create.

1. There are 4 employees. Insert the monthly sales for each of them for the third quarter of 2019.
2. Sales are expected to increase by 2.5% for the first month of the fourth quarter of 2019 over the last month of the 3rd quarter. Find the forecasted sales for each person for that month.
3. Find the total sales for each employee for the 3rd quarter.
4. Find the total sales for each month, the quarter, and for the first month of the 4th quarter.
5. Create a stacked bar chart for the company for the 3rd quarter. Months must be on the y axis, employee names in the legend.
6. Properly title the chart. Name of company, subject, time period and unit of measure of the data.
7. Look at the data in the table. Whose sales pattern most closely follows that of the company during the 3rd quarter? Your answer might be 0-4 of them depending on your data.

City Distributors

1. Create a worksheet that includes the following information. Do not use a Microsoft Style for the table you are going to create.

1. You have 5 items you import.
2. Insert monthly sales for the third quarter of 2019. Use numbers from $1000 to $5000 per month per item.
3. Find total sales for each item for the quarter, and for the company each month and for the quarter.
4. Your vendor has agreed to give you a discount for each item you buy depending on the dollar volume of each item for the quarter. For total sales under $3000 you dont get anything for your orders. $3000 - $7500 you get a 2% discount, for $7500 - $11000 you get 2.5% and anything over $11000 you get 2.75%.
5. Add a column next to the existing tabl Label it Discount Earned. Using the vlookup function calculate the discount earned for each item and the total earned by the company. Remember, to do this you need to create a lookup table using the information in step d. You cannot use the vlookup function if you dont create the lookup tabl Label the field headings for the lookup tabl
6. In the next column, you are going to use the weighted average method to predict sales for next month. Use the name of the month for the column label. From prior sales experience, we are 60% confident that this past months sales are a good indication of sales for next month. We are 30% confident about sales from the prior month, and only 10% confident about sales from the first month of the quarter. Using the sumproduct function, determine the expected sales by item for next month. Place the percentages above the months in the table, not below. You have hundreds or thousands of items in real life. You are not going to place the percentages on a worksheet where you cant easily find them to change them if you need to.
7. Calculate the total expected sales for next month.