Multiple layers COMP1059 Test Lab #1 Excel – Tree Inventory Page 1 of 2 Test Lab #1 – 5% of Final Mark 1. Preparation a) Download the file Tree Farms Data File.xlsx from Test Lab #1. b) Save the file...

...


Multiple layers COMP1059 Test Lab #1 Excel – Tree Inventory Page 1 of 2 Test Lab #1 – 5% of Final Mark 1. Preparation a) Download the file Tree Farms Data File.xlsx from Test Lab #1. b) Save the file as StudentNumber_Tree_Farms_Inventory.xlsx. c) Rename Sheet 1 to Raw Data d) Copy the sheet Raw Data to a new sheet called Tree Inventory. Use this new sheet to complete the rest of the instructions. 2. Instructions a) Create a formula in cell E4 that calculates the value of the on-hand inventory, based on the price paid for the item in B4. Format the cell in the Comma Style (use a comma as a 1000 separator). b) Use an absolute reference to calculate the sale price of one item in cell F4. Calculate a markup of 45% using the mark-up value shown in cell I1. c) Copy the formulas created above into the range E5:F14; first convert any necessary cell references to absolute so that the formulas work correctly in all cells. d) Calculate the total value of all inventory on hand in E15 and add an outside border. e) Add the bold attribute to the column headings, and italicize the items in Column A. f) Ensure all the columns are wide enough to display the data and headings. g) Format the Sale Price column so it displays the Accounting Number Format with two decimal places. h) Change the Price Paid column so it displays the Comma style with two decimal places. i) Add a row under Red Oak for “White Oak”, price paid “$0.31”, sold individually “each”, with “156” on hand. j) Merge & Center “Moose Lake Tree Farms” over the data columns. Increase the font size to 20 point & apply the bold attribute. k) Use conditional formatting to call attention to items with a quantity of less than 20 on hand. Use light red fill with dark red text. l) Create an icon set for the range D4:D15 using the symbols of your choosing. m) Delete the row containing the Clips item. COMP1059 Test Lab #1 Excel – Tree Inventory Page 2 of 2 n) Run a spell check and correct all spelling errors. Enter the number of spelling errors into cell I2. o) Freeze Pane in B4. p) Enter your Name, Student Number and Filename in the footer of the document. q) Print Preview your work. Ensure it prints on one page only, prints the columns A-E only and is centred horizontally on the page. r) Save you work and back it up. 3. Submit Your Excel File a) Upload your Excel file under Test Lab #1. 4. Marking Rubric All items in sections 1 and 2 have between 1 and 5 points associated with them. There are 35 total points available.