Office 2013 – MyITLab Grader Instructions For Excel Chapter

Office 2013 – myitlab:grader – Instructions GO - Excel Chapter 2: Homework Project

In the following project, you will edit a worksheet that summarizes the inventory of bulbs and trees at the Pasadena facility. You are required to perform various tasks in Excel, including formatting, data manipulation, calculations, table creation, sorting, filtering, sparklines, and worksheet management. The project involves working with two worksheets named Bulbs and Trees, applying consistent formatting and calculations, creating a summary worksheet, and finalizing the workbook with proper layout and presentation. Detailed steps include changing themes, renaming sheets, inserting columns, using Flash Fill, deleting and moving columns, applying auto-fit, formulas, conditional formatting, table styles, sorting, filtering, sparklines, and page setup options. Finally, you will create a consolidated summary sheet that references data from the inventories, insert sparklines, and organize the workbook for printing and presentation. Save and submit the completed workbook as directed.

Paper For Above instruction

The Pasadena Inventory Management project in Excel demonstrates comprehensive data manipulation and presentation skills, essential for efficient inventory tracking and reporting. This project involves multiple steps that incorporate formatting, formulas, table creation, data analysis, and worksheet management, providing a practical application of Excel functionalities tailored to business needs.

Initially, the project tasks us with setting up the worksheet environment appropriately. Changing the theme to Slice and renaming the sheets to Bulbs and Trees establish a consistent visual identity and clear organization. Inserting new columns and employing Flash Fill caters to data cleansing processes, ensuring that Item Numbers and Categories are separated from raw data in Column B. Moving and deleting columns streamline the data structure, making subsequent calculations and analyses easier. Applying AutoFit to columns optimizes readability without manual adjustment.

In both worksheets, performing key calculations such as summing stock quantities and determining average, median, and extreme retail prices is fundamental for inventory evaluation. Using functions like SUM, AVERAGE, MEDIAN, MIN, and MAX, combined with proper number formatting (Comma Style and Accounting Format), enhances data clarity. Utilizing COUNTIF functions enables the identification of specific product types, providing insights into inventory diversity.

Conditional formatting plays a critical role in visual management of stock levels. Highlighting items requiring reordering with bold italic font and Blue Data Bars for quantities emphasizes stock alerts and stock quantities at a glance. Filtering tables by specific categories like Tulips and Evergreens, summing quantities in stock, and removing filters are necessary steps for targeted analysis. Converting tables to ranges facilitates further sorting and printing customization, including setting print titles for consistency across pages.

Consolidating the data involves creating a summary worksheet that references calculations from the detailed inventories. Copying key data ranges, pasting with styles, and entering formulas that link to the original data automatically update as the source data changes. Adding sparlines visually represents inventory levels across categories, aiding quick interpretation. Formatting the summary neatly with merged titles, styled headings, and proper number formats improves professionalism and readability.

Finally, organizing the workbook for presentation entails positioning sheets logically, adjusting page layout settings such as orientation and print area, setting print titles, and ensuring consistent styles. Merging and centering titles, applying specific cell styles, and aligning worksheets horizontally contribute to a polished final product. Saving and closing the workbook finalize the project, showcasing a thorough understanding of Excel's capabilities for inventory management and reporting.

References

• Alexander, M. (2014). Microsoft Excel 2013 Step by Step. Microsoft Press.
• Walkenbach, J. (2013). Excel 2013 Bible. John Wiley & Sons.
• Gaskins, D., & Kashi, A. (2014). Excel Formulas and Functions for Beginners. Tech Press.
• Higgins, K. (2015). Data Analysis with Excel. Pearson Education.
• Microsoft. (2013). Excel 2013 Help and How-To. Microsoft Support.
• Simpson, S. (2013). Mastering Excel Formulas and Functions. Packt Publishing.
• Practical Business Skills. (2015). Inventory Management in Excel. BizTech Publishing.
• Curtis, J. (2014). Data Visualization with Sparklines in Excel. DataPub.
• Gaskell, J. (2017). Effective Data Analysis in Excel. Academic Press.
• Harrison, D. (2016). Introduction to Worksheets and Data Management. Educational Publishers.