Cpb-ca-c1.wpmucdn.com



Excel Practice AssignmentsThese assignments will be completed for practice using new excel skills and techniques. They will not be marked but will need to be completed in order to learn skills that will apply towards formal assignments.Practice 1 – Build a calculatorFor this assignment you will practice using the mathematical and formula functions of excel. You will also play around with different formatting styles to create a visually appealing overall layout.Steps: Create a title for your assignment. Have this title be centered within the width that will make up your calculator.Use a function to add the date to the spreadsheet (This date should update and show the current date when the spreadsheet is opened).Create an area for a user to input two different numbers. Be sure to create appropriate headings so that the user knows what to put into the cells.In a column to the right, use mathematical functions to calculate the sum, the difference, the product, the quotient, and the result of the first number to the power of the second number, Be sure to label each of the respective equations noted above so that the user knows what sort of output they will be receiving. Below the information that you have created above, generate a list of 15 random numbers between 0 – 100. Be sure to use a formula for this.Using the randomly generated numbers, use different formulas to find the average, the median, the maximum, the minimum, the sum, the sum of the numbers greater than 50, and the count of numbers (ie total number of numbers) that is less than 50.Format these cells so that the maximum number of decimals shown is 2. Create a formula that will show the Fibonacci sequence. If you do not know what this is then look it up. Calculate the first 16 numbers in this sequence. Spend some time formatting your spreadsheet to create a visually attractive layout. You may want to try different borders, text alignment, shades, etc. Show Mr. Jamieson when you have completed this.Practice 2 – Spreadsheet FormattingFor this assignment you will practice using the mathematical and formula functions of excel. You will also play around with different formatting styles to create a visually appealing overall layout.In this exercise, you will complete the following tasks independently:Format a headerUse a simple formulaCopy dataApply font stylesAdjust column and row sizeWrap textIndent cell dataFormat valuesAdd a border08382000Open a new spreadsheetSet a header with your name in the left section, the current date in the center, and the filename assigned to this exercise by your teacher in the right section.Key the data shown in the table below in the exact cell locations 31756096000Merge and center the title cells for Technology Advancements in all caps and bold across Columns A-FMerge and center the sub-title Sales Income Comparison-2 Years in initial caps and bold across Columns A-FAdjust all column widths to fit contentsCenter all data in Row 4 and in cell range A5:A11Format the data in Columns D and E for two decimal placesChange the title in A4 from Item No to Item NumberWrap the text in A4Merge and center the heading in B4 across Columns B and CReplace “Drive” with “Cartridge” in Cell B8Enter a formula in Cell F5 that will calculate Net ChangeCopy the formula to the remaining cells in Column F and format for 2 decimal placesUse conditional formatting to highlight the items that experienced negative growth.Add and indent the word Total in Cell A12Use a formula to calculate the total net change in Cell F12 and format it for currency.Add a single line bottom border to Row 4, Columns A-FAdd an accounting border to Cell F12Practice 3 – Calculating Bonus PaymentsFor this assignment you will practice using IF statements as well as practice with advanced functions, sorting a spreadsheet, and using basic charts.Part 1:Create a spreadsheet that includes the information in the screenshot below.Use an IF statement to calculate how much bonus should be paid out to the employees listed. Employees only receive a bonus if they sold over $65,000 for the year.Employees earn a bonus at a rate of 10% for every dollar of sales over the Bonus Target. For example, if someone sold $70,000 they would earn a bonus of $500.Calculate the total amount of bonuses paid out to employees this year.Sort the spreadsheet in order of the bonus amount paid. If the amount is equal or “0” then sort by alphabetical order.Format your spreadsheet so that it is visually appealing. Part 2:On Sheet 2 of the same document create a spreadsheet to look like the following:Ensure that your spreadsheet is formatted the same way.You will be sorting the list by t-shirt size and then by colour. In order to do so you will be creating a custom list so that the shirts are order “Small, Medium, Large, X-Large”. The sorting is similar to how you would do so in Microsoft Word. Once sorted, create a count of the number of shirts of each size.Using this small count, create a chart that shows how many shirts of each size are being ordered.Format your chart so that it is visually appealing.Practice 4 – Using IF StatementsIn this activity you will create a spreadsheet, perform simple calculations, fill a series, and use IF statements.Create a spreadsheet to match the one below.9080510795Enter a formula in Cell I4 to calculate the Value on Hand (Cost*Quantity on Hand) of the beverages in stock. Copy the formula to the remaining cells in the column and format the data as currencyUse the AutoSum Feature to find the Total for Value On Hand of all the beverages in stockEnter an IF statement in Cell G4 so that the word “Reorder” appears if the Cases on Hand is less than or equal to the Reorder Point. If the Cases on Hand has not reached the Reorder Point, the formula should return a value of “No”. Copy the formula to the remaining cells in the column and center the textEnter an IF statement in Cell H4 that will restore the Cases on Hand to 100 (100-Cases on Hand) if the reorder point has been reached. If the reorder point has not been reached, the formula should return a value of “n/a”. Copy the formula to the remaining cells in the column.Sort the spreadsheet in ascending order by Vendor NamePractice 5 – Filtering DataFor this assignment you will practice filtering data to search for specific items that you want to view.Begin by entering the information below into a spreadsheetAdd filters to the data so that you can begin to sort and look for specific instances of information.Add a column after the Equipment Details labeled “PRICE” and create random prices for the different items.Apply the currency formatting to the prices column.At the bottom of the table add the following subtotal functions in whichever row you choose – SUBTOTAL(SUM), SUBTOTAL(COUNT), SUBTOTAL(MAX). For Sum, you will show the sum of the prices of all the items being shown after applying a filter, count will show the total number of items being shown, and max will show the highest priced item.Use a filter to look only at the Laptops.Clear the filterUse an advanced text filter to view all the items that do not have “Cam” in their details.Use an advanced date filter to view all the items that were checked out between April and August of 2010.Use an advanced number filter to view all the items that have ID #’s less than 3000.Practice 6 – Using different chartsFor this assignment you will practice using different charts.Begin by creating the following spreadsheet. Format the information as a table. This will automatically apply filtering. Rename the sheet to say “FICTION BOOK SALES”Create charts to match the ones below. You can format your charts to appear visually different. Add titles to all of your charts.Add data labels to one of your charts.Add axis labels to one of your charts.Put each chart on a different sheet. Properly label all of the sheet names.Use formulas to calculate the following:Average sales for each year and each different book typeCalculate the total sales for each yearCalculate the % change in sales from year to year. This is calculated by (Sales from current year – sales from previous year)/Sales from current year).Practice 7 – Using SparklinesFor this assignment you will practice using sparklines.Begin by copying out the spreadsheet belowInsert a sparkline for the first row. You will find sparklines in the same area that you would find charts. A sparkline is basically a small chart that fits into a single cell.Choose to use either a line or column sparkline.Add markers for the highest and lowest points. Change the color of the sparkline and the color of the high and low point markers.Calculate the total sales for each month and each individual sales person.Find the average for each month and each individual sales person.Use conditional formatting to show who was in the top 10% and the bottom 10% in terms of sales for each month. You will have to do this column by column. Use conditional formatting to show who the top 10 sales people were in terms of overall sales. Practice 8 – Using Pivot TablesFor this assignment you will practice using Pivot Tables. Pivot Tables can be difficult to use at first. Check out these videos for support. Pivot Tables Part 1 Pivot Tables Part 2Start by entering in the data as shown below:Insert a pivot table to begin to analyze the data. First create a pivot table to determine how much was sold in each region. Add a second row to see how much each salesperson sold for each respective region.Add the month category to the column section to see how much was sold each month by each salesperson in their respective region.Move the Salesperson category to the Filters section. View the different sales totals by each respective salesperson.Add a slicer to your pivot table. You can pick to slice the data by any category you wish.Insert a Pivot Chart to your spreadsheet. Format and reposition items in your spreadsheet to make it visually appealing. Practice 9 – What-If Analysis – Car BuyingFor this assignment you will practice using the data analysis tool – What-If Analysis.Begin by finding a new car to buy online. You can find any car you want and modify or add to it in any fashion that you desire. Make sure you have all the information on the total cost of the car.Assume that you will be taking out a loan for the entire amount of this car and will be paying that loan off over plete a small chart in excel similar to the one below:Do not enter in an interest rate or Payment amount. We will be using the Goal Seeking tool to find what our optimum interest rate would be. Use the PMT formula to calculate your payment amount with the following information – RATE = B4/12, NPER = B3, PV = B2.Use the What-If Analysis: GOAL SEEKING tool to calculate out what your interest rate would have to be in order to have a monthly payment of $1500 on your car loan.Use the What-If Analysis: SCENARIO MANAGER tool to be able to look at different lengths of loans and interest rates. Create 5 different scenarios for possible monthly payments. Create both a Scenario Summary Pivot Table Report and a Scenario Summary Report. Be sure to have the Payment Cell selected for your Results cell.Use the What-If Analysis: DATA TABLE tool to create a table of different possible scenarios for your monthly payments. A data table allows you to view many different results at once all in a table. To use a data table you need to select a range of cells with your payment amount cell being selected for the top left cell of the table. Select the Term cell for the row input and the Interest Rate cell for the column input. You will then go about putting in different lengths of Terms (ie 12, 24, 36, 48, 60, 72 months) and different interest amounts. Your data table will display all the information about what your monthly payments would be based on different terms and different rates.Format your table so that it is visually appealing. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download