excel work

Excel Lesson 3

Overview

 Watch the Excel Lesson 3 Video

 Using the L3_Titan_Profit_Loss_Projections Excel file, complete the worksheet using the instructions

below saving it as “firstName_lastName_L3_Titan_Profit_Loss_Projections”.

 Complete the Lesson 3 Quiz using the completed worksheet.

 Upload the competed “firstName_LastName_L3_Titan_Profit_Loss_Projections” to the “Lesson 3

Excel Submission Link”.

Objectives

1. Use IF, TODAY, NOW functions

2. Create formulas using Relative Referencing, Absolute Referencing, and Mixed Referencing

3. Perform What-If Analysis

4. Inserting Rows and Columns

5. Reinforce functions(SUM), simple formulas, and formatting(Color Fill, Font Color, Indent)

Lesson Instructions

Background: You will be completing a Profit/Loss Projections worksheet for the Titan Off-Campus Shops. The

worksheet you will use is the L3_Titan_Profit_Loss_Projections file. The income has been provided for you, it

will be your job to calculate all the projected expenses and profit for the year. Open it and complete the

following instructions. (Note: when no specific cell reference is given, you are to use your best judgement

based upon how the worksheet is set up for you to decide what cells are involved or where a result should be

placed.)

1. Re-save the file to either your desktop or other storage device using the name

“firstName_LastName_L3_Titan_Profit_Loss_Projections”. (Note firstName and LastName are your

own first and last names).

2. Fill the Total Income, Total Expenses, and Profit/Loss rows with Yellow (through column N).

3. Under Income, indent the two income categories two spaces (Sales and Interest). Under Expenses,

indent the six expense categories two spaces.

4. Under Assumptions, insert a blank row above Bonus % and type in “Target for Bonus”, then next to it in

B29 type in $35,000.

5. Using a function, calculate the Total Sales and Total Interest for the year.

6. Using a formula, calculate the Total Income for January (Sales plus Interest), and copy this across for

the remaining months and year total.

7. Calculate the projected expenses (rows 12 through 16 – excluding Bonuses) which are based upon the

percentages under the assumptions as a portion of the Sales amount which appears under Income.

 Starting with the Product expenses for January, you will multiply the Sales for January by the

Product % under assumptions.

 You will then copy this formula across for the additional months.

Lesson 3, P a g e | 2

(In the original formula, you will need to use absolute and relative referencing. Note: If any of these

values are $0, then you didn’t use the absolute referencing correctly.)

8. Continue calculating the projected expenses for the remaining expense categories, excluding Bonuses

(Administrative through Advertising). As above, you will calculate the amount for January, then copy

across to the other months.

9. To calculate the Bonuses for January (row 17), you will use the IF function to compare the January

Sales (B7) to the Target for Bonus amount (B29) under Assumptions.

 If the January Sales meets or exceeds the Target, then they will get a % of the sales as indicated

under Assumptions (multiply the % for Bonuses (B30) by the Sales (B7)). If they don’t meet the

sales amount, then they get $0. Use Absolute and Relative Referencing. Note: It is possible for

some of these results to be $0.

10. Copy the Bonuses formula across for the remaining months.

11. Use a function to calculate the total for each of the expense categories (column N) (sum up each row).

12. Use a function to calculate the Total Expenses for January, and copy this across for the additional

months and the total for the year.

13. Calculate Profit/Loss row for the months and year by subtracting Total Expenses from Total Income.

14. Format numbers using Accounting Style for the top rows of each of the Income, Expenses, and the

Total rows for each in addition to Profit/Loss rows (7, 9, 12, 18, & 20). Format remaining Income and

Expense values to Comma Style.

15. Perform What-If Analysis by changing the Advertising % under Assumptions from 10% to 15%. Copy

the new Total Expenses and Profit/Loss rows and paste them under the Option1 Heading (rows 34 and

35). Note: You need to “Paste Special” and use Values and Number formats.

16. Change the Advertising % under assumptions back to 10%. (Be sure the values under Option 1 do not

change).

17. Perform What-If Analysis again, this time by changing the Sales Force % under Assumptions (B26) from

10% to 18%. Copy the Total Expenses and Profit/Loss rows and paste them under the Option 2

Heading (rows 38 & 39). See note in step 15

18. Change the Sales Force back to 10%

19. Use a function in cell B4 to display the current date. (you may need to format so that only the date

appears)

Project Complete.

 Take Lesson 3 Quiz.

 Close and upload completed workbook to Lesson 3 Excel Submission Link.