...Data Lists Naming Cells and Ranges Conditional Functions Lookup Functions Pivot Tables Data Validation Today is 7/22/2011 3 Naming Cells and Ranges Today is 7/22/2011 4 Name cells by using labels Name cells by using existing row and column labels Select the range you want to name, including the row or column labels. On In the Insert menu, point to Name, and then click Create. the Create names in box, designate the location that contains the labels by selecting the Top row, Left column, Bottom row, or Right column check box. Note A name created by using this procedure refers only to the cells that contain values and does not include the existing row and column labels. Today is 7/22/2011 5 Name a Cell or Range Using Names Box 1. Select the cell or range you want to name. 2. Click the Name box on the formula bar. 3. Type a name for the range. A range name can include uppercase or lowercase letters, numbers, and punctuation, but no spaces. Try to use a simple name that reflects the type of information in the range, such as BookTitle. 4. Press Enter. The range name will appear in the Name box whenever you select the range. Today is 7/22/2011 6 Conditional Functions Today is 7/22/2011 7 Using IF – Basic and Nested Syntax IF(logical_test,value_if_true,value_if_false) Using IF Function - Basic Enter the formula =IF(condition,expression1,expression2), where condition...
Words: 1579 - Pages: 7
...Data Table Analysis Data Table Analysis Kudler’s Fine Foods is a gourmet food store that provides specialized products in the Southern California region. Since the inventory of the store is perishable, it is important to have control of the inventory on hand and put in place a procedure for replacing the inventory promptly. If Kudler’s has too much inventory on hand, the loss of revenues due to waste increases. Alternatively, if Kudler’s cannot replace inventory fast enough, they risk alienating customers who count on them to provide gourmet specialty products that are hard to find in the grocery store chains. By evaluating the design elements of the data tables, implementing the recommended improvements to the data tables and utilizing pivot tables to get a more accurate picture of the inventory ordering and processing system, Kudler’s should be able to increase their bottom line. The inventory table that Kudler’s uses contain the components that make up an item. The items listed include a general ledger code, item description, summary line item, and cost (Apollo, 2013). An evaluation of the database tables will provide suggestions for improvements of their data system. One of the most important components of Kudler’s inventory is missing from their database table. A recommendation would be to include a purchase date or an expiration date of each item. Since perishable items are so prominent in the business, the ability to have an alert system for almost expired items, as...
Words: 465 - Pages: 2
...Homework #2 – Excel Charts and Pivot Tables Overview and Learning Objectives This homework assignment asks you to use Microsoft Excel to use the VLookup function in a report, and also to generate charts and pivot tables. These are all useful skills. VLookup simplifies spreadsheet logic in many situations, and charts offer a visual perspective that can help people to see trends and data relationships. And once you get the idea of pivot tables, they are extremely useful in summarizing data “on the fly” in a variety of formats. This assignment is related to course objective #4 (“Design and implement Microsoft Excel spreadsheets and Microsoft Access databases to support business decision making”). Quality Furniture Enterprises, Continued This homework assignment continues the scenario from Homework #1, about Quality Furniture Enterprises. Caitlin’s uncle (and boss) was pleased with the sales forecast report she created. “I guess I should learn Excel,” he says. “I’ve been using the calculator on my laptop to compute the totals, and then copying and pasting things into a Word document.” Caitlin did her best not to appear smug, but it was nice to be appreciated, especially since this was still her first month on the job. But before she had any time to bask in her success, her uncle said, “And since you did so well on that report, I have another project for you.” So much for taking an early lunch break today. “Sure,” she said. “What do you need?” “First off, remember how I told...
Words: 1904 - Pages: 8
...Kudler Foods Data Tables Kudler Fine Foods was founded in 1998 by Kathy Kudler as an upscale specialty food store. The first location of Kudler was in La Jolla, CA, followed by stores opened in Del Mar and Encinitas. Kudler Fine Foods offers its customers selections in bakery and pastry products, fresh produce, fresh meat and seafood, condiments and packaged foods, as well as cheese and specialty dairy products. This brief will evaluate the design of Kudler’s current inventory data table, make recommendations for improvements to the current tables, and provide information for effectively using pivot tables. Design Element Evaluation Analysis of Kudler’s data tables by end users is essential to Kudler’s inventory control. End users should be able to use inventory data tables to determine the amount of inventory on hand in each location, which in turn will assist end users in determining the amount of inventory to be ordered to replenish each location. Kudler’s current inventory data table provides its end users with general ledger coding, inventory items, store locations and departments, and the value of each inventory on hand. Users of Kudler’s current inventory data table are able to quickly identify the products store location and department by viewing the general ledger code. The first two numbers of the general ledger code indicate the store location of the inventory, while the third number indicates the department the inventory belongs to, and the last two numbers...
Words: 599 - Pages: 3
...locations in California is faced with increasing productivity while controlling costs internally. In short, Kudler Foods has to manage costs and use technology to aid in the process of streamlining operations in all locations. The purpose of this analysis is to provide a clear and concise analysis of the method Kudler Fine Foods is currently using and offer recommendations to improve efficiency by capturing important data rather than extemporaneous data. Specifically, the current system used by Kudler Fine Foods does little to provide a true picture of inventory sources and the effect of the data on Kudler Fine Foods bottom line. The current data table found on Kudler Fine Foods Finance & Accounting tab simply lists GL codes, products, and cost. While this information provides basic information regarding inventory and costs, the data table does not break out for users of the data comparable data to make decisions regarding inventory. One benefit of the data table currently used is that it provides departmental data that can be used and analyzed by department managers. Recommendations Data tables, specifically a pivot table can provide essential data that can be used not only departmentally, but also across the organization. The data extracted can be then analyzed and compared location to location providing management insight on cost expenditures and vendor inventory. Many...
Words: 607 - Pages: 3
...REPORT ON INDUSTRIAL ATTACHMENT WITH ST AEROSPACE SYSTEMS PREPARED BY: ZHANG ZHIRONG 054535B06 CE Table of Contents Page Abstract Acknowledgement List of Figures i ii iii Chapter One: Introduction 1.1 Background 1.2 Purpose 1.3 Scope 1 Chapter Two: Approved Certified Holder Test System 2.1 Background, Purpose and Scope 2.2 Admin Menu Items 2.2.1 Login Page 2.2.2 Admin.’s Menu 2.3 Admin operations 2.3.1 Assign Examinee Paper 2.3.2 Generate Report 2.3.3 Set Questions 2.3.4 Set Paper Type 2.3.5 View Examinee answer archive 2.4 Examinee operations 2.4.1 Confirm Particulars 2.4.2 Take Test 2.4.3 View Results 2.5 Security 2 2.5.1 Direct Question Bank Access 2.5.2 Linked tables 2.5.3 User application Chapter Three: Incident Reporting System 3.1 Background, Purpose and Scope 3.2 Menus 3.3 Handling Forms 3.3.1 Enable Data Entry 3.3.2 Refreshing Forms 19 Chapter Four: Library Loans System 4.1 Background, Purpose and Scope 4.2 New Loan Record 4.2.1 Using Sub Forms 4.2.2 Borrower Friendly Design 4.2.3 Clean Records 4.3 Return of Manual 4.3.1 Design Methods 24 Chapter Five: Roster Database System 5.1 Background, Purpose and Scope 5.2 Report Viewing Methods 5.2.1 Order by 5.2.2 Group by 5.2.3 Search by 29 5.3 Keeping track of Last Update Chapter Six: Approved Certified Holder Database System 6.1 Background, Purpose and Scope 6.2 Manipulating Records 6.3.1 Deleting Single Record 6.3.2 Detecting overdue and prompting 35 Chapter...
Words: 7431 - Pages: 30
...Data Table Analysis University of Phoenix Accounting Information Systems ACC/542 Anita Rodriguez April 2, 2012 Data Table Analysis Kudler Fine Foods was founded by Kathy Kudler in La Jolla in 1998. In the beginning, Kudler Fine Foods had only one store, causing Kathy Kudler to make the decision to use the Microsoft Access database to monitor and maintain sales, employees, inventory, customers, and orders. Because Kudler Fine Foods inventory was perishable, it was imperative to track properly these items. Possessing too much inventory was always a concern of Kudler Fine Foods, if the inventory information was inaccurate, outdated, or incomplete the information would be of no use to the company. This paper will present suggestions for enhancement, evaluate database tables from an accounting point of view, and generate a pivot table to assist the management of Kudler Fine Foods with preparing improved reports in accounting and enhance the decision-making process. Evaluate the design elements of the data tables from an accounting perspective The inventory tables allow the users to establish when to order additional inventory and what inventory is on hand. The use of data table analysis is of the essence for this process. The data table is intended to categorize the data into departments, financial codes, items, and it adds the total amount for the transaction codes. It also adds the amount of items in the inventory during a specified period. The data table design...
Words: 903 - Pages: 4
... |School of Business | | |ACC/542 Version 2 | | |Accounting Information Systems | Copyright © 2009, 2007 by University of Phoenix. All rights reserved. Course Description In this course, students examine the fundamentals of accounting systems design. Topics include business information systems, business processes and data flows, database concepts and tools, internal control and risks, auditing the information system, and using the information system to perform audit functions. Policies Faculty and students/learners will be held responsible for understanding and adhering to all policies contained within the following two documents: • University policies: You must be logged into the student website to view this document. • Instructor policies: This document is posted in the Course Materials forum. University policies are subject to change. Be sure to read the policies at the beginning of each class. Policies may be slightly different depending on the modality in which you attend class. If you have recently changed modalities, read the policies governing your current class modality. Course Materials Bagranoff, N. A., Simkin, M. G., & Strand Norman...
Words: 2534 - Pages: 11
...Assignment 3 (100 marks) ------------------------------------------------- Top of Form Bottom of Form (Covers Modules 6, 7, and 8) Note: If this is your first time using the Online Learning Environment, check out the Course Orientation and the quick tutorials in the Support Centre. General assignment FAQs can be found in your Assignment Submission area. Prepare the answers to these assignment questions in Word and save them as one Word document on your hard drive. For the recommended format and filename, see the FAQs in the Assignment Submission area. If this assignment Word file requires the pasting of Accpac.RTF reports, or Excel.xls sections, or other files, students are strongly advised to refer to the How To/Use Software/Use Excel, Use Word or both, to ensure the successful submission of their complete assignment. Multiple-choice questions are to be completed within the Online Learning Environment in your MS2 Assignment Submission section. This portion of the assignment will be automatically graded. Do not include your answers in your Word document as they will not be graded. When your file is complete and you are ready to submit it for marking, select your Assignment Submission area. For help, refer to the quick tutorial, “Submit your assignment.” Follow these steps to ensure that your assignment was received by your marker: * Select the Grade Centre link. * Select the exclamation mark (!). In the section “Your work,” select the file. If you can view...
Words: 1736 - Pages: 7
...Exam Case PRACTICE Exam From the Pivot Info sheet: a) Create a Pivot Table on a new worksheet that Averages the Sales amount by Specialist (row label) and Date (column label). b) Use the Group Field property to display the Date field by month (p. EX 289). Click on one of the dates, click the Group Field button in the Group group then click Months c) Format the values as Currency with two decimals Hint: You should see four columns: Jul, Aug, Sep and Grand Totals. The Grand Total should be $477.42. The Aug Total should be $700.00 d) Rename the sheet with the Pivot Table. Use the name PivotTable (space removed). e) Add a Slicer to Filter by Type of Customization and apply it to show only Pin Striping. Your Grand Total should be $3,930 On the January sheet: a) Group (p. EX 304) the January, February and March sheets together: b) Calculate the difference between each product group: Actual-Forecast. c) Calculate the % change for each product group: Difference/Forecast d) Calculate the total revenue for the Forecast, Actual, and Difference columns, and then calculate the total %Change e) Ungroup the sheets f) Make a copy of the March sheet and name it Summary. Place the Summary sheet right after the March sheet. Delete B5:C9 from the Summary sheet. You will create 3-D cell reference formulas (also known as drilling formulas) to total data across the months. g) On the Summary sheet in cell B5, create a 3-D cell reference...
Words: 1218 - Pages: 5
...Hermione_Granger_91023_G.xls Assignment- i) Use column V to create a new character variable of length 10- ABCDEFGHIJ where AB=Region, CD=Province, E=Product Category, FG=Customer Segment, H=Ship Mode, I=Order Priority, J=Product Container. (You’re free to use your own nomenclature. Specify the same in the assumptions sheet. Eg- Use initial 1(or 2) characters of the corresponding variable.) ii) Summarize the data using a pivot table by region and province. Subsequently, find the province with highest profit per shipment and highest profit per Unit. iii) Create a pivot table where you provide a summary of sales, cost and profit statistics. How will you summarize? State the variables used as row labels, column labels and filters. Justify your selections. Do you think it would make sense to use some of the statistics variables in percentage terms? If yes, create those variables in the pivot table. (Note: The new variables are to be created in the pivot table itself. You’re not supposed to create the variable in the raw dataset and then using it in the pivot table). iv) Find...
Words: 607 - Pages: 3
...|BUSINESS & MANAGEMENT |[pic] | ASSIGNMENT FRONT SHEET |Student Name: | |Certification : I certify that the whole of this work is the result of my individual effort and that all quotations from books, periodicals etc. | |have been acknowledged. | |Student Signature: |Date: | |Student Registration Number: | |Student email address : | |Programme : Business |Year/Level : 1 | |Academic Year : 2012/13 |Semester : 2 | |Module title : Academic & Personal Development ...
Words: 1335 - Pages: 6
...tied up in Kudler’s inventory making this a top priority to improve. Kudler needs a standardized ordering system to keep track of their inventory. As of now they are using a computerized ordering system, what they need is a system that will help them keep track of their inventory as it is sold or purchased. Kudler also needs an accounting system that will help them with payroll, accounts payable, and sales. A network that will connect sales, accounts payable, payroll, and inventory all together. Having a system that will connect all departments will allow for a more accurate financial statement. This will also allow for the managers to see expenses with each department, and see which item is being sold more. Another opportunity that Kudler needs is a system that would allow authorized personnel to enter all three stores from any location. Kudler would benefit from a network that linked managers together in order from them to view and enter data from any location. From an accounting perspective the inventory data table Kudler Fine Foods uses does not provide enough information. It does seem that the data table is easy to use and understand, but it is lacking in many areas. The data table does not give enough information as to how much the stores are spending on a department. A well-organized pivot table will allow more information to be presented; it...
Words: 400 - Pages: 2
...Data Table Analysis LeKane Gilling ACC/542 March 13, 2014 Brad Thompson Abstract In this brief, data tables will be analyzed because it can be critical to the accounting cycle. With accurate data that is informing, Kudler Fine Foods ought to be able to make more practical business decisions. With Kudler Fine Foods using data table to order, keep inventory, and to process their products, they can become more successful and on top of the food industry. As a company, Kudler needs to evaluate their design elements of the data tables that are needed for review from an accounting perspective. An entity relationship program would need to be developed showing the past data tables. A recommendation would need to be met for improvements or changes. With providing vital information in the pivot tables, then this will improve making decisions for every department at Kudler Fine Foods. By creating a pivot table that shows everything in a list, this will possibly help management see where items need to either increase or decrease because they're not selling. Data Table Analysis Kudler Fine Foods are a part of a broad spectrum of prestigious locations that are becoming successful. La Jolla, Del Mar, and Encinitas are the three stores that Kudler has opened, all located in California. The products range from fresh bread, harvest, meat and poultry, flavorings and packeted foods, and special milk and cheese products. Since 1998, Kudler’s goal is to supply the consumers carefully chosen...
Words: 1218 - Pages: 5
...1 Commence Date : Week 6 Deadline Date : Week 11 Unit Controller / Examiner : Suha Salem Contact Number : ext 8406 E-mail : ssalemp@gmail.com ------------------------------------------------- Coursework Requirements: 1. You are required to come out with a Microsoft Excel application to solve next Exercises 2. Open new workbook in Microsoft Excel and save as “Group Assignment”. 3. You are required to solve all the exercises and each exercise in different sheet. This group project can have Maximum 3 students. Exercise1 In this exercise, practice Conditional Formatting 1- Open a new sheet and Rename the sheet as “Exercise 1” 2- Type the next data and format the data as a Table. 3- Set up the rule that Excel formats any cell within a range with yellow fill color and dark yellow font color whenever it contains the word “Dairy”. 4- Set up the rule that excel formats any cell within a range with light red fill color whenever it contains the amount value less than ($500). Exercise 2 In this exercise, practice simple and complex formula...
Words: 1008 - Pages: 5
