INFO 2130 – Spring 2021 Optional Final Exam Total points: 100 Instructions: Download and rename the macro-enabled Excel start file using your NinerNet ID

Subject:MS ExcelPrice:19.99 Bought4

Share With

INFO 2130 – Spring 2021

Optional Final Exam

Total points: 100

Instructions:

• Download and rename the macro-enabled Excel start file using your NinerNet ID.
• Complete all the problems listed below in appropriate worksheets.
• Save your file often.
• Upload the complete file to Canvas.

Problem 1. Basic Formula & Goal Seek (14 Points)

Use the Problem-1 worksheet

1. Create a 2-D pie chart based on the non-adjacent range A5:A12 and F5:F12. Modify the chart as described below:
   1. Resize and reposition the chart so that the upper-left corner is located within cell H4 and the lower-right corner is located within cell O22.
   2. Apply Chart Style 3 to the chart.
   3. Enter 2022 Budget by Department as the chart title.
2. In the 2018 Budget by Department 2-D pie chart (located in the range H23:O43), make the following changes:
   1. Change the data labels to display only the percentage and a label position of Center.  (MAC users remember after a change you must click “Reset label text’)
   2. Reposition the legend on the right side of the chart.
3. Update the Departmental Budget Comparison 2018-2022 combo chart in the range A15:G36 as follows:
   1. Change the minimum bound of the right vertical axis to 40,000,000.

Add axis titles to the chart. Use Department Budgets as the left vertical title, use Total Budget as the right vertical title, and remove the horizontal axis title.

 

Problem 2. LOOKUP (8 points)

Go to the Problem-2 worksheet.

1. Enter a formula in cell M7 using the VLOOKUP function to find an exact match for the department abbreviation. Look up the department abbreviation (cell M6) using an absolute reference, search the staff table data (the range A6:J13) using an absolute reference, and return the department name (the 2nd column).
2. Copy the formula in cell M7 to the range M8:M15, without formatting, and edit the copied formulas to return the value from the column indicated by the label in column L.

 

Problem-3-5)

Edward Joes is a financial consultant with West Monroe Consulting in Chicago, Illinois. He is working with JBL Technology, a company that sells models of portable speakers to consumers in North America. In an Excel workbook, Edward is analyzing the performance of each speaker model and projecting sales for a new product. He asks for your help in completing the analysis.

Problem-3) Solver and Minimizing (12 points)

In the Problem-3 worksheet where Edward wants to determine whether subcontracting could reduce the cost of the new virtual assistant product.
Run Solver to solve this problem as follows:

1. 1. Set the objective as minimizing (Min) the total cost (cell E10).
   2. Use the units produced values (range B5:D5) as the changing variable cells.
   3. Adjust the number of units produced by each supplier using the following constraints:
      · Set the total number of virtual assistants produced (cell E5) as greater than or equal to 11,000, JBL Technology's minimum production goal.
      · Set the total cost (cell E10) to be less than or equal to 925,000, the maximum total cost JBL Technology wants to spend.
      · Set the total number of virtual assistants produced by a single supplier (range B5:D5) to be less than or equal to 4000 to balance the production among the suppliers.
      · Make sure the values in the range B5:D5 are integers since JBLTechnology cannot sell a fraction of a product.
   4. Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model in the range A14:A21. 

 

Problem-4) PivotTable, Table Sorting and PivotChart (25 points)

Go to the Problem-4 worksheet.

1. Edward wants to examine the January, 2022 sales by country and channel. Create a PivotTable based on the Sales table as follows:
   1. On a new worksheet, insert a PivotTable based on the Sales data (A4:G39), and use January Pivot as the name of the worksheet.
   2. Display the Channel Type values as column headings.
   3. Display the Location values as row headings.
   4. Sum the Amount values
2. Format the PivotTable as follows to make it easier for Edward and others to interpret:
   1. Apply the Currency number format with 0 decimal places and the $ symbol to the Sum of Amount values.
   2. Use Sales (000s) as the custom name of the Sum of Amount field.
   3. In cell A4, use Country to identify the row headings.
   4. In cell B3, use Channel to identify the column headings.
   5. Change the PivotTable style to Light Blue, Pivot Style Light 16.
3. Edward wants to isolate sales for each product. Insert a slicer as follows to filter the PivotTable:
   1. Insert a slicer based on the Product field.
   2. Move and resize the slicer so that it covers the range F3:G12.
4. Edward also wants to compare the sales data by country in a visual format, and then display data only for the Mini 1, the company's best-selling product. Create a PivotChart as follows:
   1. Insert a Stacked Column PivotChart based on the data in the PivotTable.
   2. Move and resize the PivotChart so that it covers the range A10:E25.
5. Format and filter the PivotChart as follows to meet Edward’s requests:
   1. Change the colors of the PivotChart to Monochromatic Palette 1 to coordinate with the PivotTable.
   2. Use the slicer to display sales data for only the Mini 1 in the PivotTable and PivotChart.

 

Problem-5) Scenario Manager (12 points)

1. Go to the Problem-5 worksheet, which calculates the profit from each model of portable speaker. Edward wants to compare three scenarios: one with the current prices and costs, another with the prices raised $10.00, and a third that reduces the variable costs per unit by $5.00. He has already created the first two scenarios and defined names for cells and ranges.
   Create a third scenario as follows:
   1. In the Scenario Manager, add a scenario using Reduced Costs as the name.
   2. Accept the same changing cells (nonadjacent ranges B6:F6 and B11:F12) as the other two scenarios.
   3. Reduce each variable cost per unit value (Mini1_Variable_Cost, Mini2_Variable_Cost, VoiceXP_Variable_Cost, VoiceXP10_Variable_Cost, and Waterproof_Variable_Cost) by $5.00.
   4. Create a Scenario Summary report that summarizes the effect of the three scenarios. Use the profit per unit sold (range B17:F17) as the result cells.

Problem-6-8)

Lecount Davis is a financial analyst at W&K Engineering in Honolulu, Hawaii. He is using an Excel workbook to analyze the financial data for engineering projects, recent revenue, and funding for additional construction equipment. He asks for your help in analyzing data, making financial calculations, and correcting errors.

Problem-6) Advanced Functions (10 points)

Go to the Problem-6 worksheet.

1. Lecount needs to identify the number of projects that have an estimate of more than $50,000. In cell J6, create a formula using the DCOUNT function to count the number of projects with estimates of more than $50,000, using the data in the entire Projects table (Projects[#All]) and counting the values in the column of estimates ("Estimate") that are equal to the values in the range I9:I10.
2. Lecount also needs to calculate the total amount paid by client W-0338 for engineering projects. In cell J10, create a formula using the DSUM function to calculate the total paid by client W-0338, using the data in the entire Projects table (Projects[#All]) and totaling the values in the Paid to Date column ("Paid to Date") that are equal to the values in the range I13:I14.

 

 

Problem-7) Macro (9 Points)

1. 1. Lecount created a macro in the Problem-6) worksheet named CopyStats to copy the project statistics in the range I3:L22, and then paste them on a new worksheet. When he runs the CopyStats macro, however, it includes extra data from column G. View and edit the macro as follows:
      1. 1. Open the CopyStats macro in the Visual Basic Editor. The macro should select and copy the range I3:L22.
         2. In the CopyStats macro VBA code, change the range specified in the first line of code after the comments (Range("G3:L22").Select) to use I3:L22 as the range.
         3. Save and close the CopyStats macro.
         4. Run the CopyStats macro.
         5. Use Project Stats as the name of the new worksheet the macro created.

Problem-8) NPV and IRR (10 points)

1. Go to the Problem-8 worksheet. Besides using a bank loan to fund their upcoming expansion, W&K Engineering is looking for investors. This worksheet should show the returns potential investors could realize if they invested a total of $400,000 in the company. Lecount knows that a desirable rate of return would be 9 percent. He also estimates the investment would pay different amounts each year (range B6:B14). Calculate the present value of the investment and the internal rate of return as follows:
   1. In cell B18, enter a formula that uses the NPV function to calculate the present value of the investment in funding the company's expansion.
   2. Use the target rate of return value (cell B17) as the rate argument.
   3. Use the payments in Years 1–10 (range B5:B14) as the returns paid to investors. (Hint: If a Formula Omits Adjacent Cell error warning appears, ignore it.)
   4. In cell B20, enter a formula that uses the IRR function to calculate the internal rate of return for the investment.
   5. Use the payments for Years 1–10 (range B5:B14) as the returns paid to investors.

Final Figures:

Problem 1:

Problem 2:

Problem 3:

January Pivot:

Scenario Summary:

Problem 6:

Project Stats:

Problem 8: