Microsoft Excel Assignment
Jasperactive Microsoft Excel Assignment
he Create project is designed to allow you to work OUTSIDE of Jasperactive, from within the specific Microsoft Office application you’ve just learned, and apply in a “live” document environment your new knowledge. Here, you will combine your skills and creativity to produce a project-based result, while complying with a rubric-like project requirements checklist.
Tip: As you work on this project, it’s okay to return to Jasperactive and use the Lesson exercise Practice option for review or more practice.
To begin, review Project Specifications, print the instructions (optional), then either click Launch Application or just launch the applicable Office product and open a new Document, Worksheet, Presentation and so on, unless otherwise indicated.
Once you feel that you’ve completed the project, and you’ve saved your work accordingly, then return to Jasperactive and from this Create tab click Submit Exercise to upload your project for immediate analysis and grading in relation to how well your work aligns to the specifications. Note: Although this may be determined by your Instructor/Teacher, the Create project can be submitted more than once – that is, you can return to your project file, make corrections or additions, to improve your score result.
IMPORTANT: Do not save your file to the Documents/Jasperactive folder, as content in this folder is deleted automatically once results are assessed by the system. It is recommended that you save your project work, regularly, to a school network directory folder, a jump drive device, and/or more simply to your PC desktop (be sure your school does not clean the desktop folder each day though).
In this project, you will create a new workbook to track your earnings from summer “jobs” (for example, mowing the neighbor’s lawn, or walking someone’s dog, or pet-sitting, or baby-sitting) during the first two weeks of July, and your expenses by category for five weeks of summer fun. Save this new file as Summer Work.
You will then create data for two new worksheets. Enter your data as described in the following paragraphs, and then apply the specifications listed in the table.
You will create the first worksheet to track your daily summer job earnings. List each day for the first two weeks of July in the left-most column, beginning in cell A3. In row 2, add titles for the date, a description of the job, customer first name, customer last name, the time spent (in hours), and the total dollar amount earned. Fill the range with data. Name this worksheet Job Record.
You will create the second worksheet to track your weekly expenses for a variety of costs over a period of five weeks. Add the title “Week” in cell A1, then enter week 1, week 2, week 3, week 4, and week 5 below the title in column A. The costs you will track can be for things such as movies, food, gasoline, clothing, concerts, and so on. Select five “categories” of costs and list the titles for the categories in cells B1 through F1. Fill the range with data; it is acceptable to have cells that contain the value zero (0). Name this worksheet Weekly Expenses.
Additionally, make sure your workbook adheres to the following specifications:
|Ensure that the first new worksheet (the one that contains your earnings data) is named Job Record.|
|In the Job Record worksheet, set the row height for row 1 to 30.|
|Add the title Earnings to cell A1 and center it across columns A through G.|
|Apply the Heading 1 cell style to cell A1.|
|Add the column title Rate to column G and set the cell orientation to something other than 0 degrees.|
|Apply the Long Date format to the dates in column A.|
|Enter a formula in the Rate column that calculates the rate per hour of each job.|
|Apply the range name Rate to the values in the Rate column.|
|In cell A19 enter the text Lowest Rate and insert a formula into cell B19 that calculates the lowest rate. Use the range name in the formula.|
|In the data, locate the customer who paid you the lowest rate and format the customer first name and last name with the strikethrough text effect.|
|In cell A20 enter the text Highest Pay-off and insert a formula into cell B20 that calculates the highest dollar amount earned for a single job.|
|In cell C20, use a text function to display the full name of the customer who paid the highest for a single job.|
|Apply a Data Bar conditional format to the earned dollars column.|
|Clear the conditional format from cell F8 in the earned dollars column.|
|Enter a formula in column I that will display the text “Pretty Good” if the value in the Rate column is $12 or more, and will display the text “Not next summer” if the value is less than 12.|
|In cell A22, set the font size to 20 points, enter the text My favorite customer is, and then wrap the text in the cell.|
|In cell B22, enter a formula that will display the customer name shown in cell C20 in uppercase letters.|
|In cell C22, enter a formula that will display only the last name of the customer listed in cell B22.|
|Insert a header into the worksheet. Type your first and last name in the center section of the header, insert a code to show the current date in the right section, and then return to Normal view.|
|Modify the page setup so that gridlines will appear on the printed output.|
|Set a print area that includes only the data in columns B through F.|
|Configure the print settings so that the sheet will fit on one page.|
|Lock the first two rows and the left-most column in the Job Record worksheet.|
|Ensure that the second worksheet (the one that lists expenses) is named Weekly Expenses.|
|In the Weekly Expenses worksheet, convert the data range to a table with headers.|
|Apply a table style to the table.|
|Name the table JulyExpenses.|
|Add a Total Row to the table and configure each column to show a sum total.|
|Apply the First Column table style option to the table.|
|Add formulas to column H that will sum each row of the table for the five weekly entries. Add the title Weekly Total to column H.|
|Insert Column sparklines in column I for each row of detail data.|
|Insert a column chart that shows the weeks along the X-axis and the expense categories as the legend.|
|Filter the table so that the data for week 3 does not display.|
|Apply a chart layout to the chart.|
|Apply a chart style to the chart.|
|Turn off the display of axis titles and move the legend to the top of the chart.|
|Add the alternative text Spending chart|
|Move the chart to its own chart sheet named My Chart.|
|Add another worksheet to the workbook and name the new worksheet Details|
|Copy the data (cells A2:I16) in the Job Record worksheet and paste it (keeping the source column widths) into the Details worksheet starting at cell A1.|
|In the Details worksheet, clear the cell formatting from the data in column F.|
|Copy the values in column G and paste them back into the same location as values.|
|In cell A20, insert a hyperlink to http://www.monster.com|
|Sort the data in cells A2:G15 in ascending order by customer last name (column D), and then in descending order by rate (column G).|
|Format only the Details worksheet to display formulas instead of results.|
|Add the title Earned and Spent and the subject Summer Money to the document properties.|