MAR 6805 - Excel Exercise

Instructions:

1. Create a new excel file and save the file as (your name)MAR6805ExcelExercise.xlsx (i.e. my

file would be named JohnDoeMAR6805ExcelExercise.xlsx)

2. Enter the text "Company X Revenues" in cell A1

3. Increase the font size to 21, make it bold and italicized, and change it to a different color

than black (any color other than black is acceptable)

4. Enter today's date in cell A2 (the date in the file MUST MATCH the date in which the file is

submitted)

5. Format the date to be in this format: 03-Mar-12

6. Enter the year (starting with 2011) in cell A5, copy down so all years are displayed until

2020, and make these years bold

7. Enter "Jan" into cell B4 and copy across to include all months and make these italicized

8. Enter your Panther ID number into cell B5 and then format this as currency (with 2

decimals)

9. Enter a formula in cell B6 that equals the number in cell B5 but add 1,000 to it. Copy this

formula down so that January is 1,000 more each year than it was the past year all the way

up to 2020.

10. Enter 5% into cell C2 and format it as a percentage with one decimal. Then copy the value

of B5 into C5 and format C5 as currency.

11. Enter a formula into cell C6 that increases the dollars each year by the percentage that is

entered into cell C2. Make sure to use an absolute reference to cell C2 in your formula.

12. Copy this formula down through the year 2020 resulting in the Feb dollars increasing 5%

each year through 2020.

13. In the Mar row (starting with cell D5), using the IF function, enter a formula that compares

the Jan data to the Feb - if Jan is equal to or higher than Feb then return the dollars from

Jan, otherwise show the dollars from Feb. Copy this down through year 2020. Make sure

Mar is also formatted as currency.

14. Copy the data from Jan into Apr and make sure to paste values. Copy Feb into Jun and then

Mar into all the remaining months making sure to paste values for the months Apr through

Dec. Format all these columns (all months) as currency.

15. Create a formula in cell B16 that sums all the data in Jan for all years. Format as currency.

Copy this formula across all months.

16. Enter the text "Total" in cell N4. Make this bold and italic.

17. Create a formula that sums every month up and give a total for each year. Enter this

formula starting in cell N5 (for 2011) and copy it down through 2020.

18. Highlight the cells from B5 through M14 and create a named range for this area called

"Revenues".

19. In cell B18, enter a formula that counts the number of cells in the Revenues range that

contain values larger than the value that is in cell B5 (make sure to use the named range in

the formula and you may enter the actual value of B5 in the formula instead of referencing

the cell).

20. Resize column A so that it is just big enough to display the date in cell A2 without returning

an error (########).

21. Resize column B through N to all be the exact same size (making sure that the numbers are

displayed and there are no errors (########).

22. Create a new sheet in your excel file and name it "Pivot".

23. Open the provided excel file named "data.xlsx". Highlight columns B through E and copy

the contents. Go back to your new "Pivot" tab and paste the data into cell A1 in the "Pivot"

tab.

24. Create a new pivot table with the data on the same sheet starting in cell F4.

25. Create a report in the pivot table showing the sum of amounts by "Type" within "Buyer"

(i.e. a total of each buyer and then the totals of each type within each buyer underneath).

26. Format the "Sum of Amount" column by currency with zero decimals.

27. Create a new tab and name it "Analysis".

28. Go back to the first report (Company X Revenues) and create a clustered column chart using

only the months and the years data (do not include any of the Totals) and place it in the

Analysis tab. Make sure that the years are going across the bottom of the chart and months

are referenced in the legend.

29. Give the chart a title using your name like Your Name's chart (for me would be Vivian's

chart).

30. Resize the chart to fill cells A1 through Q21.

31. Add data labels to the bar chart for only the month of December for each and every year.

32. Save the file, and upload it to the Assignment Dropbox.