Category: Excel

Excel 2021 Skills Approach – Ch 3 Challenge Yourself 3.3 Excel 2021 Skills Approach – Ch 5 Skill Review 5.2

Steps to Perform: Step Instructions 1 Open the file named Excel Assignment #4. Save the file as Your last nameExcel Assignment #4. 2 Go to the Health worksheet and we want to create a frequency table to see the results of the Health Survey. Create a frequency distribution to determine how often respondents replied Poor Fair Good Excellent Use the method we reviewed in class copy the column of data to column D and go to Data/DataTools/Remove Duplicates 3 Add a sum to add up the total of Excellent, Fair, Poor, and Good, to confirm that the frequency numbers add to 150. 4 We want to create a Centering of the title Quality over the data columns, changing the title to Quality Survey Results, merging the title over cells D and E, increasing the font size, and applying a different font color will make it stand out as a title. Merge and center the title over the range D1:E1, change the font size to 20, and apply dark Blue Fill color and white font color. Make the title Bold. 5 We want to visually inspect the survey responses. Create a Clustered Column chart. Select the data in D2:E5 and insert a clustered column chart. 6 We want the clustered column chart to be more visually appealing. Add a title and make it Bold. Select the horizontal axis and make the labels Bold and size 14 font. Move the chart to a new worksheet called Charts/Graphs. 7 Open the Friends worksheet. We want to create a contingency table with Male or Female as the column headings and Feasible: Yes or No as the row heading. Insert a pivot chart and make Gender (Male or Female) as the columns and Feasible (Yes or No) as the row heading. We want to count how many students answered yes or no, so use Count of Students as the Value Field Setting. 8 Pivot tables are good, but we want a better visual! Create a stacked column chart using the data in the pivot table. Select the data in the body of the Pivot Table and insert Stacked Column Chart. 9 We want the chart to be more visually appealing. Add a Bold Centered Title of Friends or Not Just Friends. Using Chart Elements, Data Labels, add the number of respondents to the columns of the chart. Then move the Chart to the Charts/Graphs worksheet. 10 Open the Midterm Grades worksheet. We want to see if there is a relationship between Midterm and Final grades. Select the data and insert a scatter plot. Make the plot more appealing by changing the scale on both the horizontal and vertical axes to range from 25 100 for Midterm and 40-100 for Final Exam. Make the axes numbers Bold Add a title of Relationship of Midterm and Final and make it bold and size 20 font. Add a Gradient Fill to the chart (the background of the chart) (Use chart options, Format chart, Fill, Gradient) Move the chart to the Charts/Graphs worksheet. 11 Open the High School SAT worksheet. Construct a bubble plot that shows math score on the x-axis, writing score on the y-axis, and number of test takers as the size of the bubble. 12 We want to make the Bubble Chart more appealing. Change the horizontal and vertical axes to be scaled better minimum of 300 and maximum of 700. Add a title of Relationship of Size of School to SAT scores and make it BOLD and centered. Move the chart to the Charts/Graphs worksheet. 13 We want to see if there is a relationship between math and writing scores. Construct a scatterplot that shows math score on the x-axis and writing score on the y-axis. Use different colors or symbols to show whether the high school is a private or a public school. You should sort by Public or Private and then Insert Scatter Plot, then Select Data and call the series Public and select Math and Writing scores, then Private and select those Math and writing Scores 14 We want to make the Scatterplot more appealing. Change the range of numbers on the axes to 300-700 and make the numbers Bold. Add a title of Relationship of Math and Writing and make it Bold and centered. Move the chart to the Charts/Graphs worksheet. 15 Open the TShirts worksheet. Construct a contingency table that shows the total quantity sold for each color and size combination. Insert Pivot Table, move Size to Columns and Color to Rows and Quantity to Sum and change Value Field Settings to Count. 16 We still would like to make this more visually appealing! Create a heat map using the pivot table results. Select the data in the pivot table. Go to Home, Conditional Formatting, Color Scales, and choose one you like. Make the titles and labels more appealing and move the heat map to the Charts/Graphs worksheet. 17 Open the Country worksheet. We want to compare the average returns for Latin America and Canada. We want to also compare the riskiness of the funds for each country. Calculate the mean and standard deviation of the funds for each country and then compute the Sharpe Ratios. 21 Save and close file. Exit Excel. Submit the file as directed. 22 Have a great break this is your last Excel Assignment!

Scenario

You have been tasked to manage the inventory at your company. Your boss thinks that inventory management is out of control, but is not sure and wants you to perform some analysis of the inventory and provide him with some data. This chart lists your current stock, annual volumes, and unit cost.

Use Excel OM and the chart to conduct an ABC analysis to determine which category each of the parts falls into. Provide analysis and interpretation of the results.

In the Excel OM software, the default Percent of dollar volume for A items is 75%, however, it has been determined by your boss that he would like to see the data for the A items set at 80%.

Use Excel OM to calculate the EOQ and the reorder point for part number 1359 (check reorder point and graph when setting up the software).

The lead time to reorder parts is 30 days. The set up/ordering cost is $1,000 per order, and the holding cost is fixed at $125 per month (hint: be careful here). The factory runs 365 days per year, and the company wants to maintain a safety stock of 50.

Provide analysis and interpretation of the results.

In Step 2, you were given the safety stock of 50; however, this can be calculated for a more accurate number. Assume that the average daily demand for part number 1359 is 30 with a standard deviation of 2 parts per day. If the company wants to make sure that they do not stockout more than 5% of the time, then what level of safety stock should they hold?

As part of your analysis of these results, how does the safety stock calculated here compare to the safety stock requirement in step 2?

To calculate the safety stock, use Excel OM and the Safety Stock (Normal Distribution) spreadsheet. Input your data into column H of the model titled Model: Daily Demand and its standard deviation given.

Be sure to create only one spreadsheet – each activity should be on a separate tab.

Submit your spreadsheet. Save your assignment using a naming convention that includes your first and last name and the activity number (or description). Do not add punctuation or special characters.

Do not forget to add a detailed analysis for each of your problems. Your analysis should be inserted on each spreadsheet.

take the xlx file and complete it please!