Excel Assignment 1
1. Create a projected profits worksheet using the Excel file template available in your D2L course. To download the file,
select the Content tab, then click on Table of Contents, then Excel Assignments, then Excel Assignment 1. Then select
the down arrow to the right of Excel Assignment 1 Template and download the file. You should watch the assignment 1
tutorials before attempting this assignment.
2. Bold all text in column A.
3. Under the Projected Increase Assumptions table enter the following percentages in column B next to each category
and format as percent with 1 decimal place:
10% for Gross Sales
3% for Rental Income
2% for Wages
2% for Contract Labor
1% for Cost of Goods Sold
2% for Advertising
4. This “Projected Profits” worksheet covers a 6 year period so in cell C13 enter the year 2022. Then copy cells B13 and
C13 to cells D13, E13, F13, and G13, respectively. Use what you learned in the Excel tutorials, so you end up with the
years 2021 – 2026 as column headings in row 13.
5. In cell B19, B28, and B30 respectively, calculate Total Income, Total Expenses, and Net Profit (use the sum function
for Total Income and Total Expenses). Format all three as currency with 2 decimal places
6. Gross Sales are predicted to increase by 10% per year for the next 5 years. In cell C15 calculate projected Gross Sales.
Use absolute cell referencing and the percentage Gross Sales increase from the Assumptions Table to calculate the
projected Gross Sales for 2022. Do not use the actual percentage (10%) in the formula. Instead use the cell address
where the Gross Sales Increase is stored in the table. Then copy the formula to the remaining year columns. Adjust
column widths as necessary to properly display data.
7. Sales incentives are not expected to increase so just copy the 5,000 in cell B16 to the remaining year columns.
8. Similar to what you did in #6 above, calculate projected Rental Income, Wages, Contract Labor, Cost of Goods Sold,
and Advertising for the years 2022, 2023, 2024, 2025, and 2026. Use absolute cell referencing and percent increase
from the Assumptions Table. Use the cell address from the table, not the actual percentage in the formula.
9. Supplies are not expected to increase so just copy the 2021 numbers to the remaining year columns.
10. Copy the formulas for Total Income, Total Expenses, and Net Profit to the columns for the years 2022, 2023, 2024,
2025 and 2026.
11. Format all dollar values as number with 2 decimal places except for those already formatted in #5 above.
12. Create a line chart that graphs Total Income, Total Expenses, and Net Profit for the years 2021 – 2026. Place the
chart just below the Net Profit row. Add a chart title “Projected Profits” and make sure the legend correctly identifies
the chart lines as Total Income, Total Expenses, and Net Profit. Make sure the horizontal axis labels display years
correctly.
13. Save the file with your last name and first name as the file name. Using the name John Smith as an example, the
Excel file name would be SmithJohn.xlsx. Upload your Excel file to the D2L Dropbox for Excel assignment 1 by the due
date listed in your calendar and the dropbox. Verify that your assignment is uploaded correctly. This can be
accomplished by opening the uploaded file to verify the file opens properly.
| Projected Profits Worksheet | |
| Projected Increase Assumptions Table | |
| – | |
| Gross Sales | |
| Rental Income | |
| Wages | |
| Contract Labor | |
| Cost of Goods Sold | |
| Advertising | |
| 2021 | |
| Income: | |
| 200,000.00 | |
| Sales Incentives | 5,000.00 |
| 50,000.00 | |
| Total income | |
| Expenses: | |
| 65,000.00 | |
| 20,000.00 | |
| 8,900.00 | |
| Supplies | 21,000.00 |
| 2,750.00 | |
| Total expenses | |
| Net profit |
