SAM Capstone Project 1a Blue Foods Excel Solution

Microsoft Excel

PROJECT DESCRIPTION

Clement Aozora is the owner of Blue’s Foods, a fleet of food trucks with a growing following in Chicago, Illinois. Each one of Clement’s trucks (Blue Flame, Blue Bird, and Blue Sea) specializes in a different cuisine, but deliver the same quality food that Blue’s Food is known for. Clement is hoping to expand his fleet with fourth food truck (named Blue Green) specializing in vegetarian faire. He has asked for your support organizing some of the information he needs for this new investment. He wants your help calculating some data about the loan needed for the new food truck along with generating and formatting data related to the current and historical performance of his food truck fleet.

GETTING STARTED

  • Download the following file from the SAM website:
    • SC_Excel2013_CS_C4-7_P1a_FirstLastNamexlsx
  • Open the file you just downloaded and save it with the name:
    • SC_Excel2013_CS_C4-7_P1a_FirstLastNamexlsx
    • Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.
  • To complete this Project, you will also need to download and save the following support files from the SAM website:
    • support_SC_E13_CS_C4-7_P1a_BlueGreen.jpg
    • support_SC_E13_CS_C4-7_P1a_Historical.txt
    • support_SC_E13_CS_C4-7_P1a_Estimates.accdb
  • With the file SC_Excel2013_CS_C4-7_P1a_FirstLastNamexlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

  1. Switch to viewing the Blue’s Foods Insert WordArt into the worksheet and update the WordArt and worksheet as described below:
    1. Use the Fill – Blue, Accent 1, Outline – Background 1, Hard Shadow – Accent1 option for the WordArt.
    2. Update the WordArt text to Blue’s Foods. (Hint: Do not include the period.)
    3. 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
    4. 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 placeholder.
  3. Add a new shape to the SmartArt graphic and update it as described below:
    1. Insert the text Blue Green into the new text placeholder.
    2. Insert the support_SC_E13_CS_C4-7_P1a_BlueGreen.jpg into the image placeholder.
  4. Update the formatting of the SmartArt graphic as described below:
    1. Change the colors to Transparent Gradient Range – Accent 1.
    2. Apply the Polished SmartArt style.

Compare the Blue’s Foods worksheet to Figure 1 below.

Figure 1: Blue’s Foods Worksheet 

  1. Switch to viewing the All Trucks In cell D5, enter a formula using the SUM function to total the revenue generated by all 3 of Blue Food’s Trucks.
    1. In your formula, use 3D references to total the values in cell D5 on the Blue Flame, Blue Bird, and Blue Sea
    2. Copy the formula from cell D5 to the range D6:D16.
    3. Copy the formulas you just entered into the range D5:D16 into the range E5:E16.
  2. Group the Blue Flame, Blue Bird, and Blue Sea Make the following formatted changes to the grouped worksheets:
    1. Merge and center the range A2:F2.
    2. Apply the Blue-Gray, Text 2 cell fill color to the range A2:F2.
    3. Apply White, Background 1 font color to the merged range A2:F2.
    4. Bold the contents of merged range A2:F2 and then apply the Arial font and a 20 point font size to the merged range.
  3. 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.
  4. With the Blue Flame, Blue Bird, and Blue Sea worksheets still grouped, use a Header & Footer Element to display the Sheet Name in the center section of the header. Do not change the Header & 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 below, and then ungroup the worksheets

Figure 2: Blue Flame Worksheet (Page Layout view)

Create a copy of the Blue Sea worksheet and insert into the workbook between the Blue Sea and the Preferences – Blue Flame Update the worksheet as described below:

    1. Change the worksheet tab name to Blue Green.
    2. Change the worksheet tab color to Blue, Accent 5, Lighter 80%.
    3. Change the value of the merged range A2:F2 to Blue Green.
    4. Clear the contents of the range B5:E16.
  2. Switch to viewing the Preferences – Blue Flame worksheet and make the following adjustments to the 3-D Pie chart:
    1. 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.
    2. Explode the data point that represents the Pulled Pork Plate by 25%.
    3. Change the X Rotation of the 3-D Pie Chart to 180

Compare the updated worksheet to Figure 3 below.

Figure 3: Preferences – Blue Flame worksheet

Switch to viewing the May Performance – Blue Flame Create a table that contains headers based on the range A5:J36.

    1. Apply the Table Style Medium 2 table style.
    2. Change the table name to BF_May2017.
  2. Switch to viewing the June Performance – Blue Flame Delete the duplicate record in the table.
  3. In column J, add a calculated field to the table using the parameters described below:
    1. In cell J5, enter Waste as column heading.
    2. In cell J6, enter a formula 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
    3. 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.
  4. Unhide the July Performance – Blue Flame worksheet and then switch to viewing it.
  5. In the July Performance – Blue Flame worksheet, add a Total Row to the table and then, using the total row, perform the following calculations:
    1. In cell G37, use a SUM function to calculate Blue Bird’s total revenue for July.
    2. In cell I37, use the SUM function to calculate the total number of items sold in July at the Blue Flame truck.
    3. Remove any other calculations automatically added to the total row.
  6. Switch to viewing the Aug Performance – Blue Flame 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 truck in August.
  7. Filter the table using the Lead Chef column values, so only those records with Hefferon as the Lead Chef appear in the table.
  8. Switch to viewing the 2017 – Catered Events Sort the table first by the Truck field from A to Z and then by the Date field from oldest to newest.
  9. In cell K5, enter a formula using the COUNTIF function to total the number of catered events served by the Blue Fire food truck as described below:
    1. Use the range C4:C49 as the Range
    2. Use a reference to cell K4 as the Criteria
    3. Do not include spaces between the parameters in your formula.

(Hint: You can also use the structured reference Catered_2017 as the Range parameter.)

  1. In cell K6, use the SUMIF function to total the revenue generated by the 2017 events catered by the Blue Fire truck as described below:
    1. Use the range C4:C49 as the Range
    2. Use a reference to cell K4 as the Criteria
    3. Use the range G4:G49 as the Sum_Range
    4. Do not includes spaces between the parameters in your formula.

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

  1. 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.
    1. Use a reference to cell K13 as the Lookup_value
    2. Use the range A3:H49 as the Table_array (Hint: This parameter may turn into a structured reference to the table.)
    3. Use the number 4 as the Col_index_num
    4. Do not use any value for the optional Range_lookup
    5. Do not include spaces between each parameter in your formula.
  2. Go to the Performance Trends 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.)
  3. 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.
  4. 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:
    1. Convert the table (that you just imported) into a range, clicking OK to when prompted
    2. Adjust the column width of columns B:F to 11.00

Compare the worksheet to Figure 4 on the next page.

Figure 4: Performance Trends worksheet

Switch to viewing the NewTruckPayments 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.

  1. 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:
    1. Enter a negative sign in front of the PMT function to display the monthly payment as a positive number.
    2. Use the defined names Term (representing cell D3), Rate (representing cell D2) and Loan_Amount (representing cell B6) in the formula.
    3. In your formula, the rate argument should be divided by 12 so that the annual interest rate is converted to a monthly interest rate.
    4. In your formula, the nper argument should be multiple by 12 so that the number of payments is calculated on a monthly basis.
    5. Do not include any values for the optional fv or type parameters in the formula.
  2. In cell D6, enter a formula 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.
  3. Create a single input data table in the range A11:D23 as described below:
    1. In cell B11, create a reference to the defined name Monthly_Payment (which represents cell D4).
    2. In cell C11, create a reference to the defined name Total_Interest (which represents cell D5).
    3. In cell D11, create a reference to the defined name Total_Cost (which represents cell D6).
    4. 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 below.

Figure 5: NewTruck_Payments worksheet

Select the range A4:D6 and lock the cells. Protect the worksheet without a password and without modifying any of the default formatting options.

  1. Protect the workbook structure without using a password.

Your workbook should look like the Final Figures below and on the following page. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

$3.00 – Purchase

Leave a Reply

Your email address will not be published. Required fields are marked *



$3.00 – Purchase
  • File Format: .xlsx, .accdb, .txt