Part A: Place your name in cell A1. Now, suppose several of the input variables (parameters) for your planning

model change as follows. Make your changes to the spreadsheet and rename it "Part A".

	Input Variable
Sales Revenue Growth Rate	4.6%
CGS Ratio	43.7%
Tax Rate	25%
SG&A Growth Rate	30%
Depreciation and Amortization Growth Rate	24%
Rental Expense Growth Rate	0%
Other Expense Growth Rate	17%
Advertising Increase ($ millions)	$22

Note: You should enter these numbers directly, one at a time. Cutting and pasting the whole range may lock the spreadsheet from future use.

Notice that at the bottom of the spreadsheet there is a breakout of the companys sales revenue into its four product lines: Bowls, Cups, Plates, and Napkins. Next to the column for 2022 Sales Revenue, there is a column for a performance evaluation based on sales revenue. Put formulas in cells I40, I41, I42, and I43 (highlighted in yellow) to show the performance evaluation based on 2022 sales for each product.This evaluation is obtained by looking up the sales in the table (in light green) in cells B52 through B56 as follows: Sales less than $10, 000 are unsatisfactory and sales beyond $20,000 are excellent. Sales at or greater than $10,000 but less $15,000 are adequate. Sales at or greater than $15,000 but less than $20,000 are good. You should use the HLOOKUP or VLOOKUP function (whichever is the appropriate one). Hint: This is NOT an exact match lookup (i.e., it affects your choice of the range_lookup option).

Part B: Submit 3 well designed charts corresponding to the data in Part A as follows. Place all 3 charts on a single sheet in your workbook and name the sheet Part B.

(1) A "line" chart showing the trend over the seven years in Sales Revenue, Gross Profit, Earnings Before Taxes, and Net Income. (Put the years on the X axis. Be sure to label the chart well and to include a legend.)

(2) A "column" chart showing the expenses for each of the seven years for the various expense line- items that contribute to Total Expenses. (Put the years on the X axis. Be sure to label the chart well and to include a legend.)

(3) Any "pie" chart that you find managerially meaningful. (Be sure to label the chart well.) Hint: Not all pie charts are meaningful. To figure out if your pie chart is meaningful, ask yourself what it tells you.

Refer to the Charting Hints posted on Blackboard for additional hints with charting in Excel.

2

Part C: Again, focus on the bottom of the spreadsheet where there is a breakout of the companys salesrevenue into its four product lines: Bowls, Cups, Plates, and Napkins. Use these data to create Scatter Charts (Scatter Plots) that make pairwise comparisons of the Sales Revenue from Bowls with each of the other three products for the time period 2016 through 2022.

Scatter Charts with markers but NO LINES are likely to be the most effective type for this task. Create an empty Scatter Plot and then in the "Select Data" dialog box, on the left side, you will need to add the appropriate data. For each Scatter Chart, place Sales Revenue from Bowls on the horizontal axis ("Series X Values" and the other products Sales Revenue (on the vertical axis (Series Y Values"). You can leave the "Series Name" box blank. Be sure that the chart has a meaningful title and that the horizontal and vertical axes are well labeled. If you do this well, you will not need a legend on this chart.

Place these Scatter Plots of a new sheet labeled Part C.

Assess these three charts visually to identify which other products have a sales relationship (a correlation) with paint and describe that relationship. That is, do cups, plates, and napkins have a sales relationship with bowls such that they trend together? Indicate your answer on the Part C sheet byinserting a Text Box that briefly (one sentence per chart) addresses the possible relationships (common trends) between sales of Bowls and the sales of each of the other three products. That is, comment on each of the three (possible) relationships.

Note: A Scatter Chart shows the relationship between two variables. A steep upward slope means that a higher value of the variable on the X axis corresponds to a higher value of the variable on the Y axis during the period you analyzed. A steep downward slope means that a higher value of the variable on the X axis is associated with a lower value of the variable on the Y axis. A relatively flat line, or widely scattered points without a pattern, would suggest that there is not much of a relationship between the two variables.

Make a copy of the Part A spreadsheet in your workbook by right-clicking on the "Part A" sheet tab, selecting "Move or Copy," and checking the "Create a copy" box. Right-click on the new tab and select "Rename" to rename the copy "Part D." Use this sheet for Part D of the assignment.

Suppose that instead of the prediction in Part A, the company predicts a decline in sales revenue each year by 5% per year for each of the next four years (2019 through 2022). Moreover, the companys analystshave revised their predictions of growth in SG&A to 39% per year. Given these predictions, in what year will the company first show a negative net income (that is, a LOSS)?

Indicate your answer on the sheet in a "Text Box" and also use an arrow to point from the textbox to the data that support your answer.

Part E: Make a copy of the Part D spreadsheet in your workbook, rename the copy "Part E," and use this sheet for Part E of the assignment.

Assuming the same SG&A predictions as in Part D the company's goal is to break-even over the four-year projected period 2019 through 2022, where breaking even is defined as showing a non-negative sum of the Net Incomes for those four years. Assume also a worsening of the CGS Ratio to 61.4% and an aggressive Advertising campaign that will increase Advertising expenses by $73 million dollars each year over the previous year (instead of the $22 million dollars in Part A). What is the minimum Sales Revenue Growth Rate that would be necessary for the company to break-even (as defined above)? Show your answer to one decimal place (e.g., 5.3%). Indicate your answer on the sheet in a "Text Box" and also use an arrow to point from the textbox to the data that support your answer.

How to Create a Text Box and an Arrow

Select the "Insert" tab on the ribbon, then Text, and then "Text Box" (on a Mac, select "Insert, then"Text Box"). Move the mouse to the upper left-hand corner of where you want the textbox. Then drag the mouse (press and hold the left mouse button) to indicate the region where you want the textbox. Release the button. Type your text in the box. Use the usual formatting toolbar if you want to format it. Click outside the box. You are done!

To draw an arrow, select the "Insert" tab, then Illustrations, and then "Shapes." Select the arrow icon. Move the mouse to where you want the arrow's tail. Then drag the mouse (press and hold the left mouse button) to where you want the arrow's head. Release the button.