New Perspectives Excel 2016 Module 4 Sam Project 1

New Perspectivesexcel 2016 Module 4 Sam Project 1anew Perspectivese

Analyze and chart financial data from the provided Excel workbook, including creating histograms, sparklines, pie charts, line charts, stacked column charts, and combo charts. Edit and format charts according to specifications, update axis labels, add data labels, and modify chart styles and layouts. Use formulas to perform loan calculations on the Loan Options worksheet. Save, close, and submit the completed workbook as instructed.

Paper For Above instruction

The analysis of financial data using Excel involves a series of charting, formatting, and data manipulation tasks that illustrate key business insights. This process is central to making data-driven decisions, especially in scenarios where visual representation of complex data sets enhances understanding and presentation for stakeholders such as lenders or management. The project outlined here exemplifies these skills by guiding the creation and customization of various chart types based on specific datasets within an Excel workbook, as well as performing financial calculations using formulas.

The initial step involves manipulating a histogram chart to display the distribution of average weekly usage hours of members at Inception Workspace in 2024. Selecting the data range A4:B243 on the "Average Usage 2024" worksheet, a histogram chart is created to visually represent member activity patterns. The chart must be positioned precisely within the worksheet, with its size and location specified to fit within the cells D4 to K18. When editing the chart, the title "Average Weekly Usage (in Hours) in 2024" must be added, and the bins should be adjusted to a bin width of 10 hours, which simplifies data categorization and makes the histogram more interpretable.

Next, the focus shifts to visualizing the impact of membership packages on annual income. Using the "Annual Income" worksheet, column sparklines are inserted into range H5:H10 based on data in B5:G10, with a specific color fill applied to match corporate branding or aesthetic standards. This visual tool succinctly indicates trends over time within each package category, enhancing the income analysis. Additionally, data bars are applied via conditional formatting in range I5:I10 to represent income magnitude concisely, making polarizing comparisons easier at a glance.

A key component involves creating a pie chart to depict how different membership packages contributed to total income in 2019. Selecting the range A5:B9, a 2-D pie chart is inserted, then formatted to match the specified size and position within K1 to Q13. The chart receives a title, "2019 Total Annual Income by Package," and a particular style, "Style 6," to ensure consistency and visual appeal.

The analysis extends to a 3-D pie chart illustrating the income contribution for 2019 with details such as data labels positioned outside the slices, showing only the percentage contribution with one decimal place. Removing value labels ensures clarity, and positioning the legend at the bottom maintains readability. Data labels are carefully formatted to display percentage values only, enhancing the viewer's focus on proportionate contributions.

Further, a line chart representing total income from 2019 to 2024 is refined by editing axis labels to display categories from B4:G4, which aligns the data presentation with the timeline. The vertical axis's minimum bound is set to 150000 to accommodate the data scale, and gridlines are added to improve the chart's readability. The chart is formatted with a light blue fill for the chart area and styled with Arial font and specific font color for the title, ensuring a professional appearance.

The project continues with modifications to a stacked column chart showing each membership package's income contribution over the years. Removing the "Total" data series improves clarity, and formatting the legend with a white background, blue border, and shadow effect enhances visual separation and emphasis.

Finally, a combo chart is constructed to compare individual package incomes with total income on the same graph. Using the "Range A4:G10," the chart features clustered columns for all individual packages, and a line series for total income on the secondary axis. Properly positioned within the "Income Overview" worksheet, the chart includes descriptive axis titles and a comprehensive main title, facilitating effective communication of financial trends. The process culminates with moving, resizing, and formatting all charts to meet specifications.

To complete the analysis, formulas are entered into the "Loan Options" worksheet to calculate monthly loan payments for different options using the PMT function, based on specified interest rates, periods, and principal amounts. These financial calculations support the decision-making process for securing favorable loan terms. After verifying calculations, the workbook is saved, closed, and prepared for submission per SAM instructions.

References

  • Excel (2016). Creating histograms and modifying bins. Microsoft Support. https://support.microsoft.com/en-us/excel
  • Microsoft Office. (2016). Insert sparklines in Excel. Office Support. https://support.microsoft.com/en-us/office/insert-sparklines-3703ef41-1943-44a7-b779-8bd9e92bba49
  • Tableau Software. (2020). Data visualization best practices. Tableau Public. https://public.tableau.com/en-us/s/blog/data-visualization-best-practices
  • Harvard Business Review. (2017). Effective Data Charts. https://hbr.org/2017/05/the-right-way-to-chart-data
  • Microsoft Office. (2016). Format chart elements in Excel. Office Support. https://support.microsoft.com/en-us/excel
  • Pokorski, B. (2017). Excel charts and graphs: How to create, customize, and format. DataCamp. https://www.datacamp.com/blog/excel-charts-and-graphs
  • Robert, J. (2018). Advanced Excel charting techniques. ExcelJet. https://exceljet.net
  • Gaskins, R. (2019). Financial calculations in Excel: Practical formulas and functions. Journal of Financial Planning. https://jfponline.com
  • Khoury, N. (2020). Visual storytelling with data: Creating impactful reports. Data Visualization Society. https://datavisualizationsociety.com
  • Winston, W. (2019). Operations research: Applications and algorithms. Duxbury Press.

Related Assignments