Programming Homework Help

After graduating from college, you and three of your peers founded the software company Tech Store Unlimited (TSU). TSU provides an online market place that fosters business-to-business (B2B), business-to-consumer (B2C), and consumer-to-consumer (C2C) sales. As one of the company’s principal owners, you have decided to compile a report that details warehouse information, orders, inventory, and facility management. To complete the task, you will duplicate existing formatting, utilize various conditional logic functions, complete an amortization table, visualize data with PivotTables and 3D Maps, connect and transform several external data sources; finally, you will inspect the workbook for issues.

To start your report, you want to ensure formatting is consistent between worksheets. You also want to define range names for later use in what-if analysis.

1. Open the file name eApp_Cap2_TechStore and save it as eApp_Cap2_TechStore_LastFirst.

2. Group the Employee_Info and New_Construction worksheets in the workbook and fill the range A1:E1 from the Employee_Info worksheet across all worksheets including the formatting.

3. Make the New_Construction worksheet active and create range names based on each item in the range A6:B9.

4. Save the workbook.

You want to calculate employee 401K eligibility. Any FT employee hired before 1/1/19 is eligible. You will also add formatting to visually indicate eligible employees. To complete the task, you will use conditional logic and conditional formatting.

5. Ensure the Employee_Info worksheet is active.

6. Click cell G6 and enter a nested logical function that calculates employee 401K eligibility. If the employee is full time (FT) and was hired before the 401k cutoff date of 1/1/19 (cell H3), then he or she is eligible and Y should be displayed; non-eligible employees should be indi-cated with an N.

7. Use the fill handle to copy the function down without formatting, completing the range G6:G25.

8. Apply conditional formatting to the range G6:G25 that highlights eligible employees with Green Fill with Dark Green Text color.

9. Save the workbook.

You want to create a custom lookup function that enables you to locate employee information based on user-defined criteria and employee number. You also want to use conditional math to analyze part-time (PT) salaries. To complete these tasks, you will use data validation; a nested INDEX/MATCH function; and the conditional match functions MAXIFS, SUMIF, COUNTIF, and AVERAGEIF.

10. Create a Data Validation list in cell J7 based on the employee ID’s located in the range A6:A25. Add the input message Select Employee ID and use the Stop Style Error Alert.

11. Use the Data Validation list in cell J7 to select Employee_ID 31461 and select Salary in cell K6 to test the function.

12. Enter a nested INDEX and MATCH function in cell K7 that examines the range B6:H25 and returns the corresponding employee information based on the match values in cell J7 and cell K6.

13. Enter a conditional math function in cell K14 that calculates the total number of PT employees.

14. Enter a conditional math function in cell K15 that calculates the total value of PT employee salaries.

15. Enter a conditional math function in cell K16 that calculates the average value of PT employee salaries.

16. Enter a conditional math function in cell K17 that calculates the highest PT employee salary.

17. Apply Currency Number Format to the range K15:K17.

18. Save the workbook

You want to determine additional statistical information about your full-time (FT) employees. You plan to use advanced filtering to create an isolated table of full time employees. You also will use Database functions to calculate total number of FT employees and additional summary statistics.

19. Click cell K11 and type FT. 20. Click cell A28 and type Full Time Employees.

21. Use the Format Painter to apply the formatting from the cell A3 to the range A28:B28.

22. Use advanced filtering to restrict the data to display only FT employees based on the criteria in the range K10:K11. Place the results in cell A29.

23. Enter a database function in cell K18 to determine the total number of FT employees.

24. Enter a database function in cell K19 to determine the total value of FT employee salaries.

25. Enter a database function in cell K20 to determine the average FT employee salary.

26. Enter a database function in cell K21 to determine the highest FT salary.

27. Format the range K19:K21 with Currency Number Format.

28. Save the workbook.

As part of your analysis, you will evaluate the cost of adding a new facility. You will use what-if analysis tools to aid in your assessments.

29. Ensure that the New_Construction worksheet is active.

30. Use Goal Seek to reduce the monthly payment in cell B6 to the optimal value of $8000. Complete this task by changing the Loan amount in cell E6.

31. Create the following three scenarios using Scenario Manager. The scenarios should change the cells B7 and B8. Good B7 = 0.0312 B8 = 5 Most Likely B7 = 0.0575 B8 = 5 Bad B7 = 0.0625 B8 = 3

32. Create a Scenario Summary Report based on the value in cell B6.

33. Format the new report appropriately and reorder the worksheets so the Scenario Summary worksheet appears as the last worksheet in the workbook.

34. Save the workbook.

To continue your analysis of facility expenditures, you want to complete an amortization table detailing payment, principal, interest, cumulative principal, and cumulative interest.

35. Ensure that the New_Construction worksheet is active

36. Enter a reference in cell B12 to the beginning loan balance.

37. Enter a reference in cell C12 to the payment amount.

38. Enter a function in cell D12 based on the payment and loan details that calculates the amount of interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. All results should be formatted as positive numbers.

39. Enter a function in cell E12 based on the payment and loan details that calculates the amount of principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references and ensure the results are positive.

40. Enter a formula in cell F12 to calculate the remaining balance after the current payment. The remaining balance is calculated by subtracting the principal payment from the balance in column B.

41. Enter a function in cell G12 based on the payment and loan details that calculates the amount of cumulative interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references and ensure the results are positive.

42. Enter a function in cell H12 based on the payment and loan details that calculates the amount of cumulative principal paid on the first payment. Be sure to use the appropriate ab-solute, relative, or mixed cell references. All results should be formatted as positive numbers.

43. Enter a reference to the remaining balance of payment 1 in cell B13.

44. Use the fill handle to copy the functions created in the prior steps down to complete the amor-tization table. Expand the width of columns D:H as needed.

45. Save the workbook.

You want to create a PivotTable to analyze sales information. To complete this task, you will use Get & Transform to connect and transform the data. Then you will use 3D Maps to create a geo-graphic visualization of warehouse information. MAC TROUBLESHOOTING: The standard installation of Excel for Mac does include drivers for importing SQL databases but does not include drivers to import data from Access. To import data from Access, third-party ODBC drivers must be purchased and installed. The required drivers can be downloaded. For more information search ODBC drivers that are compatible with Excel for Mac on https://support.office.com.

46. Use Get & Transform to connect and open the Orders table in the eApp_Cap2_Orders.accdb database. Use the Power Query editor to format column A with Date number format and load the table.

47. Rename the worksheet Orders and move the worksheet to the right of the Scenario Summary worksheet.

48. Adapt Steps 46 and 47 to connect and load the Warehouse table.

49. Use PowerPivot to create a PivotTable based on the Inventory table in the eApp_Cap2_Orders.accdb database. Place the PivotTable on a new worksheet.

50. Create the following relationships:

51. Add the following fields to the PivotTable:

52. Insert a Slicer based on Location. Place the upper left corner of the Slicer in cell F3.

53. Create a 3D Map that displays the location of all warehouses. (On a Mac, 3D Maps is not available.)

54. Rename the worksheet Inventory and move the worksheet to the right of the Orders worksheet.

55. Save the workbook. Use the Data Analysis ToolPak After connecting the external sources using Get & Transform, you want to use the Data Analysis ToolPak to calculate summary statistics on the Orders worksheet.

56. Make the Orders worksheet active.

57. Load the Data Analysis ToolPak Add-In, if necessary.

58. Use the Data Analysis ToolPak to output Descriptive Statistics including Summary statistics starting in cell G3. The statistics should be based on the quantity of orders located in the range E1:E50. Be sure to include column headings in the output. Resize columns G:H as needed.

59. Save the workbook.

Use the Macro Recorder and Insert a Form Control Since you imported the order information using Get & Transform, the table will update when refreshed. You want to use the Macro Recorder to create a macro to sort the table by date after the workbook has been refreshed.

60. Enable the Developer tab, if necessary.

61. Use the Macro Recorder to record a macro named Sort. When activated, the macro should sort the Orders table in ascending order by date.

62. Insert a Form Control button in the range G21:I24.

63. Add the label Sort and assign the Sort macro.

64. Save the workbook as a Macro Enabled workbook. Check Workbook for Issues For your final step, you want to inspect the document for accessibility issues.

65. Use the Accessibility checker to inspect for issues. Once located, take the recommended actions to alleviate the issues.

66. Create a footer with your name on the left, the sheet code in the center, and the file name on the right for each worksheet.

67. Save and close the file. Based on your instructor’s directions, submit eApp_Cap2_TechStore_LastFirst.xlsm.