ITEC 1001 Excel Assignment 2: Forecasting Monthly Sales

ITEC 1001 Excel Assignment 2 Forecasting Monthly Sales for Four Restaurants

ITEC 1001 Excel Assignment 2: Forecasting Monthly Sales for Four Restaurants

The assignment involves creating a financial forecast for four restaurant locations in Gwinnett County based on estimated July sales and projected percentage changes over subsequent months. The task requires organizing data, applying formulas for sales projections, calculating totals and averages, and creating visual charts for analysis.

Participants are to prepare a spreadsheet with compressed data summaries, perform calculations using formulas and functions, generate charts for comparison and visualization, and present their work in a clear, formatted manner suitable for review and analysis.

Paper For Above instruction

Introduction

Forecasting sales is a fundamental task in financial planning for retail and hospitality establishments. It involves utilizing historical data, making realistic assumptions, and applying mathematical formulas to project future performance. This case study simulates forecasting monthly sales for four retail restaurants in Gwinnett County, based on initial estimates for July and designated percentage changes for subsequent months. The aim is to develop a comprehensive Excel spreadsheet that not only calculates projected sales but also visualizes these projections for strategic analysis.

Data Organization and Assumptions

The first step involves organizing all initial data and assumptions in the worksheet for clarity and ease of reference. This includes entering the July sales figures for each restaurant:

  • Duluth: $777,666 with monthly percentage change of 1.4% for subsequent months
  • Lawrenceville: $444,222 with 3.2% change
  • Lilburn: $678,321 with 2.5% change and a -1.7% change in December
  • Snellville: $765,567 with 2.8% change, and 1.0% in October, and -2.5% in December

These assumptions form the basis for projecting sales through December using percentage increases or decreases on a month-to-month basis.

Financial Calculations Using Excel Formulas

The next phase involves creating a second data table dedicated solely to sales projections, where only formulas are used. For each subsequent month (August through December), the projected sales are calculated by referencing the previous month's sales and applying the respective percentage change. For example, the projected sales for August for each restaurant are computed as:

August Sales = July Sales + (July Sales * August Percentage Change)

Similar formulas are used for September through December, ensuring each month builds upon the prior month's sales data.

In addition to individual sales forecasts, total sales per month across all four restaurants must be calculated using the SUM function, and averages of these monthly totals are to be determined using the AVERAGE function. Grand totals encompassing all six months provide an overall perspective of the combined sales forecast, while averaging these totals yields an average monthly sales figure across all restaurants.

Formatting and Presentation

Proper formatting enhances the spreadsheet's readability and professionalism. This includes aligning columns, setting appropriate widths, formatting currency values, and emphasizing headers through bolding or italics. Additional visual elements, such as borders or shading, may be added for clarity and aesthetic appeal.

Graphical Data Visualization

To complement numerical analysis, two types of charts are recommended:

  1. Column Chart: Comparing projected sales across all four restaurants for each month, with the months on the x-axis. The chart should include descriptive titles and a footer with the author's name.
  2. Pie Chart: Showing the total sales contribution of each restaurant over the forecast period. Actual sales values should be displayed next to each slice, with a comprehensive legend. The title should span two lines for clarity.

The charts may be placed on separate sheets within the workbook, with clear labels and titles to facilitate interpretation and presentation.

Conclusion

This exercise combines data organization, formula-driven calculations, and visual presentation tools in Excel to prepare a comprehensive sales forecast. It emphasizes accurate referencing, proper formatting, and effective visualization to support strategic business planning for the restaurants.

References

  • Walkenbach, J. (2013). Excel 2013 Bible. Wiley.
  • Gaskins, B. (2010). Excel Data Analysis: Your visual blueprint for analyzing data, charts, and PivotTables. Wiley.
  • Bates, T. (2012). Excel Formulas and Functions for Dummies. Wiley.
  • Microsoft Support. (2021). Create and manage charts in Excel. https://support.microsoft.com
  • Heap, G. (2018). Practical Financial Management: A student guide. Routledge.
  • Dumont, R. (2014). Forecasting Techniques: A practitioner’s guide. Routledge.
  • Excel Easy. (2020). Excel tutorial for beginners. https://www.excel-easy.com
  • Chamberlain, J. (2021). Mastering Excel Formulas and Functions. Packt Publishing.
  • Everett, G. (2016). Advanced Excel Reporting and Analysis. Wiley.
  • ExcelJet. (2022). Excel chart types and best practices. https://exceljet.net

Related Assignments