Med Supply Online WarehouseSix-Year Financial Projection
Year 1
Sales
Cost of Goods
Gross Margin
Expenses
Advertising
Maintenance1605000 4378000
Rent
2700000
Salaries
Shipping
Supplies
Web Services 250000
Total Expenses
Operating Income
Income Tax
Net Income
Assumptions
Units Sold in Prior
1589712
Year
Unit Cost
59,5
Annual Sales Growth
0,045
Annual Price Decrease
0,038
Margin
0,388
5920000
5050000
3200000
4250000
Unit Outcomes:
• Use Freeze Panes so that column and row titles remain visible.
O
Apply the use of absolute cell references.
• Use the IF function to enter values in a cell on the basis of a logical test.
• Create a 3-D Column chart with appropriate data and formatting.
• Use the Goal Seek function in Excel.
Course Outcome:
IT153-2: Use formulas and functions to perform calculations.
Purpose
This assignment will help you to understand how to use Excel as a decision-making tool, create professional
looking 3-D charts, and manage the viewing of large worksheets on a single screen.
Assignment Instructions
Your supervisor 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.
The desired worksheet is shown in Figure 1 below and is detailed in the instructions.
In Part 1 you will create the worksheet.
In Part 2 you will create a chart to present the data, which is shown in Figure 2 and is detailed in the
instructions.
In Part 3 you will use Goal Seek to analyze three different sales scenarios.
Assignment Requirements
Figure 1
MED SUPPLY ONLINE WAREHOUSE
Six-Year Financial Projection
30-May-11
Year 6
Year 1
Year 2
Year 3
Year 4
Year 5
154,555-333
94.587.864
59,967,469
155,372,931
95.088,234
60,284,697
156,194,854
95.591.351
60,603,603
157,021,125
96,096.928
60,924,196
157,851,766
96,605,281
61,246,485
158,686,802
97,116.323
61,570,479
Sales
Cost of Goods
Gross Margin
Expenses
Advertising
Maintenance
Rent
Salaries
Shipping
Supplies
Web Services
Total Expenses
12,364,927
1,605,000
2,700,000
26,274,407
6,027,658
2,009,219
250,000
51,231,211
12,430,334
4.378,000
3,024,000
26,413.398
6,059,514
2,019,8.18
287,500
54,612,625
12,496,088
5.920,000
3.386,880
26,553,125
6,091,599
2,030,533
330,625
56,808,851
12,562,190
5,050,000
3.793.306
26,693.591
6,123,824
2,041,275
380,219
56,644,404
12,628,641
3,200,000
4,248,502
26,834,800
6,156,219
2,052,073
437,252
55,557,487
12,695,444
4,250,000
4,758,323
26,976,756
6,188,785
2,062,928
502,839
57,435,076
Operating Income
Income Tax
Net Income
8,736,259
3.931,316
4.804.942
5,672,072
2,552,432
3,119,640
3,794,752
1,707,639
2,087,114
4,279,792
1,925.907
2,353,886
5,688,998
2,560,049
3,128,949
4,135,403
1,860,931
2,274,472
Assumptions
Units Sold in Prior Year
Unit Cost
Annual Sales Growth
Annual Price Decrease
Margin
1,589.712
$59-50
4-50%
3.80%
38.80%
Instructions Dart 1.
1. Download the Unit 3 data file.
2. Start Excel. Apply the Civic theme (or a similar theme) to the worksheet by using the Themes button (Page
Layout tab | Themes group). Bold the entire worksheet by selecting the entire worksheet and using the Bold
button (Home tab | Font group).
3. Format the worksheet title in cell A1 to 36-point Copperplate Gothic Bold (or a similar font). Format the
worksheet subtitle in cell A2 to 20-point Verdana (or a similar font). Enter the system date in cell G2 using
the NOW function. Format the date to the 14-Mar-01 style.
4. Change the following column widths: A = 25.00 characters; B through H = 15.00 characters.
5. Change the heights of row 1 to 45; row 2 to 25 and rows 7, 15, 17, 19, and 22 to 18.00 points.
6. 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. Format cell B3 as follows: (a) increase the font size
to 14; (b) center and italicize it; and (c) angle its contents clockwise. Use the Format Painter button (Home
tab | Clipboard group) to copy the format assigned to cell B3 to the range C3: G3. Increase the Row Height
to 45.
7. Change the font in cells A7, A15, A17, and A19 to 14-point Verdana (or a similar font). Add thick bottom
borders to the ranges B3: G3 and B5: G5. 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.
8. In cell A22, change the font size to 14-point Verdana and underline it.
9. Complete the following entries:
*
a. Year 1 Sales (cell B4) = Units Sold in Prior Year * (Unit Cost / (1 – Margin))
b. Year 2 Sales (cell C4) = Year 1 Sales * (1 + Annual Sales Growth(Cell Absolute)) * (1 – Annual Price
Decrease(Cell Absolute))
c. Copy cell C4 to the range D4: G4.
d. Year 1 Cost of Goods (cell B5) = Year 1 Sales * (1 – Margin(Cell Absolute))
e. Copy cell B5 to the range C5: G5.
f. Gross Margin (cell B6) = Year 1 Sales – Year 1 Cost of Goods
g. Copy cell B6 to the range C6: G6.
h. Year 1 Advertising (cell B8) = 500 + 8% * Year 1 Sales
i. Copy cell B8 to the range C8: G8.
j. Year 2 Rent (cell C10) = Year 1 Rent + (12% * Year 1 Rent)
k. Copy cell C10 to the range D10: G10.
I. Year 1 Salaries (cell B11) = 17% * Year 1 Sales
m. Copy cell B11 to the range C11: G11.
n. Year 1 Shipping (cell B12) = 3.9% Year 1 Sales
o. Copy cell B12 to the range C12: G12.
p. Year 1 Supplies (cell B13) = 1.3% * Year 1 Sales
q. Copy cell B13 to the range C13: G13.
r. Year 2 Web Services (cell C14) = Year 1 Web Services + (15% * Year 1 Web Services)
s. Copy cell C14 to the range D14: G14.
t. Year 1 Total Expenses (cell B15) = SUM(B8: B14)
u. Copy cell B15 to the range C15: G15.
v. Year 1 Operating Income (cell B17) = Year 1 Gross Margin – Year 1 Total Expenses or = B6-B15
w. Copy cell B17 to the range C17: G17.
x. 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
y. Copy cell B18 to the range C18: G18.
z. Year 1 Net Income (cell B19) = Year 1 Operating Income – Year 1 Income Taxes
aa. Copy cell B19 to the range C19: G19.
ab. In cell H4, insert a Sparkline Column chart (Insert Tabſ Sparklines group) for cell range B4: G4
ac. Repeat step bb for the ranges H5: H6, H8: H15, and H17: H19
10. Select the range B4: G19 apply the Comma style with no decimal places. Set cells B25 to B27 to Percent with 2
decimal places.
11. Change the background colors as shown in Figure 1 of the Finished Worksheet above. Use a background
color(for example: Teal); add an Accent.
12. Change the worksheet header with your name (Right Side) and course number (Left Side). Change the Footer to
include the Date (Left Side) and Page number (Right Side & use tools). Save the workbook using the file
name, Unit 3_Assignment_Your Name.
13. 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.
Instructions Part 2:
1. Open the workbook Med Supply Online Warehouse Six-Year Financial Assignment if not already open.
2. Use the nonadjacent ranges B3: G3 and B19: G19 to create a 3D StackedColumn Chart. Draw the chart by
clicking the Column button (Insert tab Charts group). When the Column gallery is displayed, click the Stacked
Columnchart type (column 1, row 2). When the chart is displayed, click the Move Chart button to move the chart
to a new sheet and Name the chart sheet 3-D Column Chart.
3. Select the legend on the right side of the chart and delete it. Add the chart title by clicking the Chart Titles button
(Chart Tools Layout tab | Labels group). Click above the Chart in the Chart Title gallery. Format the chart title as
shown in completed chart below.
4. To change the color of the cylinders, click one of the columns and use the Shape Fill button (Chart Tools Format
tab Shape Styles group). To change the color of the wall, click the wall behind the columns and use the Shape
Fill button to change the chart wall color. Use the same procedure to change the color of the base of the wall.
Change colors to a color similar to the screenshot below.
5. Rename the sheet tabs to Six-Year Financial Projection and 3-D Column Chart. Rearrange the sheets so that the
Six-Year worksheet is leftmost and color their tabs (your choice of different colors).
6. Click the Six- Year Financial Projection tab to display the worksheet.
• Save the workbook as Unit_3_Assignment_Your Name.
7. Click the chart area and use the Shape Effects button, click on Format (Chart Tools Format Tab | Shape Effects).
Click 3-D Rotation, 3D Rotation Options. Check the Right Angle Axes box. Click the close button.
Chant
Une che Med
Page Layout Formula
ww
Home
Den
Ae
Ah
Abc
Abc
Abe
EMEELEPE
Cumho
Shoes
Six-Year Net Income Projection
5000,000
4.500.000
3.500.000
3.000.000
2.500,000
2,000
II
DO
L000.000
500.000
Tess
Tera
Tors
T5
H3-D Columart
Sw-Yes Projection
3- Onder het se
Goal Seek
Instructions Part 3:
1. Re-Open the workbook if not already open.
2. Make 4 copies of the Six-Year Financial Projection Worksheet (make sure you use the Move or Copy by right-
clicking the Tab):
a. Name the new worksheets Case 1, Case 2, Case 3 and Goal Seek.
b. Color the 3 Case Tabs using a Green color and the Goal Seek Tab as Red
Table 2 Med Supply Online Warehouse Data to Analyze and Results
Case
Annual
Sales
Growth
Annual
Price
Decrease
Year 6 Resulting Net
Income
in Cell
G19
2,925,008
1
8.45%
5.75%
2
14.75%
23.00%
(2,353,532)
3
25.50%
2.65%
14,668,149
3. Use the What-lf Analysis button (Data tab | Data Tools group) to goal seek. Determine a margin (cell B27) that
would result in a Year 6 net income of $ 4,000,000 (cell G19). You should end up with a margin of 40.49% in cell
B27.
4. In the Six-Year Financial Projection Worksheet, divide the window into two panes. Select rows 6 and 7. Next go to
the View tab-Window group, select Split. Use the scroll bars to show both the top and bottom of the worksheet.
Using the numbers in columns 2 and 3 of Table 2 below, analyze the effect of changing the annual sales growth
(cell B25) and annual price decrease (cell B26) on the net incomes in row 19. The resulting answers are in
column 4 of Table 2 above. Save the workbook and results of the what- if analysis for each case.
5. Delete any blank worksheets and Submit the workbook with the new values or the results of the goal seek.
6. Save the workbook with the latest changes and submit the workbook to the Dropbox for Unit 3.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
