Excel App Capstone: Tech Store Report And Data Management

Excel App Capstone Tech Store Report and Data Management

Analyze the business operations of TechStore Unlimited (TSU) by creating a comprehensive Excel workbook that covers employee payroll, facility management, sales data, and inventory management. Your task involves duplicating formatting, importing data from an Access database, applying conditional logic functions, performing amortization calculations, visualizing data with PivotTables and Power Maps, connecting to external data sources, and inspecting the workbook for issues. You will also create scenarios, use PowerQuery for data transformation, build PivotTables and 3D maps, perform statistical analysis, and record macros to automate tasks.

Sample Paper For Above instruction

TechStore Unlimited (TSU) is a burgeoning software company providing a comprehensive online marketplace that facilitates B2B, B2C, and C2C sales. As a principal owner, I was tasked with developing a detailed Excel workbook that encapsulates all critical aspects of the business, including employee management, facility operations, sales data, and inventory tracking. This report not only offers insights into current business performance but also demonstrates advanced Excel skills such as data importation, conditional calculations, scenario analysis, data visualization, and automation to support strategic decision-making.

Data Formatting and Preparation

The first step involved standardizing the appearance of the workbook. I began by opening the provided Excel file, named dynamically with the last name, and filled the header row across all sheets with the employee information headers from A1:E1, ensuring consistent formatting for easy readability. On the 'New_Construction' worksheet, I created named ranges based on data in A6:B9, facilitating easier data referencing in subsequent formulas. I ungrouped all worksheets, activated the 'Employee_Info' sheet, and prepared it for the implementation of employee eligibility logic.

Conditional Logic and Data Validation

In cell G6 of the 'Employee_Info' worksheet, I entered a nested IF statement to determine full-time employee eligibility for 401K participation based on their employment status, hire date in H3, and the cutoff date of January 1, 2019. The formula checked if the employee was full-time and hired before the cutoff, returning 'Y' for eligible employees, and 'N' for others. I copied the formula down through G25, highlighting eligible employees with green fill and dark green text using conditional formatting. Additionally, I created a dropdown list for employee IDs in J7 using data validation from the A6:A25 range, and used an INDEX-MATCH combination in K7 to retrieve employee data based on selection, making data retrieval dynamic and interactive.

Statistical and Database Functions

I used COUNTIF, SUMIF, AVERAGEIF, MAXIF functions in cells K14 to K17 to analyze part-time (PT) employee statistics, including total count, total salaries, average salaries, and maximum salary, with currency formatting applied to relevant cells for clarity. To analyze full-time ($FT$) employees separately, I employed Advanced Filter based on criteria in K10:K11, filtering data in A29 accordingly. I then used database functions (DCOUNT, DSUM, DAVERAGE, DMAX) to replicate these calculations, referencing A5:H25 as the database and specifying criteria, ensuring accurate dynamic data summaries that update with filter changes.

Scenario and Goal Seek Analysis

On the 'New_Construction' worksheet, I utilized Goal Seek to determine the loan amount needed to bring the monthly payment down to $8,000, adjusting the value in E6. Additionally, I created three scenarios—'Good,' 'Most Likely,' and 'Bad'—using Scenario Manager, altering the cells B7 and B8 to simulate different interest rates and loan durations. I generated a Scenario Summary report, formatted it properly, and moved it to the last position in the workbook for clarity and presentation purposes.

Financial Calculations and Amortization Table

Next, I set up a detailed amortization schedule. In B12, references linked to loan balance, and in C12, payment amount. Using the IPMT function in D12, I calculated the first payment’s interest based on loan terms specified in E6:E9, appropriately using absolute and relative references. The principal paid was determined by subtracting interest from the total payment in E12, with the remaining balance updated in F12. CUMIPMT and CUMPRINC functions calculated total interest and principal paid up to the first payment, formatted as positive values. I generated subsequent rows by copying formulas down, expanding columns D through H for full schedule visualization.

External Data Integration and Visualization

I connected to an Access database (eApp_Cap_Orders.accdb) via PowerQuery to load the 'Orders' table, formatted date columns appropriately, and renamed the sheet for clarity. Similarly, I linked the 'Warehouse' table and established relationships: one between 'Inventory' and 'Warehouse' on warehouse IDs, and another between 'Inventory' and 'Orders' on item numbers. Using PowerPivot, I created a PivotTable with fields for warehouse location, warehouse name, item number, and inventory metrics, accompanied by a slicer for warehouse filtering. Additionally, I generated a 3D Power Map showing warehouse locations based on city data, for geographic analysis.

Data Analysis and Automation

In the 'Inventory' sheet, I used the Data Analysis ToolPak to generate summary statistics for order quantities, starting at G3, including mean, median, mode, etc., with headers. To automate sorting, I recorded a macro named 'Sort' that sorts the 'Orders' table by date in ascending order, then viewed its code in VBA and embedded it into a module named 'Code.' I assigned this macro to a form control button labeled 'Sort' placed on the 'Orders' worksheet, enabling quick data reordering.

Final Touches and Workbook Inspection

I inspected the workbook using the Accessibility Checker, resolving issues such as changing table styles to 'None' on relevant sheets, correcting font color to black on specific ranges, and ensuring consistent formatting throughout. I saved my completed workbook as 'Exp19_Excel_AppCapstone_Comp.xlsx', verifying all tasks were performed correctly, and then closed Excel for submission.

References

  • Excel Function Reference. (2023). Microsoft Support. https://support.microsoft.com
  • Power Query Documentation. (2023). Microsoft Docs. https://docs.microsoft.com
  • PowerPivot and Data Model. (2023). Microsoft Support. https://support.microsoft.com
  • Access Database Connectivity with Excel. (2023). Microsoft Tech Community. https://techcommunity.microsoft.com
  • Advanced Filtering Techniques. (2023). Excel Easy. https://www.excel-easy.com
  • Scenario Management in Excel. (2023). ExcelJet. https://exceljet.net
  • Financial Functions in Excel. (2023). Investopedia. https://www.investopedia.com
  • Creating Power Maps in Excel. (2023). Microsoft Support. https://support.microsoft.com
  • Macro Recording and VBA Coding. (2023). Excel Campus. https://www.excelcampus.com
  • Accessibility Testing in Office. (2023). Microsoft Support. https://support.microsoft.com

Related Assignments