Excel 3 and 4
Excel 3 and 4
Excel 3 and 4
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.
Excel 3 -4 Page 1
FPIT1032 ICT APPLICATIONS IN BUSINESS
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”.
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.
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.
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.
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)
YES NO
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.
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 3 -4 Page 5
FPIT1032 ICT APPLICATIONS IN BUSINESS
Figure 2-1
Table 3-11
Excel 3 -4 Page 6
FPIT1032 ICT APPLICATIONS IN BUSINESS
Part 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.
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.
Excel 3 -4 Page 7
FPIT1032 ICT APPLICATIONS IN BUSINESS
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.
Excel 3 -4 Page 8
FPIT1032 ICT APPLICATIONS IN BUSINESS
Figure 2-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).
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.
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