Assignment 1
1. Your assignment is to design, layout and created a bank reconciliation worksheet so that it can be used month after month. You are required to use an input sheet and separate sheets for the bank reconciliation and any necessary journal entries. The input sheet can contain only the information provided above. The bank reconciliation and journal entry worksheets can have no manual input other than the heading and the line/column titles, all dollar amounts must be input by formulas. The date in the heading must be entered by a formula. Your worksheet must be designed so that it can handle all possible events without changing your formulas. Include your name and the current date and time so they print out automatically on the top left of your printout. Design so that the worksheets print out on one landscape page each and are professional in appearance.
2. You are to design the worksheet so that any journal entries that are required to be recorded in the accounting records will print out. Again, no dollar amounts are allowed to be keyed into the journal entries.
3. Your bank reconciliation worksheet must contain a line that proves your reconciliation is in balance. This line is not to appear on the reconciliation printout.
Flybynight Inc. deposits all receipts nightly in the bank's night depository. The following data has been extracted from Flybynight's accounting records:
Checking account balance as of (2010/11):
Dec 31 $8,720.23
Jan 31 $8,626.80
Deposits made during Jan.:
|
Jan 3 $545.25
|
Jan 8 $842.00
|
Jan 11 $978.35
|
|
Jan 16 $658.05
|
Jan 19 $643.90
|
Jan 24 $352.62
|
|
Jan 28 $715.28
|
|
|
Check number and amount of checks issued during Jan.;
|
750
|
$361.90
|
751
|
$ 64.71
|
752
|
$462.18
|
|
753
|
$ 51.90
|
754
|
$869.52
|
755
|
$471.86
|
|
756
|
$156.20
|
757
|
$274.35
|
758
|
$246.60
|
|
759
|
VOID
|
760
|
$87.95
|
761
|
$719.27
|
|
762
|
$236.74
|
763
|
VOID
|
764
|
$210.00
|
|
765
|
$615.70
|
|
|
|
|
The following data was extracted from the Jan. 31 bank statement received from the bank: Balance as of:
Dec. 31 $ 9,568.63
Jan. 31 $11,157.57
Deposits recorded during the month of Jan.;
|
Jan. 1,
|
$361.90
|
Jan.4,
|
$545.25;
|
|
Jan. 9,
|
$842.00
|
Jan.12,
|
$978.35;
|
|
Jan.17,
|
$658.05
|
Jan. 20,
|
$643.90;
|
|
Jan. 25,
|
$352.62
|
|
|
Checks that cleared during Jan;
|
740
|
$ 90.60
|
747
|
$217.45
|
748
|
$570.97
|
|
750
|
$361.90
|
751
|
$ 64.71
|
752
|
$462.18
|
|
753
|
$51.90
|
754
|
$869.52
|
755
|
$471.86
|
|
756
|
$156.20
|
757
|
$274.35
|
758
|
$264.60
|
|
760
|
$87.95
|
762
|
$236.74
|
|
|
Bank memos that appeared on the bank statement;
Jan. 4 Debit memo for NSF check of $64.20
Jan. 12 Credit memo for note collected $1,400.00 plus interest of $60.00
Jan. 31 Debit memo for bank service charge of $8.00
The following data was extracted from the Dec. bank reconciliation:
$361.90
| Deposit in transit Dec. 31 |
|
| Outstanding checks: 740 |
$90.60 |
| 747 |
$217.45
|
| 748 |
$570.97
|
| 749 |
$331.28
|
NOTE: All errors (if any) were made by Flybynight Inc. and involved Accounts Receivable (if error was with a deposit) or Accounts Payable (if error was with a check).