In Part 1 you will create the worksheet. In Part 2 you will use Goal Seek and analyze three different sales scenarios.

Having Trouble Meeting Your Deadline?

Get your assignment on In Part 1 you will create the worksheet. In Part 2 you will use Goal Seek and analyze three different sales scenarios.  completed on time. avoid delay and – ORDER NOW

Do my paper

Assignment Instructions & Requirements

Your supervisor in the Finance department at Med Supply Online Warehouse has asked you to create a worksheet that will project the annual gross margin, expenses, total expenses, operating income, income taxes, and net income for the next six years based on the assumptions below. In Part 1 you will create the worksheet. In Part 2 you will use Goal Seek and analyze three different sales scenarios.

Part 1:

Perform the following tasks:

1. Download the Unit 3 Data File.
2. Complete the following worksheet formatting:
   1. Apply a design theme to the worksheet.
   2. Format the worksheet title in cell A1 to 36-point bold font.
   3. Format the worksheet subtitle in cell A2 to 20-point font.
   4. Enter the system date in cell G2 using the NOW function.
   5. Change the width of column A to 25 and the width of columns B through H to 15.
   6. Change the height of row 1 to 45, row 2 to 25, and rows 7, 15, 17, 19, and 22 to 18.
   7. Enter the six column titles Year 1 through Year 6 in the range B3: G3 by taking Year 1 in cell B3 and then dragging cell B3’s fill handle through the range C3: G3.
   8. Format cell B3 to font size to 14 centered and italicized, with contents angled clockwise (Home | Alignment | Orientation). Use the Format Painter button (Home tab | Clipboard group) to copy the format assigned to cell B3 to the range C3:G3.Increase the height of row 3 to 45.
   9. Change the font in cells A7, A15, A17, and A19 to 14-point font.
   10. Add thick bottom borders to the ranges B3:G3 and B5:G5.
   11. Use the Increase Indent button (Home tab | Alignment group) to increase the indent of the row titles in cell A5, the range A8:A14, and cell A18.
   12. In cell A22, change the font size to 14-point font and underline it.
   13. Select the cells in range B4:G19 and cell B23 apply the Comma style with no decimal places.
   14. Set cells B25 to B27 to Percent with 2 decimal places.
   15. Change the background color in cells A1:G2, A7, A15, A17:G17, A18:G19, A22:B27 to a green color.

1. Complete the following formulas:
   (Note: Use absolute references as noted. The dollar signs before the letter and number of the cell reference indicate that this cell reference is an absolute reference, which means it will not change when the formula is copied to other cells (e.g., $B$25).)
   1. Year 1 Sales: (cell B4) = Units Sold in Prior Year * (Unit Cost / (1 – Margin))
   2. Year 2 Sales: (cell C4) = Year 1 Sales * (1 + Annual Sales Growth(Cell Absolute)) * (1 – Annual Price Decrease(Cell Absolute)) and then Copy cell C4 to the range D4:G4.
   3. Year 1 Cost of Goods: (cell B5) = Year 1 Sales * (1 – Margin(Cell Absolute)) and then copy cell B5 to the range C5:G5.
   4. Gross Margin: (cell B6) = Year 1 Sales – Year 1 Cost of Goods and then copy cell B6 to the range C6:G6.
   5. Year 1 Advertising: (cell B8) = 500 + 8% * Year 1 Sales and then copy cell B8 to the range C8:G8.
   6. Year 2 Rent: (cell C10) = Year 1 Rent + (12% * Year 1 Rent) and then copy cell C10 to the range D10:G10.
   7. Year 1 Salaries: (cell B11) = 17% * Year 1 Sales and then copy cell B11 to the range C11:G11.
   8. Year 1 Shipping: (cell B12) = 3.9% * Year 1 Sales and then copy cell B12 to the range C12:G12.
   9. Year 1 Supplies: (cell B13) = 1.3% * Year 1 Sales and then copy cell B13 to the range C13:G13.
   10. Year 2 Web Services: (cell C14) = Year 1 Web Services + (15% * Year 1 Web Services) and then copy cell C14 to the range D14:G14.
   11. Year 1 Total Expenses: (cell B15) = SUM(B8: B14) and then copy cell B15 to the range C15:G15.
   12. Year 1 Operating Income: (cell B17) = Year 1 Gross Margin – Year 1 Total Expenses or = B6-B15 and then copy cell B17 to the range C17:G17.
   13. Year 1 Income Taxes: (cell B18): If Year 1 Operating Income is less than 0, then Year 1 Income Taxes equal 0; otherwise Year 1 Income Taxes equal 45% * Year 1 Operating Income (Reference: Microsoft IF Function) and then copy cell B18 to the range C18:G18.
   14. Year 1 Net Income: (cell B19) = Year 1 Operating Income – Year 1 Income Taxes and then copy cell B19 to the range C19:G19.
2. In cell H4, insert a Sparkline Column chart (Insert Tab| Sparklines group) for cell range B4:G4 and repeat for the ranges H5:H6, H8:H15, and H17:H19.
3. Select row 4 and freeze the pane containing the year headings. (View | Window | Freeze Panes). Use the scroll bars to scroll down and see how the top three rows stay fixed.
4. Rename the worksheet tab to Six-Year Financial Projection.
5. Delete any unused sheets.
6. Preview the formulas version (see Formulas | Show Formulas) of the worksheet.
7. Save the workbook using the file name IT153_Unit3_Assignment_Your Name.

Instructions Part 2:

1. Using the same workbook from Part 1, make 4 copies of the Six-Year Financial Projection Worksheet (make sure you use the Move or Copy by right-clicking the Tab):
   1. Name the new worksheets Case 1, Case 2, Case 3 and Goal Seek.
   2. Color the 3 Case Tabs using a green color and the Goal Seek Tab a red.
2. On the Goal Seek sheet, use the What-If Analysis button (Data tab | Forecast group) to goal seek.
   1. Determine a margin that would result in a Year 6 net income of $4,000,000.
      (Note: Set Cell G19 to value 4,000,000 by changing cell B27.)
   2. You should end up with a margin of 40.49% in cell B27.
3. Using the numbers in columns 2 and 3 of the table below, analyze the effect of manually changing the annual sales growth (cell B25) and annual price decrease (cell B26) on the net incomes in row 19 in worksheets Case 1, Case 2, and Case 3. The resulting answers are in column 4 of the table.
4. Save the workbook with the latest changes and submit the workbook to the Dropbox.