Project — Financial Projection Worksheet with What-If Analysis and Chart

For an introduction to Office and instructions about how to perform basic tasks in Office apps, read the Office and Windows module at the beginning of this book, where you can learn how to run an application, use the ribbon, save a file, open a file, print a file, exit an application, use Help, and much more.

BTW

Excel Screen Resolution

If you are using a computer or mobile device to step through the project in this module and you want your screens to match the figures in this book, you should change your screen’s resolution to 1366 x 768. For information about how to change a computer’s resolution, refer to the Office and Windows module at the beginning of this book.

The project in this module uses Excel to create the worksheet and clustered column chart shown in Figures 3–1a and 3–1b. Kaitlyn’s Ice Cream Shoppe operates kiosks at colleges and universities and serves both hard and soft serve ice cream. Each December and June, the chief executive officer projects monthly sales revenues, costs of goods sold, gross margin, expenses, and operating income for the upcoming six-month period, based on figures from the previous six months. The CEO requires an easy-to-read worksheet that shows financial projections for the upcoming six months to use for procuring partial financing and for determining staffing needs. The worksheet should allow for quick analysis, if projections for certain numbers change, such as the percentage of expenses allocated to commission or the cost of the kiosk rentals. In addition, you need to create a column chart that shows the breakdown of expenses for each month in the period.

Figure 3–1(a) Worksheet (b) Clustered Column Chart

BTW

Touch Screen Differences

The Office and Windows interfaces may vary if you are using a touch screen. For this reason, you might notice that the function or appearance of your touch screen differs slightly from this module’s presentation.

The requirements document for the Kaitlyn’s Ice Cream Shoppe Six-Month Financial Projection worksheet is shown in Figure 3–2. It includes the needs, source of data, summary of calculations, and chart requirements.

Figure 3–2

Worksheet Title	Kaitlyn’s Ice Cream Shoppe Six-Month Financial Projection
Needs	A woksheet that shows Kaitlyn’s Ice Cream Shoppe projected monthly sales revenue, cost of goods sold, gross margin, expenses, and operating income for a six-month period.A clustered column chart that shows the expected contribution of each expense category to total expenses.
Source of Data	Data supplied by the business owner includes projections of the monthly sales and expenses based on prior year figures (see Table 3–1). Remaining numbers in the worksheet are based on formulas.
Calculations	The following calculations are needed for each month:
Chart Requirements	Show sparkline charts for revenue and each of the items noted in the calculations area above.Show a clustered column chart that shows the contributions of each month’s expense categories to the total monthly expense figure.

Using a sketch of the worksheet can help you visualize its design. The sketch of the worksheet consists of titles, column and row headings, location of data values, calculations, and a rough idea of the desired formatting (Figure 3–3a). The sketch of the clustered column chart shows the expected expenses for each of the six months (Figure 3–3b). The assumptions about income and expenses will be entered at the bottom of the worksheet (Figure 3–3a). The projected monthly sales revenue will be entered in row 4 of the worksheet. The projected monthly sales revenue and the assumptions shown in Table 3–1 will be used to calculate the remaining numbers in the worksheet.

Figure 3–3

Table 3–1

Kaitlyn’s Ice Cream Shoppe Six-Month Financial Projections Data and What-If Assumptions

Projected Monthly Total Sales Revenues
What-If Assumptions
January	55,000.00
February	62,500.00
March	67,000.00
April	90,250.00
May	77,500.00
June	74,750.00
Margin	78.75%
Bonus	$3,500.00
Sales Revenue for Bonus	65,000.00
Commission	25.00%
Site Rental	10.00%
Marketing	5.00%
Equipment Repair and Maintenance	3.50%

With a solid understanding of the requirements document, an understanding of the necessary decisions, and a sketch of the worksheet, the next step is to use Excel to create the worksheet.

In this module, you will learn how to create and use the workbook shown in Figure 3–1. The following roadmap identifies general activities you will perform as you progress through this module:

1. enter the headings and data in the worksheet.
2. enter formulas and functions in the worksheet.
3. create sparkline charts in a range of cells.
4. format the worksheet.
5. create a column chart on a separate chart sheet.
6. change views of the worksheet.
7. ask what-if questions.

BTW

Excel Help

At any time while using Excel, you can find answers to questions and display information about various topics through Excel Help. Used properly, this form of assistance can increase your productivity and reduce your frustrations by minimizing the time you spend learning how to use Excel. For instructions about Excel Help and exercises that will help you gain confidence in using it, read the Office and Windows module at the beginning of this book.