We Helped With This Excel Programming Assignment: Have A Similar One?

MUST READ: General Requirements CHECKLIST for Individual Assignments

1. All assignments are to be done individually. Please re-familiarize yourself with York’s and Schulich’s policies on plagiarism. If you base portions of your code on any source other than your own work, insert a link, or otherwise cite the source within the comments of your code. When copying code, you MUST change:

-          procedure and variable names

-          add your own comments

Without these changes, your work will be considered a straight copy and will share the grade with other instances using exact same code, i.e. 2 works will get 50% of the grade each. Share your code wisely.

2. Create one workbook per assignment question. Name workbook sheets appropriately. Except for event procedures which belong in the sheet and workbook code containers, put your VBA code into a separate module.

3. Submission process

1.       Email the Excel FILES containing your assignment solutions to the instructor before the start of the class on which the assignment is due; make sure you receive a receipt confirmation.

2.       Bring a printed copy of ALL VBA code you create to complete the assignment (all modules, sheets, workbook and forms). Please copy the code into a Word document, and format for reading:

a.       include the question numbers that the code applies to

b.       appropriate indenting & spacing of the code

3.       Submit a hard-copy PRINTOUT of this document in-class on the assignment due date. If you can’t bring it yourself, get someone else do it for you.

4.  Late submissions will be penalized 1 grade point for each hour of delay.

5. The file naming convention for all submitted files MUST follow the format of “LastName FirstName ClassNumberSectionNumber Assignment Number-QuestionNumber”.  Take note of the blanks in the file name and the absence of any special characters.  For example, if I were to submit files for Assignment 1, it would be named “Shcherbin Pavel OMIS6350X Assignment 1-Z”, where Z is the number of the assignment question. Files named in any other format will receive an automatic 1 point deduction. I will receive over 500 assignment emails this term, each with several files, and all need to be processed there and back to 3 graders. Note that email subject should be the same as file name, i.e. “Shcherbin Pavel OMIS6350X Assignment 1”.

6. The “Option Explicit” feature must be “ON” in all questions. You can set this in the VBE in: Tools > Options > Editor tab > “Require Variable Declaration” (1 point).

7. Always be sure to include a button or some other GUI element on your spreadsheet that enables the user to call/execute your programs (2 points). Assume that the program is intended for the user who has no idea about VBA, and design GUI accordingly. Test all user inputs thoroughly, the user must not get VBA errors (3 points).

8. Before submitting the resulting files with macros, make sure they work on a lab computer. Also, double-check that all functionality requested in the assignment is realized (1 point / missing feature).

9. Ensure all subroutines that are longer than a few lines, or with complex non-obvious logic, have comments summarizing the purpose and main logic of VBA code (1 point). All VBA code must be indented and spaced to facilitate reading (2 points).

10. All Excel-generated code must be optimized following 80/20 rule (see Class 3 optimization examples). I.e. if unoptimized code is 20 lines, fully optimized is 5 lines, you target should be 20 - (20-5)*0.8 = 8 lines (2 points). Assignment should contain MINIMUM or NO redundant code, for example if you are applying the same formatting to several cells, you should create a separate procedure rather than copying the same code over several times (2 points).

Winter 2017, Individual Assignment #3

Use “Sales forecast and profit analysis” file for this question.

You are working as an analyst for a Canadian retail company, which sells various products via a chain of outlet stores and a web-site. You are looking at their History of sales for 2012.01-2016.12, and notice that some items were bought in CAD, and others in USD (differs by Product Sub-Category). Everything is sold in CAD. So when the exchange rate fluctuates, the company's profit is affected. However, the previous profitability calculation did not take that into account correctly, and needs to be re-done. You got a hint from the CEO that this analysis, done right, can secure you the promotion you've been looking for. Create an app that will add Profit calculation to the existing data, and will allow the user to explore past and future sales/profitability. Explore how CAD-USD exchange rates may affect 2017 profits.

Assume that this app will be used on daily basis by several people with varying degree of Excel skills, design UI accordingly, add sheets / UI elements as required.

7 points max

Download historical CAD-USD rates and import them into Excel on a new sheet called ‘CAD-USD’. Build a web-query that will add the latest exchange rate to the history stored in the workbook. Explicitly note the sources of your data, both historical and updates, in the sheet – name of the website, and a link to the page.

In the ‘Orders’ sheet, calculate Profit as [Sales CAD * (1 - Discount) - Shipping Cost - Quantity * Unit Cost * Exchange Rate].

Hint: CAD-USD detail level is up to you - make a choice between monthly, weekly or daily. Fill the column in the data table with the Unit Cost Currency, based on Product Sub-category - see ‘Unit Cost Cur’ sheet for details. Populate the column ‘CAD-USD’ in the data table with CAD-USD rate for each transaction.

6 points max

Add new sheet ‘Report’ to the workbook, and on it build UI using 2 Pivot Charts And/Or Tables with slicers connecting them, that would allow the user to explore the past/future Sales/Profit. Main dimensions of analysis will be Region, Product category, and Customer segment. Horizontal axis should be the year/quarter timeline.

In this chart, based on historical sales and profit for 2012-2016, project sales and profit for 2017 by quarter using trendlines that provide the best visual fit for the past data.

Hint: use Group function of the Pivot table to adjust the time dimension to Year/Quarter. Use Ship Date for all dates in this app.

7 points max

Explore how CAD-USD rate may affect company profitability in 2017. Use assumptions where required, and ANY preferred tools from your Excel arsenal (Trendline equation, Scenario, Simulation, VBA, etc.)

Provide a short (1/2 page max) summary of the findings in the report for the CEO.

Depth of analysis, additional charts/graphs providing additional perspective on business, quality of conclusions, convenience of UI will be evaluated, and extra effort will be recognized.