Excel Assignment
READ THE ENTIRE PROBLEM BEFORE BEGINNING TO DEVELOP THE SPREADSHEET.
Overview
As a starting point for this assignment, you will be provided with a spreadsheet that is very similar to the one you produced in Excel Assignment #2. It will contain data for a fictional company. The formulas on the spreadsheet will be locked so you cannot see them or change them, but you will be able to change the values of input variables and you will be able to add additionalformulas when you need to. Most of the input variables will be set to zero when you receive the sheet.
Rename the workbook yourfirstname.yourlastname.analysis (as described above).
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
|
10.1%
|
|
CGS Ratio
|
19.7%
|
|
Tax Rate
|
25%
|
|
SG&A Growth Rate
|
10%
|
|
Depreciation and Amortization Growth Rate
|
8%
|
|
Rental Expense Growth Rate
|
0%
|
|
Other Expense Growth Rate
|
6%
|
|
Advertising Increase
|
$77
|
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.
Part C: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 C." Use this sheet for Part C of the assignment.
Suppose the company predicts a decline in sales revenue by 5% per year for the next four years.Moreover, the company's analysts have revised their predictions of growth in in SG&A to 17% 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 by using the "Drawing" toolbar to make a "Text Box" and also use an arrow to point to the data that support your answer.
Part D: Make a copy of the Part C spreadsheet in your workbook, rename the copy "Part D," and use this sheet for Part D of the assignment.
Assuming the same SG&A predictions as in Part C, the company's goal is to break-even over the four-year projected period 2016through 2019, where breaking even is defined as showing anon-negativesum of the Net Incomes for those four years. Assume also a worsening of the CGS Ratio to 32.4%and an aggressive Advertising campaign that will increase Advertising expenses by $92 million dollars each year (over the previous year). What is the minimum Sales Revenue Growth Rate that would be necessary for your IP company to break-even (as defined above)? Indicate your answer on the sheet by using the "Drawing" toolbar to make a "Text Box" and also use an arrow to point to the data that support your answer.
How to Create a Text Box and an Arrow
Select the "Insert" tab on the ribbon and 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 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.
Attachment:- spread sheet.rar