#Chapter 1: Data entry, name and group worksheets, formatting, sorting and filtering, page layout, and forms. Business Problem: How might a manager track inventory quantities-on-hand?
#Chapter 1: Data entry, name and group worksheets, formatting, sorting and filtering, page layout, and forms.
Business Problem: How might a manager track inventory quantities-on-hand?
#Chapter 2: Data validation, formulas, conditional formatting, and defining names.
Business Problem: What tools can designers use to make sure that the data entered is reliable (error-free)? Consider different ways to achieve this goal.
#Chapter 3: Pivot Tables and Pivot Charts.
Business Problem: How can you create a visual display that neatly summarizes data into useful information for managerial decision-making?
#Chapter 4: Invoice with Controls and VLOOKUP.
Business Problem: How can you create an automated invoice with built-in error controls? Explain how the parameters of the form can prevent backdating invoices, entering unrealistic quantities, avoiding calculation errors, and entering incorrect items.
***Base on the title of the 4 chapters above please help me answer the 4 questions below (Do not answer business problem questions, only answer 4 questions below). These questions belong to Excel and Accounting fields. The answers to the questions must have an adequate level of input for each of them, which will require you to think about the ordering process. Just answering each question with a few words is not adequate.
(1) What other information in the workbook would you like to protect, and why is it important enough to protect it?
(2) What would you do to maintain a template invoice, and why is it important to create and maintain the template?
(3) How could you save an individual customer invoice without changing the template, and why is this necessary?
(4) What issues could during the order process, and what are additional error control measures that could be implemented to help mitigate issues with the order?
Chapter 1: Data entry, name and group worksheets, formatting, sorting and filtering, page layout, and forms Business Problem: How might a manager track inventory quantities-on-hand? Vipe will begin by creating two worksheets, each containing one inventory list. Data Entry, Naming, and Grouping Worksheets 1. On Sheet1, create a Candy Inventory table. a. Highlight the Candy Inventory table in the file by clicking on the upper left comer. Note: Do not highlight the title "Candy Inventory b. Copy the information by "right-clicking and selecting copy OR holding down the CTRL key and the "C" key simultaneously. c. Paste the information onto Sheet1 by placing your cursor in cell A1 and "right- clicking and selecting paste OR holding down the CTRL key and the "V" key simultaneously 2. Create a new sheet by clicking the icon to the right of the Sheet1 tab. 3. On the new Sheet2, use the same process to create a Popcorn Inventory table. 4. Name Sheet1 Candy by double-clicking on the tab labeled Sheet1 and typing Candy Note: you may also change the tab color by right clicking on a tab and selecting fab color. 5. Using the same process, name Sheet2 Pop.com Candy Inventory Item Code Wholesaler Cost Price 10 10 10 10 Groovy Inc Bulk Co Cheapster Bulk Co Item Number 501 502 503 504 Item Description Almond Joy Nestle Crunch Reese's Cups Kit Kat Quantity on Hand 176 862 1054 691 .43 52 .61 64 1.30 1.45 1.50 1.60 Popcorn Inventory Item Code Wholesaler Item Item Quantity on Cost Price Number Description Hand 20 Bulk Co 601 Plain 468 .76 1.50 20 Groovy Inc 602 Buttered 879 .87 1.55 20 Groovy Inc 603 Carmel 1323 1.12 1.70 20 Groovy Inc 604 Crunch 737 1.23 2.05 6. Group the worksheets by holding the CTRL key and left-clicking on the two worksheet tabs, or right click on one tab and click on 'select all sheets". You will know that the sheets are grouped because the tab colors are highlighted 7. Note: grouping allows you to make simultaneous changes to all selected sheets to make formatting more officient and consistent. Be aware that formatting only applies to identical ranges, If one list is longer than the other, formatting will not extend to the extra rows/columns s) Focus Formatting 8. Format the data within the worksheets: a. Center text and numbers. Highlight the cells that you want to center, click on the Home tab, select the centering icon listed in the Alignment section of the Home ribbon. b. Widen columns. Highlight the desired area, double-click on the right side of the I column header OR drag the right-hand border of each column until you are satisfied. c. Format numbers as currency. Highlight the cost and price columns, right-click the highlighted area and select Format Cells from the pop-up menu. On the Number tab, Select the following options: Currency Category 2 Decimal places and $ Symbol. d. Ungroup worksheets - right-click on either worksheet tab and select Ungroup Sheets. 9. Format each Inventory list as a table. Highlight the entire table (including the Header row), on the Home tab, in the Styles section, select Format as Table, select a table tyle. Note: be sure My table has headers is checked the in the Format As Table pop- up window. a. Note that drop-down arrows appear next to each header row. These enable you to sort and filter the data b. You may want to group your worksheets and adjust your column widths to accommodate the drop-down arrows. c. Your workbook should appear similar (as you chose your own table style) to the screenshot below x Page Layout Formula Des View Dope Addin QuickBooks Tale Share 15 11 HO Price Item Code 10 10 10 10 Wholesaler. Item Number Groovy Inc 501 Bulk Co 502 Chester 50 Bulk Co 504 Item Description Pamond Joy Nestle Crunch Reese's Cups Kat Quantity on Hand 20 00 175 40 Cost 5035 $0.20 $0.35 $0.25 50.75 $0.75 $1.00 $1.00 5 Candy Popcom e Sorting and Filtering 10. Click on the Candy inventory sheet. 11. Using the drop-down arrows, use the sort and filter functions to: a. Alphabetical Sort Sort by Item Description, ascending. Select the arrow next to the item description column heading, click on sort, A to Z b. Numerical Filter. Show only items that have a Cost between .40 and 50. Select the arrow next to the cost column heading, click on Number Filters, choose Between, enter 40 AND .50. 3 3 Focus c. Additional numerical filter. Display only items with a price of 1.45. Using drop down arrow, uncheck (Select All), and check the box next to $1.45. Your worksheet should be similar to the screenshot below: HA Item Code Wholesaler Item Number 10 Buk Co 504 D Item Description KitKat Quantity on Hand 40 G Cost Price 50 25 $1.00 12. Display all items by turning off filtering. In the Home tab, Editing section, locate the sort and filter icon, in the drop-down list, click Clear. 13. Note, Clear removes all filtering criteria, but leaves the drop-down arrows visible. To remove the drop-down arrows completely, in the Home tab, Editing section, locate the sort and filter icon, in the drop-down list, click the Filter icon. 14. Note: you may also use the sort and filter icon in the Editing section of the Home tab to apply sorting and filtering. 15. Page Layout 16. Change page layout to landscape. Group the worksheets and click the Page Layout tab. Click the Orientation and select Landscape. Note you may also make other printing decisions here, such as setting margins and hiding gridlines. 17. Ungroup your worksheets. Forms (You will not be able to do this on a Mac. You should make appropriate plans to use a friend's computer or a lab computer to complete this part.) Business Problem: Is there a tool to improve data entry efficiency? 18. Add the Form command to the main toolbar. Click the Customize Quick Access Toolbar down arrow and select More Commands (see screenshot below). Home To en Cat New Open O DI Whe Hem Code 10 10 10 10 W ho Buk BUK Ched A Ka Feel Rer extend Tout More 1 Canel ta FGC = == = 11 19. Select All Commands in the Choose commands from: drop-down list. Scroll down to Form... and click on Form to highlight it. Click the Add button and click OK (see the screen shot on the next page) now the Form icon should be visible in the top toolbar (see screenshot below). 5 Note: The form command allows you to enter data in a form view, rather than a spreadsheet view. Desde Am www wye co HP Po tot Custod Actor De MO letem Tento Fone Cade 19 10 TO 10 $ ce a Camere O T 1 hat ce 4 moi Fm PELUDO-AVOIR V V Heading 1 Heading 2 20. With your cursor in the candy table, click the Form icon now located in the Quick Access Toolbar. In the form dialog box, click New and add the items from the table below (next page) using the form Candy 2 X 1 of 4 Item Code: Wholesaler Item Number Nery Groovy Inc 501 Delete HD item Description: Almond joy Quantity on Hand 20 Cost 0.35 Price: 0.75 Find Prev Find Next Criteria Close Item Code Wholesaler Cost Price 10 10 10 10 Cheapster Bulk Co Groovy Inc Groovy Inc Item Number 505 506 507 508 Item Description Twix Milk Duds Heath Baby Ruth Quantity on Hand 711 591 433 657 .72 56 .61 .83 1.35 1.05 1.30 1.55 21. Notice that the items appear in your worksheet as soon as click the New button 22. Click Close when you have entered all the new items. Business Problem: Can Forms be used to search spreadsheets for particular data? 23. Say you want to locate Twix candy. Open the Form and click Criteria. 24. Type Twix in the Item Description field. 25. Click, Find Next. 26. The Twix record will appear in the Candy form 27. Search for other items by quantity item Code on hand, cost, and price. Wholesale 7 X O Best Der Tid Quantity and Cat Pre Finde For Focus Chapter 2: Data validation, formulas, conditional formatting, and defining names Business Problem: What tools can designers use to make sure that the data entered is reliable (error-free)? Consider different ways to achieve this goal. Data Validation 1. Open the Candy inventory worksheet. 2. Create a data validation rule to ensure that the item number has only three digits. 1. Highlight the Item Number column. il. Select Data tab. In the Data Tools section, click the Data Validation icon. . Enter the following information in each tab (see screenshots below): 1. Settings. Under Validation criteria: select Text length from the Allow drop-down list, select equal to from the Data drop-down list, and select 3 from the Length drop-down list. 2. Input message. Make sure the show input message when cellis selected option is checked. In the Title textbox, input Item Number and in the Input message textbox type Item Number must be 3 digits 3. Error alert. Make sure the Show error alert after invalid data is entered option is checked. Select Stop from the Style drop-down list. Input Item Number Error in the Title textbox. Type in Item Number must be exactly 3 digits in the Error Message textbox. iv. Click OK W P 3. Test the above rule by trying to enter an item number with more or less than 3 digits. 4. As you have noticed, the input message that appears when an Item Number collis selected is annoying so remove the input message. 5. Add the following rules. Adjust settings appropriately and provide an error alert. Note: Do not provide an input message a. Quantity on hand must be between 0 and 1500. The Error Alert Style is Information b. Cost must be positive. The Error Alert Style is Warning c. Price must be greater than or equal to 75. The Error Alert Style is Stop Focus Heading 1 Heading 2 Hea f. Select the icon set you want from the Icon Style drop-down list. g. In the Display each icon according to these rules section, set the green icon to > Type Number, Value 700. For the yellow icon: >=, Type Number, Value 500. The red icon will change to Type Number, Value 700. For the yellow icon: >=, Type Number, Value 500. The red icon will change to
