Possible Events

In this assignment, you will have data about a small company selling memory sticks, with much of the data similar to the example we discussed in class (remember Alibaba.com?). Your company is engaging in planning for the months of February, March, and April. An Excel sheet has been prepared with an initial set of data, and the employee who prepared it has since left the company. The data is incomplete and does not include some important contingencies/possible events. As an intern with knowledge about Excel, your manager asks you to run some analysis to provide more bits of information. In addition, he introduced some possible scenarios and asked you to inform him about the effects of those scenarios – were they to materialize- on the company’s profit.

Information:

1-Unit Contribution Margin= Sales Price per Unit – Variable Cost per Unit

2-Gross Profit = Total Sales – Total Variable Costs

3-Net Profit (Loss) = Gross Profit – Total Fixed Costs per period

4-Profit Margin= Net Profit (Loss)/Total Sales

5-All calculations need to be made in Canadian dollars.

6-Unit cost (from supplier) is USD 3.5 (so you need to convert to Canadian).

7-If your order is more than 1500 units, the supplier will decrease the unit cost to only USD 3.00

Tasks

Initial Data Preparation

Based on the information above (formulas, unit cost, etc.), complete the initial data sheet (make the necessary calculations to fill in the blank cells) (10 points).

Questions

1-In the cell below the Profitability Analysis (in the Initial Data sheet), calculate the average of profit margin across the Forecast Months (Forecast Months are February through April). If the target average profitability for Forecast Months is 12%, is the average you have just calculated acceptable? (5 points).

2-Create Sparklines for each of Total Sales, Total Variable Costs, and Profit Margin for Forecast Months to show the manager the pattern -if any- in the data (5 points):

a)Use a Line sparkline.

b)The weight of the line should be 1.5 point.

c)The color for the Total Variable Costs sparkline should be red.

d)Hint: while selecting the cell where you have your sparkline, click on the Design tab and then on the small arrow on Edit Data. Click on Hidden & Empty Cells, and then make sure you check “Connect data points with line”

3-Apply conditional formatting so that (5 points):

a)Any negative value in the Net Profit (Loss) cells has the following format: Light Red Fill with Dark Red Text

b)Any value less than 15% in the Profit Margin cells has the following format: Yellow Fill with Dark Yellow Text

In a new sheet named “Goal Seek”:

4-The manager doesn’t like the loss expected in March, and will run an aggressive promotional campaign to try and reach a 5% profit margin for that month. He asks you to tell him the exact sales volume needed to achieve the target profit margin in March (Hint: for this question, you will use the Goal Seek). After running the Goal Seek function, the result will be displayed in a Goal Seek Status dialog box. Before clicking on OK, you MUST take a screen shot and insert it in the Word document in the answer to this question. Click OK to keep the new sales volume. What is the new sales volume? What is the Average Profit Margin now? (10 points).

In a new sheet named “What If”:

5-You notice that, in the future, you will probably add more sheets to the workbook during your internship. You also know that the exchange rate (USD to CAD) may change at any time, and then you’d need to go to each sheet and update the rate, or else your calculations would be inaccurate. You thus decide to adjust the formulas in the What IF sheet so that you point to the exchange rate in the Initial Data sheet rather the one in the What If sheet (5 points).

6-Use the data validation tool to ensure that the unit price cannot be less than 10 or more than 14 (10 points).

7-Assume that -due to the promotional campaign and other favorable factors- the sales volume for March is expected to be 1800, and for April is 2300. Assume also that if the sales in any Forecast Month exceed 1700 unit, then the sales commission is only 1.5% (rather than the current 2%) and the “Other variable costs per unit” is exactly 1.00$. Use the IF function in the ‘Sales commission’ and ‘Other variable costs’ for every Forecast Month to reflect those assumptions. After applying those assumptions, what is the Average Profit Margin for Forecast Months? (10 points).

8-You know you may leave the company after a few months, and you want to leave a good legacy. You want to make it easier for whoever uses the workbook to understand what it is all about. You thus create an addition sheet, with the name Documentation, and you provide links to various other sheets in the workbook, along with a brief description of each sheet (10 points).

Deliverables

You will need to deliver two files (as detailed below): one MS Word file containing your professional report, and one MS Excel workbook containing the Initial Data worksheet and other related sheets. The worksheet should have a consistent, professional appearance; use appropriate formatting for the cells and worksheet. Dollar values should display the dollar sign and be formatted to two decimal places. Please note: use the Excel data file provided as a template.

You should submit the above in a professional looking report (In MS Word), with a proper title page, a brief introduction describing the reason for the report. – 5 points

1.You should then end the report with a brief conclusion; what you did and how this can help the person(s) or company. – 5 points

2.When solving the questions in excel, you must create a separate worksheet in the excel file for each question. You should then rename that worksheet accordingly. – 5 points

3.When answering the questions in the report, you must rewrite the question, import (embed) the corresponding table or chart beneath the question and then answer the question. – 5 points

4.The report should have headers and footers giving brief information about the case, your name and pages should be numbered. – 5 points

5.Each table or figure should be numbered and captioned. Charts should be properly formatted, with axis labels, legends and clear information. – 5 points