Homework answers / question archive / Shelly Cashman Excel 2013 Chapters 4-7: SAM Capstone Project 1a Amanda Mitchell 1

Shelly Cashman Excel 2013 Chapters 4-7: SAM Capstone Project 1a Amanda Mitchell 1

MS Excel

Share With

---

Shelly Cashman Excel 2013 Chapters 4-7: SAM Capstone Project 1a

Amanda Mitchell

1. Switch to viewing the Blue's Foods worksheet. Insert WordArt into the

worksheet and update the WordArt and worksheet as described below:

a. Use the Fill – Blue, Accent 1, Outline – Background 1, Hard Shadow –

Accent 1 option for the WordArt. (Note: Depending on your version of

Excel, this may be labelled Fill – Blue, Accent 5, Outline – Background 1,

Hard Shadow – Accent 5.)

b. Update the WordArt text to Blue's Foods. (Hint: Do not include the

period.)

c. Resize and Reposition the WordArt so that the top, left corner is

located in cell B1 and the bottom, right corner is located in cell H6.

d. After you finish updating the WordArt object, remove the worksheet

gridlines from view.

2. 

In the SmartArt graphic, enter the text Blue Sea in the third text

3. 

Add a new shape to the SmartArt graphic and update it as described

below:

a. Insert the text Blue Green into the new text placeholder.

b. Insert the support_SC_E13_CS_C4-7_P1a_BlueGreen.jpg into the image

4. 

Update the formatting of the SmartArt graphic as described below:

a. Change the colors to Transparent Gradient Range – Accent 1.

b. Apply the Polished SmartArt style.

Compare the Blue’s Foods worksheet to Figure 1 in the Assignment file.

5. 

Switch to viewing the All Trucks worksheet. In cell D5, enter a formula

using the SUM function to total the revenue generated by all 3 of Blue

Food’s Trucks.

a. In your formula, use 3D references to total the values in cell D5 on the

Blue Flame, Blue Bird, and Blue Sea worksheets.

b. Copy the formula from cell D5 to the range D6:D16.

c. Copy the formulas you just entered into the range D5:D16 into the

range E5:E16.

6. 

Group the Blue Flame, Blue Bird, and Blue Sea worksheets. Make the

following formatted changes to the grouped worksheets:

a. Merge and center the range A2:F2.

b. Apply the Blue-Gray, Text 2 cell fill color to the range A2:F2.

c. Apply White, Background 1 font color to the merged range A2:F2.

d. Bold the contents of merged range A2:F2 and then apply the Arial font

and a 20 point font size to the merged range.

7. 

With the Blue Flame, Blue Bird, and Blue Sea worksheets still grouped,

find and replace all instances of the word Octber with the word October.

8. With the Blue Flame, Blue Bird, and Blue Sea worksheets still grouped,

use a Header and Footer Element to display the Sheet Name in the

center section of the header. Do not change the Header and Footer

element displaying the current date in the right section of the header.

Confirm that the formatting of the Blue Flame, Blue Bird, and Blue Sea

worksheet matches that shown in Figure 2 in the Assignment file, and

then ungroup the worksheets.

9. Create a copy of the Blue Sea worksheet and insert into the workbook

between the Blue Sea and the Preferences – Blue Flame worksheets.

Update the worksheet as described below:

a. Change the worksheet tab name to Blue Green.

b. Change the worksheet tab color to Blue, Accent 5, Lighter 80%.

c. Change the value of the merged range A2:F2 to Blue Green.

d. Clear the contents of the range B5:E16.

10. Switch to viewing the Preferences – Blue Flame worksheet and make the

following adjustments to the 3-D Pie chart:

a. Resize and reposition the 3-D Pie chart so that the top, left corner of

the pie chart appears in cell A14 and the bottom, right corner appears in

cell F28.

b. Explode the data point that represents the Pulled Pork Plate by 25%.

c. Change the X Rotation of the 3-D Pie Chart to 180 degrees.

Compare the updated worksheet to Figure 3 in the Assignment file.

11. 

Switch to viewing the May Performance – Blue Flame worksheet. Create a

table that contains headers based on the range A5:J36.

a. Apply the Table Style Medium 2 table style.

b. Change the table name to BF_May2017.

12.Switch to viewing the June Performance – Blue Flame worksheet. Delete

the duplicate record in the table.

13. 

In column J, add a calculated field to the table using the parameters

described below:

a. In cell J5, enter Waste as column heading.

b. In cell J6, enter a formula without using a function to calculate the

number of items wasted (or not sold) each day using structured

references. To calculate wasted items, the formula should subtract the

value in the Items Sold column from the value in the Items Available

column.

c. The formula should automatically fill to the range J5:J35. If it does not,

copy the formula from cell J5 to the range J6:J35.

14. Unhide the July Performance – Blue Flame worksheet and then switch to

viewing it.

15. In the July Performance – Blue Flame worksheet, add a Total Row to the

table and then, using the total row, perform the following calculations:

a. In cell G37, use a SUM function to calculate Blue Bird’s total revenue

for July.

b. In cell I37, use the SUM function to calculate the total number of items

sold in July at the Blue Flame truck.

c. Remove any other calculations automatically added to the total row.

16. 

Switch to viewing the Aug Performance – Blue Flame worksheet. Select

the range J6:J36 and use the Quick Analysis tool to apply a conditional

formatting rule to the range. The rule should highlight the cells

containing top 10% waste food values generated by the Blue Flame food

17. 

Filter the table using the Lead Chef column values, so only those records

with L. Hefferon as the Lead Chef appear in the table.

18. Switch to viewing the 2017 – Catered Events worksheet. Sort the table

first by the Truck field from A to Z and then by the Date field from oldest

19. 

In cell K5, enter a formula using the COUNTIF function to total the

number of catered events served by the Blue Flame food truck as

described below:

a. Use the range C4:C49 as the Range parameter.

b. Use a reference to cell K4 as the Criteria parameter.

c. Do not include spaces between the parameters in your formula.

(Hint: You can also use the structured reference Catered_2017 as the

Range parameter.)

20. In cell K6, use the SUMIF function to total the revenue generated by the

2017 events catered by the Blue Flame truck as described below:

a. Use the range C4:C49 as the Range parameter.

b. Use a reference to cell K4 as the Criteria parameter.

c. Use the range G4:G49 as the Sum_Range parameter.

d. Do not includes spaces between the parameters in your formula.

(Hint: You can use structured references for the Range and Sum_Range

parameters.)

21. In cell K14, enter a formula using the VLOOKUP function to determine the

event associated with the Reservation ID shown in cell K13. Use the

parameters below when creating your formula.

a. Use a reference to cell K13 as the Lookup_value parameter.

b. Use the range A3:H49 as the Table_array parameter. (Hint: This

parameter may turn into a structured reference to the table.)

c. Use the number 4 as the Col_index_num parameter.

d. Do not use any value for the optional Range_lookup parameter.

e. Do not include spaces between each parameter in your formula.

22. Go to the Performance Trends worksheet. Copy the range H5:K10 and

transpose the data into the range A5:F8. Delete the range H5:K10,

shifting cells up. (Hint: Do not just clear the contents of the range

H5:K10, as that will not delete the formatting applied to that range.)

23. Import the data from the text file support_SC_E13_CS_C4-

7_P1a_Historical.txt into the worksheet, starting in cell B6. The data in

the text file does not have headers and is delimited with a comma.

24. 

Import the data from the support_SC_E13_CS_C4-7_P1a_Estimates.accdb

into the worksheet, starting in cell A15, and then complete the following

actions:

a. Convert the table (that you just imported) into a range, clicking OK to

when prompted

b. Adjust the column width of columns B:F to 11.00

Compare the worksheet to Figure 4 in the Assignment file.

25. 

Switch to viewing the NewTruckPayments worksheet. Select the range

A2:B6 and, using the Create from Selection option, define names for the

cells in the range based on the values in the left column.

26. In cell D4, enter a formula using the PMT function to calculate the

monthly payment on a home loan given the loan parameters listed in the

range A2:D6. Use the following parameters:

a. Enter a negative sign in front of the PMT function to display the

monthly payment as a positive number.

b. Use the defined names Term (representing cell D3), Rate (representing

cell D2) and Loan_Amount (representing cell B6) in the formula.

c. In your formula, the rate argument should be divided by 12 so that the

annual interest rate is converted to a monthly interest rate.

d. In your formula, the nper argument should be multiplied by 12 so that

the number of payments is calculated on a monthly basis.

e. Do not include any values for the optional fv or type parameters in the

formula.

27. In cell D6, enter a formula without using a function that calculates the

total cost of the food truck. The total cost of the food truck is calculated

by adding the price of food truck to the total interest paid on the food

truck loan. Use the defined names Price and Total_Interest in your

formula.

28. Create a single input data table in the range A11:D23 as described

below:

a. In cell B11, create a reference to the defined name Monthly_Payment

(which represents cell D4).

b. In cell C11, create a reference to the defined name Total_Interest

(which represents cell D5).

c. In cell D11, create a reference to the defined name Total_Cost (which

represents cell D6).

d. Select the range A11:D23 and insert a data table, using cell D2 as the

column input cell for the data table.

Compare your worksheet to Figure 5 in the Assignment file

29.

Select the range A4:D6 and lock the cells. Protect the worksheet without

a password and without modifying any of the default formatting options.

30. 

Protect the workbook structure without using a password.

.