Imagine you've been working for a week or two at your desktop publishing company and it's time to get paid. The company has required you to open a checking account and OK access through direct deposit. After you've been paid, you will want to keep track of your balance with something like a transaction register. (You will receive one page of such a contraption in class.) The company is also considering expanding into the lucrative check printing business. It would like to collect design proposals for the registers that are distributed with the checks. To satisfy both your present needs and the company's future needs, you will also "automate" the electronic version of your register so it keeps track of your balance so that you do not have to compute it by hand. The company may be able to use the same spreadsheet as part of a checkbook service that it provides.
The project consists of parts with graphic aspects and more technical aspects. With your experience with Word and Excel and exceptional creativity, you are asked to take care of the graphic design aspects on your own (with input from coworkers, your boss, etc.) A representative of the franchise will help with the technical aspects afterwards. As with previous projects, the best designs will be chosen by your peers and you will be crowned employee of the month or some such thing. You will be given one page of a transaction register so that you know more or less what they look like. An example also shows how it might look in Excel. Although there is plenty of room for creativity, it must be possible for Excel to perform the calculations, so try to keep straight lines and columns. Your design should incorporate the graphic aspects below, if only to prove that you can incorporate them when revisions are requested. You should also provide at least two pages that will be printed back to back on a page with the balance transferred from one page to another.
In addition to the printed example, there are technical examples with the row and columns headings shown and with the formulas exposed.
Horizontal alignment other than left
See the home tab.
Vertical alignment other than bottom
Home tab.
Horizontal cell merge
Home tab.
Vertical cell merge
Home tab.
Borders
Home tab.
Fill color
Home tab.
Font color
Home tab.
Font style
Home tab.
Accounting format
Home tab.
Symbols
Insert tab.
Two paged
Use the Page Layout view, the Print Area (Page Layout tab), or print preview to see how your document will look on paper. Make two worksheets and have them print on different pages (or sides of a page). The balance should transfer from one page to the next.
Named cell
Our spreadsheets won't all look the same, but somewhere on the page/sheet there should be an initial balance. On the example, it is at H3. Wherever yours is, let's all call that location BALANCE. To name a cell, right click on it and select Define Name... Name the cell BALANCE with Scope limited to the first page/sheet. Now when you need the BALANCE, you do not have to identify it by location, but can refer to it by name. You'll need a balance on each page/sheet, so label the appropriate cell on the others.
Conditional formatting
It would be great if the spreadsheet did something to attract our attention to negative balances. This means your checkbook is "overdrawn" and you will likely have to pay a penalty to the bank. The cells in the balance column can be formatted to display in red when the balance is less than zero. Select the appropriate cell (H4:H31 in the example document), press the Conditional Formatting button on the Home tab, find Highlight Cell Rulse, and Less Than..., enter 0 for "Format cells that are LESS THAN:", and format with Red Text. If values are not less than zero, the text can be formatted the normal black, blue, or green, etc. Format all sheets this way.
Conditional functions
The same group of cells (H4:H31) needs to automatically calculate your balance. The page looks a lot cleaner IF values are not displayed for lines with no payment or deposit amount. IF is the conditional function and it looks like IF(Logical_test,Value_if_true,Value_if_false). For the moment, you can use IF(TRUE,"balance",""). Enter that and make sure the word balance shows up. To calculate the actual balance for cell H4 in the example spreadsheet, use BALANCE-SUM(D$3:D4)+SUM(G$3:G4). The balance is BALANCE (H$3) from the top minus the sum of payments starting at D$3 down to the current line plus the sum of deposits starting at G$3 down to the current line. This makes the formula for the one cell =IF(TRUE,BALANCE-SUM(D$3:D4)+SUM(G$3:G4),""). You can copy this to H4:H31 to test it. Test the conditional formatting by making a large payment.
Boolean functions
The page looks a lot cleaner when values are not displayed for lines with no payment or deposit amount. So, you will only make the calculation if it is NOT the case that the cell in the payment column AND the cell in the deposit column are both blank. NOT and AND are Boolean functions which work on values TRUE and FALSE. Another one, ISBLANK, detects whether a cell is blank. The Logical_test from above should be changed from TRUE to NOT(AND(ISBLANK(D4),ISBLANK(G4))). If it is NOT the case that both D4 AND G4 ISBLANK, then calculate the balance. Otherwise, insert a blank there. The entire formul for the cell becomes =IF(NOT(AND(ISBLANK(D4),ISBLANK(G4))),BALANCE-SUM(D$3:D4)+SUM(G$3:G4),"") and should be copied throughout the column. Format the balance column cells on all sheets this way.
Balance transfer
The balance from the bottom of one sheet should transfer to the balance at the top of the next sheet. The user can enter the balance directly on the very first sheet, but the second sheet should include a formula like =Page1!BALANCE-SUM(Page1!D$3:D31)+SUM(Page1!G$3:G31) at the top. It does not need to be conditional or involve conditional formatting. If you have more than one sheet, be sure to carry over the balance each time.
Save your spreadsheet as Checkbook.xlsx or Checkbook.xls. Secondly, print your checkbook to a PDF document. We will practice this again in class and the printed version displayed online and used in the competition. Email or USB both files to the teacher. Since they are part of the very same project and shouldn't be separated, attach them to a single email. Good luck in the competition.
Points are awarded in the categories below. Subjective aspects (e.g., how good it looks) will be part of the competition and rewarded with admiration of your peers.
Graphic design aspects
Horizontal alignment other than left
Vertical alignment other than bottom
Horizontal cell merge
Vertical cell merge
Borders
Fill color
Font color
Font style
Accounting format
Symbols
Two paged
Technical aspects
Named cell (2 points)
Conditional formatting (2 points)
Conditional functions (2 points)
Boolean functions (2 points)
Balance transfer (2 points)
Other
Single email
Two attachments