Excel 3 and 4

Download as pdf or txt
Download as pdf or txt
You are on page 1of 10

FPIT1032 ICT APPLICATIONS IN BUSINESS

MICROSOFT EXCEL 3 & 4


Lab 1: Modifying a Weekly Inventory Worksheet using What-if
Analysis, Charting and Working with Large Worksheet
Problem:
As a summer intern at Dinah’s Candle Depot, you have been asked to modify the weekly inventory
report shown in Figure 1-1a, on the following page. The workbook, “Lab 1 Dinah’s Weekly Inventory”
is included with the Data Files for Students. You may log in to Google classroom and download the
data file or see your instructor for information on accessing the files.

The major modifications to the payroll report to be made in this exercise include:
(1) Reformatting the worksheet;
(2) Adding computations of quantity to order based on reorder level and weeks to arrive;
(3) Adding calculations to suggest changes in ordering;
(4) Adding current and last month sales for inventory items;
(5) Adding and deleting inventory items; and
(6) Changing inventory item information.

The final inventory report is shown in Figure 1-1b on the following page.

Figure 1-1 a (Before)

Excel 3 -4 Page 1
FPIT1032 ICT APPLICATIONS IN BUSINESS

Figure 1-1 b (After)

Part 1:
1. Start Excel. Download and open the workbook Data File – Dinah’s Weekly Inventory from
Google Classroom.

2. Resave the workbook using the file name “Lab 1 Part 1 Dinah’s Weekly Inventory Report
student name”.

3. Select the worksheet by clicking the Select All button.


Click the Clear button and then click Clear Formats on the Clear menu to clear the formatting.
Bold the entire worksheet.

4. Delete rows 11 through 13 to remove the statistics below the Totals row.
Change all the row heights back to the default height (12.75).

5. Insert four rows above row 1 by selecting rows 1 through 4, right-clicking the selection, and
clicking Insert on the shortcut menu.

6. Change the row heights as follows: row 5 = 48.00; row 6 = 25.50; and 7 = 38.25.
For the range B7:I7, change the format so that the text wraps.
Center the range B7:I7.

7. Delete column B by right-clicking the column heading and clicking Delete on the shortcut menu.

8. Insert a new column between columns D and E.


Change the column widths as follows: A = 25.00; E = 13.00; and F through I = 9.71.
Enter the new column E title Suggested Reorder in cell E7.

9. Insert two new columns between columns F and G.


Enter the new column G title Last Month Sales in cell G7.
Enter the new column H title Current Month Sales in cell H7.
Excel 3 -4 Page 2
FPIT1032 ICT APPLICATIONS IN BUSINESS

10. Enhance the worksheet title in cell A5 by using a 36-point purple Cooper Black (or a similar
font) font style as shown in Figure 1-1 b.

11. Assign the NOW function to cell B6 and format it to the “DD/MM/YYYY” style.

12. Edit the data as followings:


• Delete item Car Air Freshener (row 13).
• Change Mini Jar Candle’s (row 12) cases on hand to 6.
• Change Large Candle’s (row 8) items per case to 8 and cost per case to $131.00.
• Change Incense Stick’s (row 10) sale price per item to $2.00, and
• Incense Holder’s (row 9) sale price per item to $4.75.

13. In columns G and H, enter the current month and last month sales values listed in Table 3-13.

14. Insert three new rows immediately above the Totals row. Add the new items data as listed in
Table 3-14.

15. Center the range B8:F15.


Use the Currency category in the Format Cells dialog box to assign a Comma style (no dollar
signs) with two decimal places and negative numbers within parentheses ‘( )’ to the range
G8:K16.
Draw a thick bottom border in the ranges A7:K7 and A15:K15.

16. As shown in Figure 1-1b, enter and format the Increase Reorder Amt (2), the Decrease Reorder
Amt (1), and the Sales Threshold % (25%) information in the range A1:B3.

Excel 3 -4 Page 3
FPIT1032 ICT APPLICATIONS IN BUSINESS

17. Remove any Totals in the range B16:F16. Update and add totals as necessary so that totals
appear in the range G16:K16.

18. In cell E8, enter an ‘IF’ function by applying the following logic and copy it to the range E9:E15.
If (Current Month Sales – Last Month Sales) / Current Month Sales >= Sales
Threshold %, then Reorder Level + Increase Reorder Amt, otherwise Reorder Level
– Decrease Reorder Amt
or,
=IF((H8-G8)/H8 >= $B$3, D8+$B$1,D8-$B$2)

If (Current Month Sales – Last Month Sales) /


Current Month Sales >= Sales Threshold %

YES NO

Reorder Level + Reorder Level -


Increase Reorder Amt Increase Reorder Amt

19. In cell L8, insert a Sparkline Line chart for range G8:H8.
Copy cell L8 to the range L9:L16.

20. Preview the worksheet. Use the Orientation button (Page Layout tab | Page Setup group) to fit
the printout on one page in landscape orientation.

21. Change the worksheet header, adding your name and group.

22. Preview the formulas version (ctrl+`) in landscape orientation. Save the latest changes.

Continue d’s Lab 1 Part 1


Part 2:
1. Open Excel file named “Lab 1 Part 1 Dinah’s Weekly Inventory Report student name”.
2. Using the numbers in Table 3-15, analyze the effect of changing the Sales Threshold in cell
B3.
The first case’s result in a Suggested Reorder in cell E15 is _______.
The second case’s result in a Suggested Reorder in cell E15 of ________.

3. Save the workbook name as “Lab 1 Part 2 Dinah’s Threshold Cases Report student name”.

Excel 3 -4 Page 4
FPIT1032 ICT APPLICATIONS IN BUSINESS

Excel Formulas: Relative and Absolute Cell References

Excel 3 -4 Page 5
FPIT1032 ICT APPLICATIONS IN BUSINESS

Lab 2: Analysis of Indirect Expenses Allocations


Problem: Your classmate works part time as an advisor for the ReachOut Neighbors not-for-profit group.
She has asked you to assist her in creating an indirect expense allocation worksheet (Figure 2-1) that
will help the not-for-profit administration better evaluate the branch offices described in Table 3-11.

Figure 2-1

Table 3-11
Excel 3 -4 Page 6
FPIT1032 ICT APPLICATIONS IN BUSINESS

Part 1:

Do the following to create the worksheet shown in Figure 2-1.

1. Start Excel. Download and open the workbook Data File – ReachOut Neighbors from Google
Classroom.

2. Apply the color theme: Paper to the worksheet. Bold the entire worksheet.

3. Change the following column widths: A = 30.00; B through I = 13.00; J = 20.00.

4. Enter the worksheet titles in cells A1 and A2.


Format the system date to the “DD-MM-YYYY” style in cell I2.

5. Add a thick bottom border to the range B3:I3.


Sum the individual rows 4, 5, and 6 in the range I4:I6.

6. i. Enter the Square Footage row as shown in Table 3-11 with the comma format
symbol in row 16.
ii. Sum row 16 in cell I16.
iii. Change the height of row 16 to 42.00.
iv Middle Align the range A16:I16.

7. Enter the remaining row titles in the range A7:A17 as shown in Figure 2-1.
Increase the font size in cells A7, A14, and A15 to 14-point.

8. Copy the row titles in range A8:A13 to the range A18:A23.


Enter the numbers shown in the range B18:B23 of Figure 2-1 with format symbols.

9. Use the following formulas to accomplish the prorating:

a. Chicago Branch Office Administrative (cell B8) = Administrative Expenses * Chicago


Branch Office Total Donations / ReachOut Neighbours Total Donations or =$B$18*B4/$I$4
b. Chicago Branch Office Depreciation (cell B9) = Depreciation Expenses * Chicago Branch
Office Square Footage / Total Square Footage or =$B$19*B16/$I$16
c. Chicago Branch Office Energy (cell B10) = Energy Expenses * Chicago Branch Office Total
Donations / ReachOut Neighbour Total Donations or =$B$20*B4/$I$4
d. Chicago Branch Office Insurance (cell B11) = Insurance Expenses * Chicago Branch Office
Square Footage / Total Square Footage or =$B$21*B16 /$I$16
e. Chicago Branch Office Maintenance (cell B12) = Maintenance Expenses * Chicago Branch
Office Square Footage / Total Square Footage or =$B$22*B16/$I$16
f. Chicago Branch Office Marketing (cell B13) = Marketing Expenses * Chicago Branch Office
Total Donations / ReachOut Neighbour Total Donations or =$B$23*B4/$I$4
g. Chicago Branch Office Total Indirect Expenses (cell B14) = SUM(B8:B13)
h. Chicago Branch Office Net Income (cell B15) = Total Donations - (Distributed Goods and
Services + Direct Expenses + Total Indirect Expenses) or =B4-(B5+B6+B14)

Excel 3 -4 Page 7
FPIT1032 ICT APPLICATIONS IN BUSINESS

i. Copy the range B8:B15 to the range C8:H15.


j. Sum the individual rows 8 through 15 in the range I8:I15.

10. i. Add a thick bottom border to the range B13:I13.


ii. Assign the Currency style with two decimal places and show negative numbers in
parentheses to the following ranges: B4:I4; B8:I8; and B14:I15.
iii. Assign the Comma style with two decimal places and show negative numbers in
parentheses to the following ranges: B5:I6 and B9:I13.

11. Formatting the worksheet:


• Change the font in cell A1 to 48-point Britannic Bold (or a similar font).
• Change the font in cell A2 to 22-point Britannic Bold (or a similar font).
• Change the font in cell A17 to 18-point italic Britannic Bold.

12. Use the fill color Olive Green, Accent 1, Lighter 40% and the font color Dark Green Text 2 for
cell A7 and the ranges A1:I2; A15:I15; and A17:B23 as shown in Figure 2-1.

13. Insert a Sparkline Win/Loss chart for the range B8:H8 in cell J8.
Copy the cell J8 to the cell range J9:J13.

14. Rename the Sheet1 sheet as Indirect Expenses Analysis and colour its tab green.

15. Change the worksheet header with your name, and tutorial class. Save the workbook using the
file name, “Lab 2 Part 1 ReachOut Neighbour Analysis of Indirect Expenses student name”.

16. Preview the worksheet. Use the Orientation button (Page Layout tab | Page Setup group) to fit
the printout on one page in landscape orientation using the Fit to option. 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. Save the workbook again.

17. Additional task: Making Decisions – The IF Function


• Insert a row at row 16
• Type the text ‘Profit or Loss’ in row A16 as the row title
• At cell B16, type the formula =IF(B15>=0, “PROFIT”,” LOSS”)
• Then, fill-in-handle for the cell C16:I16.

18. Save the workbook again.

Excel 3 -4 Page 8
FPIT1032 ICT APPLICATIONS IN BUSINESS

Figure 2-2

Continue d’s Part 1


Part 2:

1. Draw a 3-D Column Chart (Figure 2-2) on a separate sheet that shows the contribution of each
category of indirect expense to the total indirect expenses.

2. Chart the nonadjacent ranges A8:A13 (category names) and I8:I13 (data series).

3. Show labels that include value of the column.

4. Do not show the legend. Format the 3-D Column Chart as shown in Figure 2-2.

5. Rename the chart sheet as “3-D Column Chart” and colour the sheet tab red. Move the chart
tab to the right of the worksheet tab.

6. Save the workbook using the file name, “Lab 2 Part 2 ReachOut Neighbour Analysis of Indirect
Expenses student name”.

Excel 3 -4 Page 9
FPIT1032 ICT APPLICATIONS IN BUSINESS

Part 3:

Start Excel. Open Lab 2 Part 1ReachOut Neighbour Analysis of Indirect Expenses.

1. Using the numbers in Table 3-12, analyse the effect of changing the planned indirect expenses
in the range B18:B23 on the net incomes for each branch office.

2. You should end with the following totals in cell I15:

Case 1 = $5,846.00 Case 2 = $124,346.00

3. Use the What-If Analysis button (Data tab | Data Tools group) to goal seek. Determine
a planned indirect Marketing expense (cell B23) that would result in a total net income
of $50,000 (cell I15). You should end up with a planned indirect Marketing expense of
$225,586 in cell B23. Save the workbook using the file name, “Lab 2 Part 3 Q3
ReachOut Neighbour Analysis of Indirect Expenses student name”.

Excel 3 -4 Page 10

You might also like