Learning Excel Access and Visio 1 Read the text that describes the problem 2 Understand and explain what the problem is explain what is known and not known explain why figuring out what is not known is important to a business decision 3 Formulate the problem using mathematical notation and statements For access use relational statements and data diagrams 4 Identify the Excel or Access functions that should be used to develop the answer formulation 5 Understand how to use the excel or access functions identified 6 Write the Excel or Access formulas and statements or use wizards to apply the Excel or Access functions to develop the answer 7 Successfully use these Excel or Access tools to answer the problem correctly Plug In T2 Techniques and Methods Look at this to learn the tools Plug in T3 Demonstrate how to analyze the situation and data presented and then use Excel to answer questions asked by managers How to Sort Data Sort Conditional Formatting On home tab top right conditional format highlight cell rules play around Auto Filter Data Filter select column advanced filter You can filter what data is on your spreadsheet Subtotals Data Sort by Subtotal by magazine sales etc Split a spreadsheet View Window Split Pivot Tables Powerful built in data analysis feature Analyses summarizes manipulates data in large lists databases worksheets and other collections Highlight field pivot table new sheet drag titles to where you want them Right click format cells alignment wrap text Landscape Add Header Footer your name custom header Show gridlines Called a pivot table because Fields can be moved within the table To create different types of summary lists Providing a pivot Exercise 1 T Shirt Production Data File Exercise 2 Coffee Trends HW IF function used to conduct conditional tests on values and formulas Conditional Value value can vary value in cell is to blank if another value in the cell is etc Goal Seek function used to find the unknown value that produces a desired result IF Statement IF logical function Returns one value if a condition is true Returns another value if the condition is false Compares two items in workbook IF logical test value if true value if false greater than or equal to on excel Logical Test Value or expression that can be true or false www youtube com watch v vFteHgX01Jk http Excel is Fun book page 136 140 Value if Note Value can be another formula Value if true considiton has this value if true Value if false condition has this value if false Formulas can include IF functions Allows for nested IF functions Example IF B13 B14 08 300 IF B13 10000 0 275 If B13 B14 is greater than 08 than the value is 300 If it is not than you look at if B13 is greater than 10 000 If it is the value is 0 if not the value is 275 A means even if you drag the formula you re keeping it constant DON T CHANGE WHAT FOLLOWS THE SIGN It will always represent the same column etc A3 A3 A 3 A 3 IF A10 A 4 C 3 IF A10 A 5 C 4 IF A10 A 6 C 5 C 6 B10 IF A10 is less than A 4 value is C 3 otherwise if A10 is less than A 5 use the value in C 4 otherwise if A10 is less than A 6 use value in C 5 otherwise its value is C 6 Goal Seek Goal seek backs into the value o Repeatedly tries new values in the variable cell Use to achieve a specific goal or output when how much specific input value is not known Ex new house purchase houses can I afford o I can pay 1500 month in mortgage payment what kind of Goal Seek Data What If Goal Seek Tell Goal Seek what you want the value to be in a formula a and it changes the formula input Set Cell Cell with Formula To Value answer you want formula to evaluate to By Changing formula input sell that you want to change HW Exercise 1 In T4 IF function spotlight video rentals Indicate on your spreadsheet use atleast so its equal to GIGO Garbage In Garbage Out Solver Function Goal Seek on steroids An Excel what if analysis tool Used when problem contains more than one variable Changes 2 or more variable values Tries to find the closest solution Set objective Set contraints Scenario Manager T4 Anaylsis of Business with scenarios xls Watch these videos Learn to Create Scenarios in Excel http www youtube com watch v FKvR8SQKHtw MANAGER MREXCEL S LEARN EXCEL 909 SCENARIO 5 41 3 15 Using Excel 2007 Chapter 11 The Data Asset Databases Business Intelligence and Competitive Advantage sections 1 5 7 8 Any learning outcomes can be questions on the test quizzes o Understand how increasingly standardized data access to third party data sets cheap fast computing and easier to use software are collectively enabling a new age of decision making o Be familiar with some of the enterprises that have benefited from data driven fact based decision making o Understand the difference between data and information o Know the key terms and technologies associated with data o Understand various internal and external sources for organization and management enterprise data Understand why transactional databases can t always be queried and what needs to be done to facilitate effective data use for analytics and business intelligence o Understand what data warehouses and data marts are and the purpose they serve o Know the issues that need to be addressed in order to design develop deploy and maintain data warehouses and data marts o Understand how Wal Mart has leveraged information technology to become the world s largest retailer o Understand how Caesars has used IT to move from an also ran chain of casinos to become the largest gaming company based on revenue o Name some of the technology innovations that Caesars is using to help it gather more data and help push service quality and marketing program success Short Essay Questions Answer following questions for Homework Section 3 1 3 5 Section 4 3 Section 5 1 4 5 What data does UMD and the Smith School have about you DOB address SS grades prev school courses financial aid scholarships jobs phone intern usage major ethnicity activities health insurance parking tickets email What data do other organizations and people have about you Transaction Processing System TPS Records a transaction some form of business related exchange o Ex cash register sale ATM withdrawal product return Loyalty Card System that provides rewards and usage incentives Provides a more detailed tracking and recording of customer activity Enhances data collection Can represent a significant switching cost Data Aggregator A firm that collects and resells data Ex Surveys External Soures
View Full Document
Unlocking...