In-Class Exercise: Graphing and Solver

We will practice graphing and using the Solver function in Excel. The data we will use is an expanded edition of the preclass construction surplus store. You can access the data here: (Starter-Workbook)-Class-Graphing-and-Solver.xlsx

Make sure to rename the starter sheet something like “[Your Name]-Class-Graphing-and-Solver.”

Exercise #1- Graphing Sales Data

This data contains information about specific sales, including the date, region, product, sales associate and more. You are tasked with creating various graphs to visualize the data. Make sure that each chart is created on its own sheet and is titled appropriately. Each chart should have the x and y-axis labeled and have a legend.

Navigate to the first sheet in the data workbook, "Construction_Sales" which is the data that we will be graphing.
Create a pivot table that aggregates total sales by product. Then make a pie chart that shows the total sales by product.
Your boss wants to know the relationship between quantity sold and revenue. Make a scatter plot (markers only) that shows the units sold vs total sales (does not require a pivot table). Does higher quantity sales lead to higher revenue?
Create a pivot table that aggregates total sales by region. Then create a bar graph that shows the average total sales by region. With this graph, we are looking to see which regions are performing the best in terms of sales.
Create a pivot table that aggregates sales first by year and then by month. Make a line graph that shows the total monthly sales trend over time. (Include a trendline to show the overall trend.) This will help us understand how sales are changing over time.

The goal of this exercise is to practice creating different types of graphs in Excel and to understand how to visualize data effectively, so feel free to experiment with different colors and styles!

Exercise #2- Using Solver in Excel (with Goal Seek)

Navigate to the second sheet in the Graphing-and-Solver workbook, "Polynomial Solver" which is designed to solve a polynomial equation using both Solver and Goal Seek.

This sheet is designed to solve a polynomial equation of the form:

\(y = ax^4 + bx^3 + cx^2 + dx + e\)

The user enters the coefficients a, b, c, d, e in cells D11:D15. For the coefficients in the sheet, we are solving:

\(y = x^4 - 3x^2 + 0.6\)

Graphing the Polynomial

The table just below the coefficients shows a set of x values and the corresponding y values using the polynomial equation with the specified coefficients. Make a chart of the data using the XY Scatter - Smooth Lines chart type.

Solving the Polynomial

Note the X and Y cells in F12:F13. You can enter any X value in cell F12 and the corresponding Y value will be computed in cell F13 based on the coefficients and the polynomial equation. Using these cells, follow each of the three instructions on the sheet to solve for various features of the polynomial using Goal Seek and solver. Copy-paste the answers you find into the cells indicated.

Turning in/Rubric

REMINDER - For this class, you will only turn in the links to your Excel files. You will get a 0 for this assignment if you turn in an Excel file or a link that is not shareable.

On the top right, click the share button --> share --> settings
Click "anyone" at the top, then underneath "More settings", change "can view" to "can edit". Then click apply.
Copy the link, then turn it into Learning Suite in the feedback box for that assignment.

Rubric:

Item	Points Possible
Total	5

The following is not a part of the rubric, but specifies how you can lose points. For example: if you fail to share your link correctly.

Reasons for Points Lost	Amount
Link shared incorrectly	-10%
Turned in late (per week)	-10% (up to -50%)