Excel Stuff: Nested IF, AND, OR, NOT, Conditional formulas

The following video gives a pretty good overview of AND and OR, and reviews IF statements:



The NOT function is not discussed in this video, but the concept is that NOT(argument) merely reverses whatever the value of the argument. For example, switches a TRUE to FALSE, and vice versa. Sometimes comes in handy when constructing an IF statement.

Here is a pretty clear example of a nested IF statement. However, please note that whenever you have a scheme, like grading, with more than 3 outcomes, using a VLOOKUP is much better. Simply because embedding all the outcomes is time consuming and would be better as an assumption table.



This final video demonstrates conditional formulas:

Excel Stuff

On Monday, we are going to be focused on mixed references (absoluting either the column or row and letting the other stay relative), LOOKUPS and IF statements.

Here is a pretty decent tutorial on VLOOKUP that I found on YouTube.  If nothing else, type up the example shown in the tutorial and try implementing it yourself so you can see how it "feels" when you are working with it...it always looks so much easier when someone else is doing it.



Here is a decent illustration of a basic IF statement.  The only thing that I don't really like about it, is that it would be better to have an assumption area with the bonus structure, in case it were to change.  

Exam Grades

I finally managed to get your exam grades done and uploaded to Blackboard.  You guys did pretty well.  The average was a 76%, with 31 A's and B's.  I met with several students yesterday, and wanted to emphasize that the way this course works, with bonus opportunities and project points, most course grades tend to be one letter grade  higher than the test average.  So I would say a vast majority of the class are set for A's and B's, which pleases me a great deal. 

Once you get the SUA done (WHEW), the time commitment I am going to ask from each of you relates to prepping for class (which will also get you DPQ points).  The Excel project is about a 6 hour project (unless you have already typed your financial statements in Excel, then a bit less), and if all goes well with your class preparation, we will do MOST of the Access project IN.CLASS.  This means I am going to fully expect good class preparation and participation in each class from each of you.  I am asking for 45 minutes to an hour prep before each class (on average, based on your skill set and learning curve), so ONLY about 2 hours a week outside of the classroom---if you do prep and come to class every time, I promise that the remainder of ACCT 3510 will go very smoothly and we will all be very happy with the end result. 

For those of you still working on the SUA...I will be available via email this weekend, but more sporadically than last weekend.  If you are turning in your project tomorrow, there is a box labeled "Loraas SUA" in the School of Accountancy office that you can drop it in...otherwise, bring your projects to class on Monday for turning in.  Good luck, have a great weekend!

Transaction related tips AND studying for exam

I have seen many of you today working on your projects and wanted to give you a few tips---

Dec 22 Received merchandise: At this point you are receiving the merchandise, but you won't receive the invoice until December 23 so you will STOP after completing the receiving report. Nothing happens with the freight bill until you pay it on Dec 23.

Dec 22 Received check.  Note the difference between the check amount and the invoice amount.  Why might that happen? 

Dec 22 Legal notification---it will be tempting to "wing it" and not use the flowchart, but you might miss two controls if you do...

Dec 23 Received goods from EWU.  You will start with a receiving report because you are receiving goods...but remember these are not from a purchase, but a return, so you will take the other branch in the flowchart.

Dec 26 Received office supplies.  Pay attention to your journal entry---

These are all that I am coming up with right now, but feel free to ask questions in the comments and I will reply.  Happy working!!

As far as the exam goes, there will be 30 multiple choice questions (2.5 points each for 75 points) on the concepts we have covered with respect to the business processes and related internal controls--I have put a study guide on Blackboard.  Sample questions:

When a company receives a check in the mail in payment of an account receivable, the journal entry is recorded in the:

a.       Accounts receivable subsidiary ledger

b.      General ledger

c.       Cash receipts journal

d.      General journal

e.       Sales journal

OR

What is the normal sequence for completing a sale on account?

a.    customer purchase order, bill of lading, sales journal, invoice, general ledger

 b.    customer purchase order, general journal, receiving report, invoice, general ledger

 c.    customer purchase order, purchases journal, receiving report, vendor invoice, general ledger

 d.    customer purchase order, receiving report, invoice, general journal, general ledger

 e.    customer purchase order, bill of lading, invoice, sales journal, general ledger

After you complete the multiple choice questions, you will be given a company's flowcharts and you will inspect it for internal control weaknesses (25 points).  We will spend 10 minutes on Monday on a practice flowchart so that you will know better what to expect (the file is locatd on Blackboard...the review will be more beneficial if you have attempted the exercise by yourself before class).  DO NOT memorize Waren's flowcharts...remember Waren is one company and because Waren does something does not make it the "golden example".  Instead, think about the normal sequencing of the business processes, the appropriate documents and what potential things could go wrong so that you can more easily identify when processes are missing potentially critical controls.