Viewing Prior Exams

Amanda will have your prior exams for review tomorrow from 10am - 1pm. I will have your exams available on Monday afternoon from 2pm - 4pm.

Monday, Monday

Two big things happening Monday:

1. Bring your completed project on your flashdrive in an envelope with your name on it. After I grade the projects, you may have the flashdrives back.

2. We will take a 'practice' practical portion of the exam. You will have 45 minutes to complete and if you feel like you were successful, you will remit your file to me. If you complete EVERY query correctly (there are 5), you will be exempt from the practical portion of the exam and will receive an automatic 25/25.

Practice your querying over the weekend! Even you aren't quite up to speed so that you are able exempt the practical portion of the exam I feel confident that the practice exam will do a good job preparing you for the actual exam.

Project Queries

As I stated in class, I am fully expecting for all of your queries for the project to be written and running at the start of class on Wednesday. If you are behind and need help, I have office hours this afternoon, and will answer questions via email (attach your database) during business hours tomorrow (I have plans tomorrow evening, so won't be online).

There are files on Blackboard that provide additional guidance. Please see these files before submitting your file to me via email if you just want a "check" (there are check figures on Blackboard).

My best guess is that you will have the following queries (names might not be exact, that is fine):

qryCashReceipts
qryCreditSales
qryDebitsAR
qryCreditsAR
qryARBalance
qryOverCreditLimit
qryOpenToBuy
qryInvoiceNoPayments

Or some similar list based on how you named the queries. Your DPQ Wednesday will be based on your project.

Querying HOMEWORK

Using The Coffee Merchant database, you need to create employee commission on sales.

There is a file on Blackboard that walks through this query (f. on Querying Warmup, we did a.-e. in class today). You will need it in order to complete your DPQ on Monday. This is an INDIVIDUAL homework assignment. If you run into problems, email me, I will be around quite a bit the end of this week.

Also, please review the Query Help file which discusses Join Properties and how to use null fields in calculations. Here is a quick video on Joins:

Querying

As far as preparing for our querying classes, I would focus on pages 844-855, and Chapter 3 in your book. Practice as well, querying isn't hard, but there is a learning curve. Here is a video to get you started (it is a bit longer than normal, 10 minutes, but he shows how to use criteria and aggregates).



I have posted the file that will the basis of the next few DPQs on Blackboard in case you want to download and familiarize yourselves with the data and layout before attempting the DPQ in class.

Access: Forms

The schedule is actually pretty wrong with regards to the chapter and pages for FORMS. Chapter 6 is where forms are described....as well as some other controls that may come in handy.

Forms are actually pretty easy....here is a quick video on how to create a simple form.

Access: Setting up relationships

Here is a pretty good guide to creating relationships in Access.  NOTE that the relationship between Students and Courses in this example is M:N...thus the Enrollment table is created to break up that many to many into TWO one to many relationships.  Further, while she suggests using Cascading update and delete, I usually hesitate with those, only because you can do more damage than good with those enacted.



I would look over pages 807-844 in your book.

Intro to Access

This is a pretty good video on the underpinnings of Access...it is about 10 minutes long, but if you don't "get" Access, it is a starting point.



Here is a link to some basic navigation stuff within Access 2007

http://www.youtube.com/watch?v=dla_z1IWPHQ

What will they think of next???

Those Googler's have figured out that we don't need keyboards or mice anymore...crazy stuff.

Gmail Motion

Excel Bonus Opportunity

As many of you know, Dr. Clark also teaches ACCT 3510 this semester.  He just showed me a pretty cool idea for using PivotTables and Charts to provide a visual overview of a company's performance measures. 

Unfortunately for us, we have completed Excel and are moving on to Access.  But it is such a neat idea, I decided to give you guys the green light to complete this exercise for bonus points.  Turn it in to me via email (engagement.senior@gmail.com) no later than April 6th, 5pm.  This is an INDIVIDUAL bonus opportunity---not to be completed together.  This is worth 5 points, all or nothing. (Means you have to complete the entire exercise to get 5 points, no partial credit.)

I will post the file and directions later today. 

Internal Controls in Excel

One of the most important things you can do for your client and yourself after you have created a wonderful spreadsheet is protect your formulas, etc. from accidental or even perhaps, malicious changes. Here is a pretty good video, plus you can jam out to the muzak while it loads.



We are also going to talk about the importance of Documentation, how Data Validation can help ensure that users only enter data that conforms with expectations and using Conditional Formatting to highlight errors. Your book does a pretty good job with "auditing", pages 716-723.

Here is an example of how to create a dropdown list:



However, if you only have a few items for your drop down list, say 2 or 3, that are not likely to change, you can type the list in lieu of putting the list on a separate sheet and naming the range. It will look like this:

Each choice in the drop down list should be separated by commas.

Text Tools

I can hardly believe that spring break is already over!  I hope you all got rested and rejuvenated for our final push of Spring 2011. 

Tomorrow we are going to work on text tools in Excel.  Here is a video that does a pretty good job...



the only thing I would recommend to the author is to use the RIGHT function to pull out the state abbreviations instead of MID. The formula would read =RIGHT(cell reference, 2).

Here is a short, kinda blurry video on Text to Columns, but I think you can get the idea:



If you haven't already done so, and would like to earn 2 extra DPQ points, please refer to the last blog post and complete the survey on your usage of the videos!

See you tomorrow.

YouTube Video Survey

Here is a link to the survey:

YouTube Survey


Remember, I am only interested in how you have used or not used the YouTube videos.  I would do this anonymously, but I want to award you DPQ points (2) for completing the survey.  Your answers in no way impact ANYTHING, other than letting me get some insight as to what works, and what doesn't work in AIS that I can then use to extrapolate to firmwide web based training.

Thanks!  Hope you are having a great break.

PivotTables

I think I mentioned this in class, but understanding and being able to use PivotTables is one of the MAJOR concerns that firms have when you go out on your internship/staff position, so we will spend an entire class period on a topic that is covered in 8 pages (or so) in your book. 

To get an idea of the power of PivotTables, check out the following video:



A few rules of thumb for formatting:

(1) Always make sure you are spelling correctly (F7) and are appropriately formatting all data fields.

(2) When you are trying to decide if a field should be the ROW or the COLUMN, the field that contains more items should be the ROW.  This is because when looking at Excel we can typically see more rows than columns, and we can fit more data in our screenshot following this methodology.  Sometimes, form will drive us away from this rule of thumb and that is okay.  For example, let's consider data that has fields for Division (let's assume our data includes 3 divisions) and Date (we have data for 12 months).  Division would be the ROW and Date would be the COLUMN even though the "rule of thumb" would suggest otherwise.  This is because we know that dates are typically shown in columns, especially with respect to financial data. 

One final thing about rows and columns.  If there is a field that is a subset of another field...say branches within regions, we will typically use both fields as rows (with region listed first, then branch) or columns.

Calculated Columns in TABLES

Here is what happened today in class...(I thought I saw this happen in the 11am, and confirmed it in the 1230pm class).

When we created our TABLE and inserted the calculated column for Total/Revenue (price*quantity*(1-discount)+freight), as long as we left it in the TABLE format, we were fine.

Once we converted to a range, our formula still "seemed" okay

=CustSales!$H2*CustSales!$I2*(1-CustSales!$J2)+CustSales!$K2

This was the formula I copied from row 2...the columns are absolute referenced, which is fine, and the rows vary, again fine. The CustSales! refers to the worksheet.  Again, that is fine.

Normally, when you apply a sort, as long as all of the data is selected, all rows (called a record) should stay intact, and the rows should rearrange as specified.  However, for some reason, all the data is sorting correctly, EXCEPT the calculated column we added.  It is staying in the same location, and referencing the "new location" of the inputs.  For example, if I sort and row 2 has shifted to row 16, the formula in L2 is

=CustSales!$H16*CustSales!$I16*(1-CustSales!$J16)+CustSales!$K16

This results in having totals in the final row that don't correspond to the record it is "attached to".

I can figure out why that is happening.  To be honest, I have never used calculated fields before, but I saw in on the video and thought it was very cool.  BUT, to be safe, do NOT use calculated fields in either TABLES or PIVOTTABLES.  Calculate your column first, then add the TABLE functionality.  That will keep you in good shape. 

Sorry for the confusion, but just one more lesson on how you should NEVER trust Excel too much!!

DPQ Timing

I just wanted to let you know that I delete DPQ email if they are received by GMAIL more than 3 minutes after I have cut off the time.

TABLES and Project stuff

I LOVE the TABLE functionality in Excel. Here is a very matter of fact video about how to use it. Note that there is also "AutoFilter" as part of the functionality that the video doesn't mention.

I can't embed the video, but here is a link.

At the end of the 1230pm class, Luis asked me a formatting question---specifically how to indent within a cell.  Great question.  The answer was there is an "indent" button on the Alignment section of the Home tab (see the highlighted area in the snapshot below).

At the end of class on Monday, I am happy to answer any question that you have regarding Excel that either came up when you were typing your financials for the SUA, or as we have gone through class.

I will post the pdf of the 12 column worksheet tomorrow afternoon.  If you were going to work on it before then, send me an email and I will send it to you directly.

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.

Transactions for Wednesday

In addition to reading Chapters 1, 3 and 4 for Wednesday, please complete the first two transactions. I recommend reading first to get a basic understanding of the business processes.

The transactions are (from the BLUE transactions list (Document No. 1))

December 16 Received validated deposit receipt

Carefully read the transaction, then follow the appropriate portion of the flow chart---the very far right column on page 21.  (The actual deposit was made "a few days ago" and we are just now getting the receipt---so the journal entry has already been recorded.)

December 16 Ordered inventory

Carefully read the transaction, then follow the appropriate portion of the flow chart---the very far left column on page 22---NOTE there will be a break in time after you order the goods so Nancy will fill out the purchase order, Ray will approve it, and then you will file appropriately and then STOP---you won't receive the goods for a few days  so NO JOURNAL ENTRY WILL BE MADE.  Again, you will not start the "Receiving" column, because you have not received the goods yet (See December 22).

Revised reading for Monday 1/31/2011

We are very close to getting too far behind, so I need for you guys to read a bit more for Monday:

Open your SUA (Systems Understanding Aid) and:

1. In the "Instructions, Flowcharts and Ledgers" book read through page 15.

2. In the "Reference" book, read Chapter 2 about flowcharts.

3. Find the BLUE transaction list (in the loose documents)---Document No. 1 (it is literally blue) and read it.

On Monday, we will finish our Internal Controls discussion, and we will go over the SUA project in pretty decent detail. 

Have a great weekend.

TL

Readings for Monday

On Blackboard, under "Course Handouts and Readings", the first two links, "Internal Control Case" and "TAT Nov10" are the readings for Monday. 

If you did not do the first assignment, please do it between now and Monday.  I will not collect it from you, but you will need to refer to it over the course of the semester. 

Final administrative details: Office hours.

I will be holding office hours on Mondays and Wednesdays from 3:30 until 4:45 pm.  My TA will hold office hours as well, but I am unfamiliar with your schedules and want to be sure I schedule hours that work well for everyone.  I have created a poll HERE.  Please choose two times that work with your schedule, then Tara (my TA) will hold office hours during the top 4 vote getting slots.  (As an aside, from a professionalism standpoint, if I ask for your input, I thoroughly expect to receive it.  Meaning, I will check to be sure everyone participates, or the process will not work as I intend.)

I wasn't sure if you all were aware, but some of our upper level accounting classes are also conducted via a distance learning program and are recorded.  I must have forgotten to tell the "tapers" that we were not meeting today, and they caught a conversation by two students...I will share that with you here.



FYI: Blackboard has been updated with the syllabus and schedule. See you next week.

War Eagle!

What a fantastic way to kick off Spring Semester 2K11!  What a game---

Due to weather and travel concerns, I am going to delay our first "in person" class session until next Wednesday. However, we do need to get started....between now and next Wednesday:

Visit a restaurant for a meal.  Any restaurant will do.  While there, make careful observations on the procedures taken by the restaurant's personnel with respect to placing your order, receiving your food, and paying your bill. 

Write these procedures within 3 paragraphs, with one paragraph per "step"...placing order, receiving food, and paying your bill.  Depending on the nature of your restaurant, these paragraphs may be in a different order.  Put your paragraphs in chronological order.

Next, rewrite the paragraphs from the perspective of the restaurant making the sale...which will be receiving order, providing the food, and receiving the payment. 

Print two copies, one you will keep in  your notes that we will refer to often, and the second you will turn in next Wednesday. 

If you have any questions, please use the comments section of the blog, in case others have the same questions.

Travel SAFE.  See you next week.

Hello!

As some of you may, or may not, know, Blackboard is not going to be Blackboard for very much longer, and I wanted to create something for AIS that could be used going forward for other classes, or for you, once you are done with AIS.  So, here is the first official AIS Blog.

You can consider this THE source of information for ACCT 3510-3513.  I will use Blackboard for posting pdf's of course notes, assignments, etc. (for the time being...I may change this) and grades (all that pesky privacy stuff). 

But for all the other good stuff, I will use this blog...as a result, this blog is required reading, and although there is not a "participation" portion of the course, I do want to hear from you.  This will be the venue where you can ask me questions, and I can either provide (or better yet, find on the web) solutions to help you learn the concepts in this class more thoroughly. 

If you haven't already done so...consider setting up a Google Reader account and (of course, add this blog).  If you have a smart phone, you can look at Google Reader while you are waiting on the transit, at Chick Fil A over lunch, or while you are sitting in Intermediate II (just kidding, sort of).  Cool thing about Google Reader is that you can add all kinds of feeds that you are interested in...sports, entertainment, news, and OH yeah, business. 

Okay, for some important stuff. 

Required on DAY ONE:

• Arens & Ward: Systems Understanding Aid (7e): ISBN: 978-0-912503-27-1

Don't need until February:

• Exploring Microsoft Office 2007 Custom Edition [ISBN: 0-536-51171-3]---make sure my name is on the cover!
• THIS EXACT THUMB DRIVE.  You can pick any color, but you will be handing them over to me....more on this later. [Edited to add: Distance students you do not need to buy a thumb drive.]
• Excel 2007 and Access 2007 (available in COB labs for on-campus students, and virtual lab for distance students), but also available here for a GREAT price.  (2010 versions work fine as well.)