Questions for Assignment 2 - Introduction Microcomputer Applications | CIS 101, Assignments of Microcomputers

Download Questions for Assignment 2 - Introduction Microcomputer Applications | CIS 101 and more Assignments Microcomputers in PDF only on Docsity! A s s i g n m e n t S S 1 - 2 J K T ' s A u t o S a l e s I n s t r u c t i o n s Determining Profit on Items Sold Purpose: To demonstrate the ability to enter and copy formulas, modify background and font colors, and apply formatting to a worksheet. Problem: You are an accountant for an automotive parts supply company. You specialize in parts for Chevrolet and Lexus automobiles. You are trying to determine the amount of profit made on a recent sale. You know the quantity of items sold, the original purchase price, and the price that each item was sold for during the sale. Instructions: Copy the SS Assignment #2 Data.xls and matching instructions files from the OUTBOX of the FTP Site (T:\Finholt\Outbox\CS101\Student Resources\ Spreadsheets\SS Assignment #2 folder if you re on campus) into your spreadsheets folder on your desktop. Remember to save the file using the file name format, yourname – SS Assignment #2, where yourname is your own last name. Perform the following tasks to create the workbook. You may mark on this test booklet. 1. Use the following formulas to determine the Total Initial Item Cost, Total Gross Sales, and Total Profit. a) Total Initial Item Cost = Quantity Sold * Initial Item Cost b) Total Gross Sales = Quantity Sold * Item Sale Price c) Total Item Profit = Total Gross Sales – Total Initial Item Cost 2. In cell B12, determine the Totals Sales by finding the sum of Total Gross Sales. In cell B13, determine the Total Cost by finding the sum of Total Initial Item Costs. In cell B14, find the Total Profit by subtracting the Total Cost from the Total Sales. In cell B15, find the Percent of Profit by dividing the Total Profit by the Total Cost. In cell B16, use the MAX function to find the greatest Total Item Profit. In cell B17, use the MIN function to find the least Total Item Profit. (Hint: The MIN and MAX functions are on the same toolbar icon as the SUM and AVERAGE). 3. Apply the following formatting to the worksheet titles in cells A1 and A2. a) change the font to bold b) change the font to Times New Roman. c) increase the font size of cell A1 to 24 point and of cell A2 to 18 point d) center the titles across columns A through G e) change the background color to dark blue (column 6, row 1 of the Color palette) and the text to yellow (column 3, row 4 of Color palette). f) draw a bold outline around the range A1:G11( Hint: Z1:Z5 refers to the cells Z1, Z2, Z3, Z4, and Z5). 4. Using the buttons on the Formatting toolbar, apply Currency style format with two decimal places to the ranges C4:G4, B12:B14, and B16:B17 (Hint: look for a $ sign icon on the toolbar). Apply the Comma style format with two decimal places to the range C5:G11. Apply percent style format with just one decimal place to cell B15. 5. Bold, italicize, and center the column titles, and draw a bold border under them in the range A3:G3. Change the background color for the same range to lavender, and change the text color to light blue. 6. In the Total Item Profit column, change the background color to aqua (column 5, row 3 of the Color palette). Draw a bold outline around the values in column G. 7. Bold the item numbers and summary headings (range A4:A17), and change the font to MS Sans Serif or a similar font (if necessary). Widen column A to 17.00 points. Change the background color of the range A12:B17 to sky blue. 8. Change the height of row 1 to 39.00 points. Change the height of row 12 to 21.00 points and the width of columns C through G to 12.00 points. 9. Rename the worksheet tab from Sheet 1 to Sales Profits and check the spelling on the entire worksheet. 10. Use the Conditional Formatting command in the Format menu to color the background yellow of any cell in the range F4:F11 that has a value less than $1,500. (HINT: Cell Value Is Less Than 1500, select the Format button to change the color of the cell.). 11. Enter your name in cell A20. In the cells directly below your name, enter your course identification, computer lab assignment (Excel - Lab Test A), date. 12. Preview the worksheet and put your name and class information in a footer. 13. On the menu bar go to Tools  Options, and in the View tab find and select the Formulas check box to change the display from values to formulas. Or use the CTRL + ` combination to show the formulas. 14. Reset the worksheet to display the values by un-checking the Formulas check box. 15. Create a second sheet in the workbook that contains a 3D pie chart illustrating the profit figures shown on the worksheet displayed as a percentage with labels. Name the tab for this sheet, 3D Pie Chart. (See the example below) 16. Delete the un-used worksheets. 17. Put a copy of your finished workbook in the INBOX on the “T” Transfer Drive.