Excel Fundamentals

This first assignment aims to familiarize you with essential Excel functions and tools for data manipulation, calculations, and organization. Clear communication of your findings is equally important, making business writing a key part of the process. Mastering these skills is crucial for effective data analysis and decision-making in business contexts.

You will work with a USDA weekly retail food sales data for three Western states. The data set includes variables such as state, category, dollars, and unit sales. This exercise will guide you in using Excel functions such as SUM, AVERAGE, COUNT, MAX, and MIN to perform calculations and gain insights from the dataset.

Task

You will use the USDA weekly retail food sales dataset for three Western states, found in the resources below. Once you have the data, follow the instructions below:

Part A: Data Filtering and Descriptive Statistics

1. Filter the data, copy it to a worksheet for further analysis and calculate Total Dollars and Total Unit Sales (SUM function):
   • Using the Filter function dropdown, select Colorado in the state column and the week 2019-10-06 from the date category
   • Copy the heading row and the filtered rows into the Colorado 2019-10-06 worksheet. This is necessary as Excel functions like SUM will include hidden rows from the Filtered data.
   • Use the SUM function to find the overall total sales for the Dollars and Unit sales columns.
2. Insert a column for $/Unit and calculate average $/Unit (AVERAGE function):
   • Insert a column to the right of ‘Unit Sales and add the heading $/Unit. Format the column using “Currency” and two decimal points.
   • Beneath the heading, enter a formula to divide the amount in the Dollars cell to the left by the amount in the Unit sales column to the left (e.g. =F9/E9) . Verify the result is correct (around $3) and then copy that formula for all remaining rows.
   • At the bottom of the column, use the AVERAGE function to calculate the mean $/unit across all categories. Enter the label Mean in the cell to the left.
3. Find Maximum and Minimum $/Unit amounts (MAX and MIN functions):
   • In the cells beneath the Mean label, enter the labels Max and Min.
   • Apply the MAX and MIN functions to find the highest and lowest $/Unit values for the State and date filtered.
4. Save your workbook before continuing and add your last name to the end of the file.

Be sure to name your files as follows:

• Excel Workbook: LastName_Unit1.xlsx
• Word Document: LastName_Unit1.docx

Part B: Data Filtering and Visualization

1. Return to the original By State and Product Category worksheet
2. Create a visualization for Dollars by State for a single product Category for week ending 2019-10-06
   • Use the Filter dropdown arrow in the State column and click on Select All and then OK to reveal all states, but leave the Date filter as is to show all data for all states for the selected week 2019-10-06.
   • Use the Filter dropdown arrow in the Category column to select any single product category .
   • Use the Dollars category to sort the dataset in descending order Largest to Smallest to view which state has the highest total dollar sales for that category.
   • Create a 2-D Clustered Column chart showing Dollars by State
     1. Hold the CTRL key down and select the range of State names with your mouse. Do not include the column name State in your range.
     2. While continuing to hold the CTRL key down, select the range of Dollar values
     3. Release the CTRL key and verify the desired data in the two columns is highlighted
     4. Use the Insert menu and select the Recommended Charts to create a 2-D Clustered Column chart
     5. Edit the chart title to Total Sales by State for Week Ending 2019-10-06
     6. Create a Word document that will be your written summary described in Part C below. Copy the chart and Paste Special to the Word document by right-clicking and choosing the Picture option. Then select the chart in the Word document and use the Layout Options tab that appears to the top right to choose Top and Bottom. This will keep your text paragraph additions above and below the chart.
     7. Alternatively, you can Copy and Paste the chart as a picture within the Excel workbook but the data for the chart will change in the next visualization task so you must do one of these options before continuing.
3. Create a Visualization for Dollars by Category for an Individual State
   • Use the Filter dropdown arrow in the Category column and click on Select All and then OK to reveal all categories, but leave the Date filter as is to show all data for all states for the selected week 2019-10-06.
   • Use the Filter dropdown arrow in the State column to select any single state.
   • Use the Dollars category to sort the dataset in descending order Largest to Smallest to view which category has the highest total dollar sales for that state.
   • Create a 2-D Clustered Column chart showing Dollars by Category
     1. Select the chart and delete it by right-clicking and using the Cut option
     2. Hold the CTRL key down and select the range of Category names with your mouse. Do not include the column name Category in your range.
     3. While continuing to hold the CTRL key down, select the range of Dollar values
     4. Release the CTRL key and verify the desired data in the two columns is highlighted
     5. Use the Insert menu and select the Recommended Charts to create a 2-D Clustered Column chart
     6. Edit the chart title to Sales by Category for [state] for Week Ending 2019-10-06
     7. Go to your Word document and enter 2 or 3 blank paragraphs to make room for the new chart
     8. Copy the chart and Paste Special to your Word document by right-clicking and choosing the Picture option. Then select the chart in the Word document and use the Layout Options tab that appears to the top right to choose Top and Bottom. This will keep your text paragraph additions above and below the chart.
   • Save your workbook.

Part C: Written Summary

In the Word document, write a brief summary (2-3 paragraphs) of your findings from the analysis. Use professional business writing to communicate your insights clearly and effectively. Your summary should be concise, well-organized, and appropriate for a business audience. Note: Copy both of your charts (Dollars by State and Dollars by Category) into this Word document. Use Paste Special > Picture format so the chart’s appearance doesnt change. Place each chart directly below your summary paragraph describing it.

Your summary should include:

• Insights into your charts of states and categories with the highest total sales, including their total sales figures. Put your insights directly above each chart.
• What trends or outliers stand out in your chart? Why might some states or categories outperform others?
• A copy of your Part A work above from the workbook and observations from your $/Unit calculations and Descriptive Statistics calculations of Average, Maximum, and Minimum. Tip: You do notneed to copy the whole table from Excel. Just include the key results in your paragraph – mention the Total Dollars, Total Units, Mean $/Unit, Max, and Min. You may summarize these in your own words rather than pasting the data.
• An assessment of how this data could inform business decisions related to pricing, marketing, or inventory management.
• Reflect on your experience using Excel. Identify one skill you feel confident in after this assignment (e.g., using formulas, formatting charts), and one area you’d like to improve in future assignments.

Struggling with your essay and deadlines?

Get this or a similar paper done in as fast as 4 hours, 24/7.

NB: We do not sell prewritten papers. All essays are written from scratch according to specific needs and instructions

✔Secure Service ✔ Plagiarism Free ✔On-time Delivery

WRITE MY PAPER