Illustrated Excel 2019 | Module 12: End of Module Project 1 Spring Package Express PERFORM WHAT-IF ANALYSES GETTING STARTED Open the file IL_EX19_EOM12-1_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Illustrated Excel 2019 | Module 12: End of Module Project 1

Spring Package Express

PERFORM WHAT-IF ANALYSES

• GETTING STARTED

• Open the file IL_EX19_EOM12-1_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_EOM12-1_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file IL_EX19_EOM12-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• To complete this project, you need to add the Power Pivot tab to the ribbon as follows:
  • From the File tab, click the Options button. In the Data options section of the Data tab, click the checkbox next to Enable Data Analysis add- ins: Power Pivot, Power View, and 3D Map, and click OK.

• PROJECT STEPS

1. Winoma Adebayo is a partner in Spring Package Express, a delivery company in Washington, DC. In an Excel workbook, she is tracking expenses and delivery charges for flat-rate packages. She asks for your help in developing scenarios and what-if analyses that identify ways to reduce costs.
   
   Winoma wants to compare the cost to complete a shipment with the current data, increased costs, and decreased costs.
   Go to the Flat Rates Worksheet. Create a scenario named Current Rates that includes the current data in cells B4:B6 as the changing cells. Create a second scenario named Increased Costs that includes the same changing cells, but change the values as shown in Table 1. Create a third scenario named Decreased Costs that includes the same changing cells, but change the values as shown in Table 1.

Table 1: Scenario Values

 

Item	Increased Costs Value	Decreased Costs Value
Labor per hour (cell B4)	$90.00	$80.00
Supplies per shipment (cell B5)	$12.50	$11.00
Hours per shipment (cell B6)	0.5	0.2

 

2. Winoma asks you to compare the results of the three scenarios. Create a Scenario Summary Report for result cell B8. On the Scenario Summary worksheet, delete column D to avoid repeating the data shown in column E.
3. Return to the Flat Rates worksheet. Winoma wants to determine how increasing or decreasing labor costs will affect the cost to complete each shipment.
   Create a one-variable data table based on the labor cost data in the range D3:E8. Use the labor per hour value (cell B4) as the column input cell. Use a Custom number format to display the text "Cost per shipment" in cell E3 instead of the formula result.
4. In the range A11:B16, Winoma wants to determine what the supplies per shipment cost should be so that the cost to complete each shipment is $30.00. Use Goal Seek to set the cost to complete each shipment (cell B16) to a value of 30. Change the supplies per shipment cost (cell B13) to achieve the goal.
5. In the range A18:B23, Winoma wants to determine what the labor per hour cost should be so that the cost to complete each shipment is $30.00. Use Goal Seek to set the cost to complete each shipment (cell B23) to a value of 30. Change the labor per hour cost (cell B19) to achieve the goal.
6. Go to the Daily Flat-Rate Expenses worksheet. Most of the flat-rate packages the company handles can be delivered within two days, so Winoma knows they can reduce the number of scheduled shipments to as few as 20 per day. She wants to find out how many shipments to schedule to reduce total expenses to $2,100.
   Run Solver and set the objective to set total expenses (cell D4) to a target value of $2,100. Use the scheduled shipments (the range B10:D10) as the changing cells. Specify that the scheduled shipments must be greater than or equal to 20 and that the values are integers (int). Make unconstrained variables non-negative and use GRG Nonlinear as the solving method. Run Solver and keep the solution.
7. Go to the Employees worksheet. Winoma wants to compare the data on this worksheet with delivery data, stored on the Deliveries worksheet. She wants to analyze the delivery charges by manager.
   Add the data in the range A3:D27 to the data model, specifying the table you are creating has headers. Go to the Deliveries worksheet, and then add the data in the range A3:D27 to the data model, specifying the table you are creating has headers.
   In Power Pivot, rename Table1 using Employees as the new table name. Rename Table2 using Deliveries as the new table name. Create a PivotTable on a new worksheet named Deliveries by Manager based on the Employees and Deliveries tables.
8. Modify the PivotTable so that it displays the Location field data from the Employees table as the row headings, the Manager field data from the Employees table as the second row headings, and the Package Type field data from the Deliveries table as the column headings. Display the sum of the Delivery Charge field data as the values. Format the delivery charges as Currency values with 0 decimal places and the $ symbol.
9. Use Power Pivot to create a relationship between the primary Employee table and the related Deliveries table using EmpID as the column that relates the tables.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.