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.
Wednesday, March 30, 2011
Monday, March 21, 2011
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.
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.
Sunday, March 20, 2011
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.
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.
Tuesday, March 15, 2011
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.
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.
Monday, March 7, 2011
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.
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!!
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.
Wednesday, March 2, 2011
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.
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.
Subscribe to:
Comments (Atom)