Tutorial of New Era Medical Excel Project in excel on Mac

Learning Excel
18 Mar 202317:11
EducationalLearning
32 Likes 10 Comments

TLDRIn this Excel tutorial, we focus on a project for New Era Medical, led by Josh Bartel, the senior director of the Pennsylvania field office. The tutorial covers how to manage and analyze revenue and expenses data to meet targeted profit goals for the fourth quarter. Key steps include moving worksheet titles, adding text to cells, entering quarterly data, using formulas to calculate needed revenue, gross margins, and operating margins, and creating a 2D pie chart to visualize year-to-date sales by region. The tutorial also highlights formatting techniques for a cleaner presentation.

Takeaways
  • 📊 The video is a tutorial focused on Excel tasks for a project called 'New Era Medical'.
  • 👤 Josh Bartel, the senior director of New Era Medical's Pennsylvania field office, needs assistance with a worksheet summarizing revenue and expenses.
  • 📈 The main goal is to determine actions needed in the fourth quarter to meet targeted profit goals.
  • 🔑 The first task is to cut and paste the worksheet title into a specific cell to display it correctly.
  • 📝 Instructions are given to add text in cells E2 and D4 to clarify the values in the worksheet.
  • 🧮 A formula is demonstrated to calculate the revenue needed in quarter four to reach annual targets.
  • 📊 The tutorial covers how to calculate the growth margin for the first three quarters and the year to date.
  • 📈 Summation of sales data for different regions is shown using the SUM function in Excel.
  • ✂️ The process of copying formulas to calculate variances for different regions and totals is explained.
  • 🔢 The video explains how to find the average revenue per salesperson by dividing total regional sales by the number of salespeople.
  • 📊 A pie chart is created to visualize total year-to-date sales separated by regions.
  • 🎨 The tutorial concludes with styling the chart, resizing it, and hiding gridlines for better readability.
Q & A

  • What is the name of the company in the project, and what do they manufacture?

    -The company is called New Era Medical, and they manufacture medical equipment.

  • Who is Josh Bartel, and what is his role at New Era Medical?

    -Josh Bartel is the senior director of the Pennsylvania field office for New Era Medical.

  • What task has Josh Bartel assigned to you in this project?

    -Josh Bartel has created a worksheet summarizing the revenue and expenses for the first three quarters of the year and has asked for help in determining what the Pennsylvania office needs to do to meet its targeted profit goals in the fourth quarter.

  • What action should be taken in the budget summary worksheet regarding the title?

    -The worksheet title should be cut from cell I1 and pasted in cell A1 to display the title in the expected location.

  • How should the text in cell E2 be edited?

    -The text 'number of' should be added in front of 'sales people' so that the complete text appears as 'number of sales people' and more clearly identifies the value in cell G2.

  • What formulas need to be entered in cells G5 and B7?

    -In cell G5, enter a formula without a function that subtracts the target revenue in cell F5 from the year-to-date revenue in cell E5. In cell B7, enter a formula without a function that divides the gross profit for quarter one in cell B6 by the revenue in quarter one in cell B5.

  • How should the gross margin be calculated for quarters 2 and 3 and for the year to date?

    -Fill in the range C7 to E7 with the formula in cell B7 to find the gross margin for quarters 2 and 3 and for the year to date.

  • What formula should be used in cell E10 to calculate year-to-date sales for the Northeast region?

    -Enter a formula that uses the SUM function to total the regional sales data for the Northeast region in the range B10 to D10.

  • How should the year-to-date sales be calculated for the other three regions?

    -Use the fill handle to fill in the range E11 to E13 with the formula in cell E10 to find the year-to-date sales for the other three regions.

  • What is the process for calculating the amount of revenue each region needs to generate in quarter 4?

    -In cell G10, enter a formula without using a function that subtracts the target sales in cell F10 from the year-to-date sales in cell E10. Copy the formula from cell G10 and paste it in the range G11 to G14, using the 'Paste Special' function and selecting 'Formulas'.

  • How should the average revenue per salesperson be calculated?

    -In cell B15, enter a formula without a function that divides the total regional sales in quarter one in cell B14 by the number of salespeople in cell G2. Fill in the range C15 to E15 with the formula in cell B15 to determine the average revenue per salesperson in quarters 2, 3, and for the year to date.

  • What formula is used to calculate the operating margin in cell B20?

    -Enter a formula without using a function that divides the operating profit or loss in quarter one in cell B19 by the revenue in quarter one in cell B5.

  • How should the operating margin be calculated for the other quarters and for the year to date?

    -Use the fill handle to fill in the range C20 to E20 with the formula in cell B20 to find the operating margins for quarters 2, 3, and for the year to date.

  • How can a 2D pie chart be created to visualize total year-to-date sales by region?

    -Select the non-adjacent ranges A9 to A13 and E9 to E13, then go to 'Insert' and select '2D Pie' chart. Set the chart title to 'Total Year-to-Date Sales by Region'.

  • How should the pie chart be resized and repositioned?

    -Resize and reposition the chart so that its upper left corner is in cell I4 and its bottom right corner is in cell L18.

  • What style should be applied to the pie chart?

    -Apply style 3 to the chart.

  • How can the grid lines be hidden in the budget summary worksheet?

    -Go to 'Page Layout', find the 'Gridlines' section, and uncheck 'View' to hide the grid lines.

Outlines
00:00
📊 Excel Project: New Era Medical Worksheet Analysis

The video begins an Excel tutorial focusing on a project for New Era Medical, a global medical equipment manufacturer. The task involves assisting Josh Bartel, the senior director of the Pennsylvania field office, in analyzing the company's revenue and expenses for the first three quarters of the year. The objective is to determine the actions needed to meet targeted profit goals in the fourth quarter. The tutorial instructs viewers to manipulate worksheet titles, cut and paste data, and enter text to clarify values. It also guides through entering formulas to calculate revenue requirements and growth margins for different quarters.

05:00
📈 Calculating Sales and Revenue Goals

This paragraph continues the Excel project by instructing viewers on how to calculate sales figures and set revenue goals for the Pennsylvania office. It details the process of using Excel functions like SUM to total regional sales data and to determine the year-to-date sales for different regions. The tutorial also covers how to input target sales amounts and calculate the revenue variance needed to meet annual targets. Additionally, it guides on copying formulas to find the variance for other regions and the total.

10:02
👥 Average Revenue per Salesperson and Operating Margin

The tutorial moves on to calculating the average revenue generated per salesperson and the operating margin for the company. It explains how to divide the total regional sales by the number of salespeople to find the average revenue per person. The video also demonstrates calculating the operating margin by dividing the operating profit or loss by the revenue. The process includes entering formulas without using functions and filling in ranges to apply these calculations across different quarters and for the year to date.

15:03
📊 Visualization and Worksheet Aesthetics

The final paragraph of the script focuses on visualizing data and enhancing the worksheet's readability. It guides viewers on creating a 2D pie chart to represent the total year-to-date sales by region and setting an appropriate chart title. The tutorial also covers resizing and positioning the chart within specific cells and applying a style to improve its appearance. Lastly, it explains how to hide grid lines to make the budget summary worksheet easier to read, concluding the Excel project tutorial.

Mindmap
Keywords
💡Excel
Excel is a widely used spreadsheet program developed by Microsoft for Windows, macOS, Android, and iOS. It allows users to organize, format, and calculate data with formulas using a grid of cells. In the context of the video, Excel is the primary tool for creating and analyzing financial data for New Era Medical, specifically for summarizing revenue and expenses and calculating profit goals.
💡Worksheet
A worksheet in Excel is a single page within a workbook where data can be entered and manipulated. It is a fundamental component of Excel, allowing for the organization of data into rows and columns. In the video, the worksheet is used to summarize the financial data for the Pennsylvania office of New Era Medical.
💡Revenue
Revenue refers to the income generated from the sale of goods or services. It is a key financial metric for any business and is essential for determining profitability. In the video, the revenue is a central focus as the Pennsylvania office aims to meet its targeted profit goals by analyzing revenue figures for the first three quarters of the year.
💡Expenses
Expenses are the costs incurred by a business in the course of its operations. They are subtracted from revenue to determine the net income or profit. In the video script, expenses are summarized alongside revenue in the worksheet to help calculate the Pennsylvania office's financial performance.
💡Profit Goals
Profit goals are targets set by a company to achieve a certain level of profitability within a specific timeframe. They are crucial for financial planning and assessing business performance. In the video, determining the profit goals for the fourth quarter is a primary objective to ensure the Pennsylvania office meets its annual targets.
💡Formula
In Excel, a formula is a sequence of characters that performs a calculation or lookup, typically starting with an equal sign (=). Formulas are used to perform operations on cells and can include functions, references to other cells, and constants. The video script provides several instances where formulas are used to calculate revenue, expenses, and profit margins.
💡Gross Margin
Gross margin is the difference between revenue and cost of goods sold (COGS) expressed as a percentage of revenue. It indicates how much profit a company makes after deducting the variable costs of producing goods or services. In the video, the gross margin is calculated for different quarters to assess the profitability of the Pennsylvania office.
💡Sales People
Sales people are individuals responsible for selling a company's products or services. They play a critical role in generating revenue. In the context of the video, the number of sales people is mentioned in relation to calculating the average revenue generated per sales person.
💡Pie Chart
A pie chart is a type of graph that is used to display data in a circular graph divided into sectors, illustrating numerical proportion. In the video, a 2D pie chart is created to visualize the total year-to-date sales separated by regions, providing a clear and concise representation of the data.
💡Grid Lines
Grid lines in Excel are the lines that divide the cells into rows and columns, making it easier to read and navigate the spreadsheet. In the video, the grid lines for the budget summary worksheet are hidden to improve readability and focus on the data presented.
Highlights

Introduction to the New Era Medical project and the task of helping the Pennsylvania office meet its profit goals.

Instructions on cutting and pasting the worksheet title to cell A1 for proper display.

Adding descriptive text in cell E2 to clarify the value in cell G2.

Entering 'Quarter Two' and 'Quarter Three' in cells C4 and D4 respectively.

Creating a formula in cell G5 to calculate the revenue needed in Q4 to meet annual targets.

Calculating the growth margin for Q1 to Q3 and the year to date in cells B7 to E7.

Summing the sales for the Northeast region using the SUM function in cell E10.

Filling the range E11 to E13 with the formula from E10 for other regions' year-to-date sales.

Setting the targeted annual sales amount for the Northeast region in cell F10.

Determining the revenue each region needs to generate in Q4 to meet their targets.

Copying the formula from G10 to G11 to G14 to find the variance for other regions.

Calculating the average revenue per salesperson for Q1 to the year to date.

Calculating the operating margin for Q1 to Q3 and the year to date in cells B20 to E20.

Inserting a 2D pie chart to visualize total year-to-date sales by region.

Customizing the chart title and resizing the chart to fit within specific cells.

Applying style three to the chart for a polished appearance.

Hiding grid lines for a cleaner look on the budget summary worksheet.

Conclusion and call to action for viewers to subscribe.

Transcripts

Browse More Related Video

Tutorial on Rivera Engineering excel project on Mac in excel

Track Your Expenses in Mac Numbers

Excel Module 1 Project 2: Retail Pro

5 Advanced Excel Formulas You Probably Didn't Know!

Excel for Beginners - The Complete Course

Excel for Intermediate Users - The Complete Course

Rate This

5.0 / 5 (0 votes)

Thanks for rating:

Related Tags
Excel TutorialFinancial AnalysisRevenue TrackingExpense ManagementProfit GoalsWorksheet TipsData VisualizationMedical EquipmentTarget SettingQuarterly Review