Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Put a checkmark beside each step that was done correctly. Next, correct the steps that were done incorrectly-Reference: https://drive.google.com/file/d/1Hu3u_UZbUc4Ibn6837tCdoDxSW__eOxo/view?usp=sharing Complete the following steps: 1.Download the

Put a checkmark beside each step that was done correctly. Next, correct the steps that were done incorrectly-Reference: https://drive.google.com/file/d/1Hu3u_UZbUc4Ibn6837tCdoDxSW__eOxo/view?usp=sharing

Complete the following steps:

1.Download the file sms210_project1_starter.xlsx.0

2.Rename the workbook to XXXxxx Project 1.xlsx, replacing XXXwith your last name (Oday), and replacing xxx with your first name (Morris).1

3.Start Excel and open XXXxxx Project 1.xlsx.0

4.Rename Sheet1 to Sales and delete the remaining unused sheets in the workbook.2

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.2

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/

Enter the current date in cell K17 and format as DD-MMM-YY2

Enter the exchange rate in K18.2

7.Make formulas in column K that convert all the Sales and Cost of Goods Sold from CDN to US $.3

8.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 $.2

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 $.2

9.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.3

10. 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.2

11. Format all numeric values to Comma Style with zero decimals.1

12. 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.2

13. Format cells C24:D24 with top and double bottom border.2

14. Merge and centre the label in cell A1 across all the columns used in this worksheet.1

15. Make 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.4

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.

16. Insert Sparkline Charts in cells L5:L8 representing the weeks CDN Sales4

(C5:I8). Format with a style of your choice.

Save and close your file.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Accounting

Authors: Jan Williams, Mark Bettner, Joseph Carcello

18th Edition

1260247945, 9781260247947

More Books

Students also viewed these Economics questions

Question

Jay corporation began the year with 1 5 0 , 0 0 0

Answered: 1 week ago