Excel Create Project – 10 marks

Due Date: Monday, April 22nd

Just like in the Word unit, the Excel Create project is where you work OUTSIDE of Jasperactive, and
apply your knowledge in a “live” document environment. You will combine your skills and creativity
to produce a project-based result, while complying with the checklist located at the end of this
document.

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. Don’t forget about the e-book too.

To begin, review Project Specifications (checklist), then launch Excel and open a new Document,
Workbook.

Once you feel that you’ve completed and saved your project, you will return to Jasperactive and click
Submit Exercise in the Create tab. Once your project is uploaded, it will be graded immediately,
according to how well your work aligns to the specifications (checklist). 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).

Project Specifications – Students are to create the content, you won’t receive the
workbook content from your instructor. However, ideas and instructions are below.

Workbook Information: In this project, you will create a new workbook and include two worksheets
Save this new excel document 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.

Worksheet #1

 You will create the first worksheet to track your pretend 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 first worksheet Job Record.

Remember, in this first worksheet you are tracking pretend earnings from summer jobs. For example,
mowing the neighbor's lawn, or walking someone's dog, or pet-sitting, or baby-sitting. This record is
only the first two weeks of July.
Worksheet #2

 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 includes the following specifications from the checklist:

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.