You have been supplied with a spreadsheet that someone else has started, and you will complete it by following the steps below.

Scope

You are a merchandise planner for a retail firm. You have been supplied with a spreadsheet that someone else has started, and you will complete it by following the steps below. The file contains one week’s worth of daily sales data for top selling products sold. There are many blank cells which need formulas. Creating these formulas will test your ability to use absolute and relative addressing, as well as functions, where applicable. Make use of the Auto Fill feature as well as copying and pasting to speed and simplify your work.

Once you have correctly entered the content, you will be asked to format the worksheet nicely, then add a chart to highlight the firm’s results.

General Instructions

Save your work frequently! The values to the right of each instruction indicate the marking scheme. The entire project will be marked out of 35, and is worth 15% of the final grade.

Complete the following steps:

1. Download the file sms210_project1_starter.xlsx.
2. Rename the workbook to XXXxxx Project 1.xlsx, replacing XXX with your last name, and replacing xxx with your first name.
3. Start Excel and open XXXxxx Project 1.xlsx.
4. Rename Sheet1 to Sales and delete the remaining unused sheets in the workbook.
5. Use the SUM function to provide weekly totals for each of the four items in column J for the Sales and Cost of Goods Sold sections.
6. Look up the exchange rate for CDN to US $ conversions.You can use whatever source you like, the Bank of Canada web site has a fantastic feature called “USA-CDA Noon Rate at: http://www.bankofcanada.ca/
7.
8. Enter the current date in cell K17 and format as DD-MMM-YY2
9.
10. Create formulas in column K that convert all the Sales and Cost of Goods Sold from CDN to US $.
11. The Gross Margin $ for the week are calculated in columns C and D of the lower section of the spreadsheet, named “Gross Margins in Absolute $”.
Enter formulas in C20 through C23 that subtract the CDN $ Cost of Goods Sold for the week from the CDN $ Sales for the week for each product. In cell C24, use the SUM function to calculate the total Gross Margin $ of all four items in CDN $.
Enter formulas in D20 through D23 that subtract the US $ Cost of Goods Sold for the week from the US $ Sales for the week for each product. In cell D24, use the SUM function to calculate the total Gross Margin $ of all four items in US $.
1. In cells F20 through F24, use the IF function to determine whether or not the CDN $ Margins (for each product and in total) met the Targets (found in K20 to K24). Appropriate values would be Yes or No.
2. In cell H25, use the appropriate function to count how many of the cells in the range F20 to F23 evaluate to a value of Yes.
3. Format all numeric values to Comma Style with zero decimals.
4. Format the first range of cells in each section (C5:K5, C12:K12, C20:D20, and K20) and the Total Margin $ (C24:D24) to Accounting Style with zero decimal places.
5. Format cells C24:D24 with top and double bottom border.
6. Merge and centre the label in cell A1 across all the columns used in this worksheet.
7. Create a chart that will compare and contrast just the US $ Margins (D20:D23) for the four products. Remember the purposes of each type of chart; one in particular is perfect for displaying a single series of data.
Include the Description of each of the four products in the chart, as Data Labels, and then do not include a Legend.
Add a Title to the chart which clearly identifies the data – what do the values represent, and for what period?
Position and size the chart so that it is precisely over cells B27 to F46 in your Sales worksheet.
1. Insert Sparkline Charts in cells L5:L8 representing the weeks CDN Sales (C5:I8). Format with a style of your choice.
2.
3. Save and close your file.