Excel Expertise for MGMT 2400

I need assistance with Goal Seek and Pivot Tables in Excel for my MGMT 2400-x02 course.

Ideal Skills and Experience:
- Proficiency in Excel, specifically in Goal Seek and Pivot Tables
- Experience in academic or professional Excel applications
- Ability to explain concepts clearly and provide step-by-step guidance

Direction
Excel Assignment 13 – Goal Seek and Pivot Tables Review – Instructions

For this assignment, you will be reviewing the Goal Seek and the Pivot Table assignment. There will be no step-by-step video instructions.

Please download the following Excel file for this assignment.

S13 Goal Seek and Pivot Tables Review -1.xlsx



Breakeven Tab (using Goal Seek Instructions)

Using the formulas to determine revenue, COGS, Gross Profit, and Net Profit, use Goal Seek to find out the number of units needed to be sold (for scenario 1) and then the price that needs to be charged (for scenario 2).

Build out your formulas to determine breakeven. Revenue, COGS, Gross Profit, and Net Profit.
Scenario 1, you will determine the minimum number of units needed to be sold to break even with the following information.
The price is $179.50
The variable Cost is $22.36
Fixed Costs are $4,250.29
Determine the quantity that needs to be sold to break even.

Scenario 2, Determine the price of the product that needs to be charged to break even using the following information.
Units Sold 1,056
Variable Cost per unit $65.20
Fixed Cost $35,789.25


PMT Tab (using PMT function and Goal Seek)

Using the PMT function in Excel and Goal Seek, determine the following:

Find the loan amount (mortgage) that has a payment of $2,875 per month,
at 6.65% annual interest,
on a 30-year loan.
Remember to build out the information and use the PMT function before using goal seek to find the answer. Also, remember to make sure that the inputs are using the same “units of time” or “time periods,” such as days, months, or years.



Contoso Tab (using Pivot table to determine a course of action)

For this assignment, you should be creating four pivot tables and then determine a course of action.

Scenario: Contoso, inc. produces microchips.

They track five types of defects (see H2:I9) that have been known to occur. Microchips are manufactured by two operators (Bob and Pat) using four different machines (1–4). You are given data about a sample of defective chips, including the type of defect, the operator, the machine number, and the day of the week the defect occurred.

Use this data to chart a course of action that would lead to improved product quality as quickly as possible.

(i.e., you don't have time to fix everything, so you're going to need to prioritize).

You should start by using PivotTables to “stratify” the defects with respect to; type of defect, day of the week, machine used, and operator working. You might even want to break down the data by machine, operator, and so on. Assume that each operator and machine made an equal number of products.

Types of Defects

Defect 1 - Human contamination (i.e., hair fell on it)

Defect 2 - Etched too deep

Defect 3 - Cut too thin

Defect 4 - Cooked too long

Defect 5 - Oxidized (came in contact with air)

Determine the following:

Which operator is having the most defects and needs to possibly be re-trained?
Which machine is having the most defects, and what is the defect?
Does the machine need to be recalibrated?
What day of the week has the most defects, and what defect is occurring most often on that day?
When completed, please rename and save your file, take the quiz, then upload the completed file.