Business Excel Project Part 03

Inventory Artic Frozen Pops Inventory Tracking Item # Product Name Quantity Cost Price Value at Cost Value at Retail Potential Profit POP01 Orange Pop 30 $ 1.00 $ 4.00 $ 30.00 $ 120.00 $ 90.00 POP02 Cherry Pop 20 1.75 5.00 35.00 100.00 65.00 POP03 Chocolate Pop 50 1.50 4.50 75.00 225.00 150.00 POP04 Vanilla Pop 45 1.25 4.50 56.25 202.50 146.25 POP05 Lime Pop 25 1.00 4.00 25.00 100.00 75.00 POP06 Grape Pop 33 1.00 4.50 33.00 148.50 115.50 POP07 Cream Pop 37 $ 1.50 $ 5.00 $ 55.50 $ 185.00 $ 129.50 Total 240 $ 309.75 $ 1,081.00 $ 771.25 Quantity Sold Artic Frozen Pops Unit Sales January-June 2022 Item # Product Name January February March April May June Total POP01 Orange Pop 15 13 20 25 30 28 131 POP02 Cherry Pop 12 10 11 14 19 20 86 POP03 Chocolate Pop 20 15 35 41 50 44 205 POP04 Vanilla Pop 21 19 24 39 41 40 184 POP05 Lime Pop 11 9 13 15 20 19 87 POP06 Grape Pop 2 13 17 20 14 22 88 POP07 Cream Pop 14 12 16 25 33 37 137 Total 95 91 136 179 207 210 918 Sales Revenue Artic Frozen Pops Unit Sales January-June 2022 Item # Product Name January February March April May June Total % of Total Q1 Total Q2 Total POP01 Orange Pop $ 60.00 $ 52.00 $ 80.00 $ 100.00 $ 120.00 $ 112.00 $ 524.00 12.68% $ 192.00 $ 332.00 POP02 Cherry Pop 60.00 50.00 55.00 70.00 95.00 100.00 430.00 10.40% 165.00 265.00 POP03 Chocolate Pop 90.00 67.50 157.50 184.50 225.00 198.00 922.50 22.32% 315.00 607.50 POP04 Vanilla Pop 94.50 85.50 108.00 175.50 184.50 180.00 828.00 20.03% 288.00 540.00 POP05 Lime Pop 44.00 36.00 52.00 60.00 80.00 76.00 348.00 8.42% 132.00 216.00 POP06 Grape Pop 9.00 58.50 76.50 90.00 63.00 99.00 396.00 9.58% 144.00 252.00 POP07 Cream Pop 70.00 60.00 80.00 125.00 165.00 185.00 685.00 16.57% 210.00 475.00 Total $ 427.50 $ 409.50 $ 609.00 $ 805.00 $ 932.50 $ 950.00 $ 4,133.50 100.00% $ 1,446.00 $ 2,687.50 Sale by Product January - June 2022 POP01 Orange Pop January February March April May June 60 52 80 100 120 112 POP02 Cherry Pop January February March April May June 60 50 55 70 95 100 POP03 Chocolate Pop January February March April May June 90 67.5 157.5 184.5 225 198 POP04 Vanilla Pop January February March April May June 94.5 85.5 108 175.5 184.5 180 POP05 Lime Pop January February March April May June 44 36 52 60 80 76 POP06 Gra pe Pop January February March April May June 9 58.5 76.5 90 63 99 POP07 Cream Pop January February March April May June 70 60 80 125 165 185 Total January February March April May June 427.5 409.5 609 805 932.5 950 % By Sale Chart Percent of Sales by Items January-June 2022 Percent of Sales by Items POP01 POP02 POP03 POP04 POP05 POP06 POP07 0.1267690818918592 0.10402806338454094 0.22317648481916053 0.20031450344744164 8.4190153622837785E-2 9.5802588605298167E-2 0.16571912422886173 April vs Total April Sales vs Total April POP01 POP02 POP03 POP04 POP05 POP06 POP07 100 70 184.5 175.5 60 90 125 Total POP01 POP02 POP03 POP04 POP05 POP06 POP07 524 430 922.5 828 348 396 685 3428 3429 3430 3431 3432 3433 3434 Activity Activity # Activity Description Checking in shipments Stocking Inventory Pricing items Setting up displays Checking customers out/cashier Taking inventory Reording stock Performance Analysis Name Duration per Team Member Date: 3/23/2021 Team Member 1: James Harrison 16:20 Team Member 2: Melanie Wentworth 16:35 24 hour (Military) time format Activity # Activity Name/Description Team Member Activity - Start Activity - End Activity - Duration On Time? 3428 Checking in shipments James Harrison 10:15 AM 1:25 PM 3:10 Yes 3431 Setting up displays Melanie Wentworth 9:00 AM 11:30 AM 2:30 Yes 3433 Taking inventory Melanie Wentworth 10:30 AM 12:00 PM 1:30 Yes 3432 Checking customers out/cashier James Harrison 8:00 AM 11:15 AM 3:15 Yes 3429 Stocking Inventory Melanie Wentworth 11:15 AM 4:30 PM 5:15 No 3432 Checking customers out/cashier Melanie Wentworth 1:25 PM 3:30 PM 2:05 Yes 3430 Pricing items James Harrison 9:00 AM 3:00 PM 6:00 No 3434 Reording stock James Harrison 10:30 AM 12:00 PM 1:30 Yes 3431 Setting up displays James Harrison 10:00 AM 12:25 PM 2:25 Yes 3433 Taking inventory Melanie Wentworth 3:00 PM 8:15 PM 5:15 No Activity Statistics Allowed Actual Total Duration All Activities 12:00 32:55:00 Average Activity Duration 2:00 3:17 Minimum Activity Duration 0:30 1:30 Maximum Activity Duration 3:30 6:00 Total Number of Activities N/A 10 Activity Data Activity Analysis Activity Data Activity Activity # Activity Description Activity Analysis Performance Analysis Name Duration per Team Member Date: Team Member 1: Team Member 2: 24 hour (Military) time format Activity # Activity Name/Description Team Member Activity - Start Activity - End Activity - Duration On Time? Activity Statistics Allowed Actual Total Duration All Activities 12:00 Average Activity Duration 2:00 Minimum Activity Duration 0:30 Maximum Activity Duration 3:30 Total Number of Activities N/A Assignment: Semester Capstone Part 3- Formulas & Functions Assignment Due Date and Time · This assignment is due Sunday of Week 4 at 11:59 p.m. ET. Purpose Part 3 of your semester capstone project will allow you to apply the skills you have learned in Excel to create and apply functions and formulas. You will build upon the work you completed through this week. Make sure you include any feedback you received from your instructor in Parts 1 and 2 of the capstone project. Description Using the same workbook you created for Parts 1 and 2 of your semester capstone project create an activity data worksheet and apply the following skills: data validation, VLOOKUp. You will also need to determine the appropriate functions to use in order to complete an activity analysis. There are a total of 5 steps to this part of your capstone project, make sure that you complete each of the 5 steps for full credit. Choose a measurable activity that would be performed in relation to the business you chose for Part 1 of the capstone project. Examples could be: · housekeeping in a hotel · setting up for an event · breaking down from an event · delivery · stocking shelves · cooking or making of products The activity must require two employees, making up a team. Step 1: Activity Data Worksheet 1. Using the Capstone Part 3 Starter File copy the Activity Data and Activity Analysis worksheets into your own Capstone Project workbook. 2. On the Activity Data sheet, list 7 to 10 activities that would occur in the business used in Part 1 of the Capstone Project. Be sure to assign unique activity identification numbers in the first column, and a description of the activity in the second column. All remaining work will be completed on the Activity Analysis Worksheet Step 2: Data Validation 1. Enter the two team member names, one in cell B4 and the other in cell B5. 2. In Column A, create a Data Validation list to enter the Activity numbers listed on the Activity Data sheet. 3. Using the drop-down list, assign at least 10 Activity numbers to the cells in column A representing the work completed by the two team members. Do Not select the numbers in order! 4. In Column C, create a data validation list to enter the team member names listed in B4 and B5. Use the drop-down list to populate column C with team member names assigning them to the task in column B. Step 3: VLOOKUP In column B, use a VLOOKUP function to bring the activity name/description from the Activity Data worksheet for the Activity number in column A. Step 4: Activity Analysis Functions 1. Using a date function, enter the date into cell G3. 2. Enter activity start times (ex. 10:15 am) in column D and end times (ex. 1:25 pm) in column E. Be sure the end time is later than the start time! 3. Use a formula to calculate the activity duration in column F. (How long did the activity take to complete?) 4. In cells C4 and C5, use the appropriate function to calculate the total duration in column F for each team member based on the data in column C. Format as Time if necessary. 5. Using functions in cells D24:D27, calculate the statistics based on the actual activity’s duration. 6. Using a function in cell D28, enter the number of activities completed. Step 5: If Function In column G, using a function, determine if the activity was completed on time. If the actual completion time is less than or equal to the average duration, enter YES, otherwise enter NO (if longer than the average duration). Use conditional formatting to highlight the activities that were not completed on time. Submission Instructions Check to ensure both sheets are formatted professionally including design theme, number formats, alignment, cell styles, borders, shading, etc. Ensure all sheets are prepared for printing in a professional manner (orientation, proper page breaks, etc.) Remember to name the Excel workbook to match the name of your business, and the part of the capstone project being turned in. For example, if the business chosen for the project is Jewelry Sales, and the company name is “LA Jewelry”, the workbook name should be LAJewelry_Part3.xlsx. Upload your completed Excel workbook file to ulearn. Grading Criteria This assignment will be graded by rubric. You can find the rubric by locating the assignment within the “My Grades” section of the course and selecting “View Rubric.” Johnson & Wales University FIT1040