In this assignment you will be creating a fictional budget using Microsoft Excel.  We strongly suggest you do not use other spreadsheet programs, as they may not have the same features Excel has required for  this assignment.  If you do not have access to the Excel app, you can use a free version of Excel at             office.com that can perform all the functions you need for this assignment. Each student is expected to  make their own spreadsheet – any source of plagiarism will automatically result in a failing grade.

You may choose whatever topic you like, and the information does not have to be accurate. However, you may want to use a topic that will easily meet the criteria outlined below, such as a budget for a     store or personal hobby.

This assignment will be submitted through a submission box on URCourses, not through Replit, as this is not a website. If you submit this assignment to Replit, the markers will not be able to find it and you will receive a 0.

You can either do the assignment all on one page, or separate the assignment into different sheets.

Section 1:

The goal of this section is to create a space on your spreadsheet that will calculate a total income from at least three sources and total expenses from at least five sources. You may choose whatever format you like to accomplish this.

You will then find the difference between the two, and illustrate if the balance is positive or negative using conditional formatting, with a different colour scheme for if the result is above or below 0.  You should make a rule for both when the number is above and below 0 for two rules total.

Lastly, there will need to be a calculation done using an absolute reference to a value found in only one cell, such as a tax value. This value should affect multiple values from your budget.  Absolute values are done using a $ sign – just having a tax value itself doesn’t make it an absolute value.  If you are unsure  what an absolute reference is, see the lab pages for Excel.

Section 2:

For this section, you will make a table, such as an inventory or some other list of items, that has at least 10 items with at least five categories of information. For example, if you were doing a table of music      sales, you could list 10 song names, with their artist name, price, etc. At least two of these categories    must be numeric for the next part.

Once you have the table done, you will use functions to produce some extra information about it. The functions requirements are as follows:

1.   Any two summary/statistic functions, such as average, sum, min, max, etc. As these functions do math, they will need to be used on your numeric categories.

2.    Countifs will be used to count the number of entries that satisfy at least one criterion. The   criteria may be whatever you like, and you may use this function on any categories you like.

3.    If and mid are combined (nested) to make an observation about one category.  A nested

function means they are both in the same box.  Writing both functions in separate boxes will not meet this requirement.

The if function is a true or false test. If conditions are met, it will display one thing, if they are not it will display the other.

The mid function is used to take a chunk of the original field for comparisons.

Some examples you could use would be to check if a date is before or after the 2000s by checking if the first digit is a 1 or a 2, or checking if an order is international by checking if the first letters belong to a   certain country.

All functions should have headings that describe how they were used.

Section 3:

For this section, you will create a chart. You may create a chart using information from a previous            section, or create some other information on the spreadsheet if your other information is too difficult to chart.

The chart may be whatever type you like. It will need to have a title that reflects the contents of the  chart, with both the x and y axis labeled as well. The chart series (information that is being charted)   needs to have meaningful names (not just left as Series A, Series B, etc). Lastly, the chart should have horizontal and vertical gridlines.

Section 4:

These marks are based on the overall appearance of your spreadsheet. You should have labels for the     different sections of the assignment with different colours, sizes, and other font properties. Headings      should have tasteful highlights (background colours). Cell spacing should be well done and readable, and you will need to have a mix of border styles. Lastly, add a picture that represents the overall topic of        your spreadsheet.

When you are finished, drag and drop your spreadsheet into the submission box on URCourses. When you save it, please include your name in the filename, such as yourname_assignment3.xlsx.

Remember not to leave submitting this assignment to the last minute. If you miss the deadline due to a slow connection or service outage, you may get a 0.

Detailed Marking Scheme:

1. Section 1 – Expenses _____ out of 10 marks

____/1 Column or row with at least 3 sources of income

____/1 Column or row with at least 5 sources of expenses