Your supervisor in the finance department at Adaptive Solutions Online has asked you to create a worksheet for the flagship product that will project the annual gross margin, total expenses, operating income, income taxes, and net income for the next eight years based on the assumptions in Table 3 - 9. The desired worksheet is shown in Figure 3 - 84.

In Part 1, you will create the worksheet. In Part 2, you will create a chart to present the data, shown in Figure 3 - 85 on page EX 201. In Part 3, you will use Goal Seek to analyze three different sales scenarios.

Units Sold in Prior Year ........................... 242,847

Unit Cost ........................................... $122.75

Annual Sales Growth ................................. 3.50%

Annual Price Increase ................................ 2.75%

Margin .............................................. 29.90%

1. Run Excel and create a blank workbook. Apply the Celestial theme to the worksheet. Bold the entire worksheet.

2. Enter the worksheet title Adaptive Solutions Online in cell Al and the subtitle Eight-Year Financial Projection for Product X in cell A2. Format the worksheet title in cell Al to 28-point Aharoni and the worksheet subtitle in cell A2 to 18-point Aharoni. Enter the system date in cell 12 using the NOW function. Format the date to the 14-Mar-12 style.

3. Change the following column widths: A = 24.00 characters: B through I = 1400 characters. Change the heights of rows 7, 15, 17, 19, and 22 to 18.00 points.

4. Enter the eight column titles Year 1 through Year 8 in the range 83:13 by entering Year 1 in cell B3 and then dragging cell B3's fill handle through the range C3:13. Format cell B3 as follows:

a. Increase the font size to 12.

b. Center and italicize it.

C. Angle its contents counterclockwise.

Use the Format Painter button to copy the format assigned to cell 83 to the range C3:13.

5. Enter the row titles in the range A4:A19. Change the font in cells A7, A15, A7, and A19 to 14-point Aharoni (or a similar font). Add thick bottom borders to the ranges B 3:13 and 85:15. 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.

6. If requested by your instructor, change the entry in row 14 by inserting your surname prior to the text, Web Services.

7. Enter the table title Assumptions In cell A22. Enter the assumptions In Table 3 - 9 on page EX 198 in the range A23:B27. Use format symbols when entering the numbers. Change the font size of the table title in cell A22 to 14-point Aharoni and underline it.

8. Select the range 84:119 and then tap or click the Number Format Dialog Box Launcher (HOME tab Number group) to display the Format Cells dialog box. Use the Number category (Format Cells dialog box) to assign the Number style with no decimal places and negative numbers enclosed in parentheses to the range 84:119.

9. Complete the following entries:

a. Year 1 Sales = Units Sold in Prior Year * (Unit Cost / (1 - Margin))

b. Year 2 Sales (cell C4) = Year 1 Sales * (1 + Annual Sales Growth) * (1 + Annual Price increase)

c. Copy cell C4 to the range 04:14.

d. Year 1 Cost of Goods (cell B5) = Year 1 Sales * (1 - Margin)

e. Copy cell B5 to the range C5:15.

f. Gross Margin (cell B6) = Year 1 Sales - Year 1 Cost of Goods

g. Copy cell 86 to the range C6:i6.

h. Year 1 Advertising (cell B8) = 1500 + 7.5% * Year 1 Sales

i. Copy cell B8 to the range C8:18.

j. Maintenance (row 9): Year 1 = 500,000: Year 2 = 625,000: Year 3 = 430,000: Year 4 = 525,000: Year 5 = 550,000: Year 6 = 450,000: Year 7 = 400.000: Year 8 = 400,000.

k. Year 1 Rent (cell 810) = 1.050,000

I. Year 2 Rent (cell C10) = Year 1 Rent + (6% * Year 1 Rent)

m. Copy cell C10 to the range D10:110.

n. Year 1 Salaries (cell Bit) = 14% * Year 1 Sales

o. Copy cell B11 to the range C11:I11.

p. Year 1 Shipping (cell 812) = 34% * Year 1 Sales

q. Copy cell B12 to the range C12:l12.

r. Year 1 Supplies (cell B13) = 1.1% * Year 1 Sales

s. Copy cell 813 to the range C13:113.

t. Year 1 Web Services (cell B14) = 75,000

U. Year 2 Web Services (cell C14) = Year 1 Web Services + (7% * Year 1 Web Services)

v. Copy cell C14 to the range D14:l14.

w. Year 1 Total Expenses (cell 815) = SUM (B8:B14).

x. Copy cell 815 to the range C15:115.

y. Year 1 Operating Income (cell 817) = Year 1 Gross Margin - Year 1 Total Expenses.

z. Copy cell B17 to the range C17:117.

aa. 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 35% * Year 1 Operating Income

bb. Copy cell 818 to the range C18:l18.

cc. Year 1 Net Income (cell B19) = Year 1 Operating Income - Year 1 Income Taxes.

dd. Copy cell 819 to the range C19:119.

ee. In cell J4. insert a column sparkline (INSERT tab | Sparklines group) for cell range B4:14.

ff. Insect column sparldines in cells J5, J6. .J8:J15, and J17:J19 using ranges 85:15, B6:16, B8:I8 - B15:115, and 817:117 - B19:119 respectively.

11. Save the workbook using the file name, Lab 3-1 Adaptive Solutions Online Eight-Year Financial Projection.

12. Preview the worksheet Use the Orientation button (PAGE LAYOUT tab | Page Setup group) to fit the printout on one page in landscape orientation. Preview the formulas version (ctrl+) of the worksheet in landscape orientation using the Fit to option. Press ctrl+' to instruct Excel to display the values version of the worksheet. Save the workbook again and close the workbook.

13. Submit the workbook as specified by your instructor.

1. Run Excel. Open the workbook Lab 3-1 Adaptive Solutions Online Eight-Year Financial Projection.

2. Use the nonadjacent ranges B3:13 and B19:l19 to create a 2-D Area chart. You can draw the chart by using the Insert Area Chart button (INSERT tab Charts group). When the chart is displayed, tap or click the Move Chart button to move the chart to a new sheet.

3. Use the Chart Elements button to add a vertical axis title. Edit the axis title text to read Net Income. Bold the axis title.

4. Change the Chart Style to Style 8 in the Chart Styles Gallery (CHART TOOLS DESIGN tab Chart Styles group). Use the 'Chart Quick Colors' button (CHART TOOLS DESIGN tab | Chart Styles group) to change the color scheme to Monochromatic, Color 13.

5. Rename the sheet tabs Eight-Year Financial Projection and Projected Net Income Chart Rearrange the sheets so that the worksheet is leftmost and color the tabs as shown in Figure 3 - 85.

6. Tap or click the Eight-Year Financial Projection tab to display the worksheet. Save the workbook using the same file name (Lab 3-1 Adaptive Solutions Online Eight-Year Financial Projection) as defined in Part 1.

Divide the window into two panes between rows 6 and 7. Use the scroll bars to show both the top and bottom of the worksheet. Using the numbers in columns 2 and 3 of Table 3 - 10, analyze the effect of changing the annual sales growth (cell B25) and annual price increase (cell B26) on the net incomes in row 19. Record the answers for each case and submit the results in a form as requested by your instructor.

2. Close the workbook without saving it and then reopen it. Use the 'What-If Analysis' button (DATA tab | Data Tools group) to goal seek. Determine a margin that would result in a Year 8 net income of $500,000. Save the workbook with your needed changes as Lab 3-1 Adaptive Solutions Online Eight-Year Financial Projection CT. Submit the workbook with the new values or the results of the goal seek as requested by your instructor.
3. How would you use what-if analysis tools to determine what Annual Sales Growth you would need to achieve in order to keep prices steady over the 8-year projection period and achieve a Net Income of 0 in Year 2?

Transcribed Image Text:

FOALA Pejeted etla Predu Annual Sales Growth 4.50% 2.50% 1.50% Annual Price Increase 3.50% 1.00% Case 2 5.00%