Bright Academic Experts

Category: Excel

  • Excel Tasks

    Hi,

    You will open each assignment file (A1a, A1b, A1c, A1d). Each file has multiple worksheets. First, read the introduction sheet to understand the goal. Then, review the instructions or demonstration sheet, which shows examples. Finally, go to the last worksheet labeled Assignment and complete the required tasks using Excel.

    In A1a, calculate descriptive statistics (mean, median, standard deviation, etc.). In A1b, follow steps to calculate a sampling distribution. In A1c, calculate the necessary sample size. In A1d, apply probability sampling methods to select a sample. Complete each assignment worksheet accordingly.

    I have uploaded the excel files.

    Requirements: ..

  • Excel skills

    Excel 2021 Skills Approach – Ch 3 Challenge Yourself 3.3 Excel 2021 Skills Approach – Ch 5 Skill Review 5.2

  • Excel data analysis

    Steps to Perform: Step Instructions 1 Open the file named Excel Assignment #4. Save the file as Your last nameExcel Assignment #4. 2 Go to the Health worksheet and we want to create a frequency table to see the results of the Health Survey. Create a frequency distribution to determine how often respondents replied Poor Fair Good Excellent Use the method we reviewed in class copy the column of data to column D and go to Data/DataTools/Remove Duplicates 3 Add a sum to add up the total of Excellent, Fair, Poor, and Good, to confirm that the frequency numbers add to 150. 4 We want to create a Centering of the title Quality over the data columns, changing the title to Quality Survey Results, merging the title over cells D and E, increasing the font size, and applying a different font color will make it stand out as a title. Merge and center the title over the range D1:E1, change the font size to 20, and apply dark Blue Fill color and white font color. Make the title Bold. 5 We want to visually inspect the survey responses. Create a Clustered Column chart. Select the data in D2:E5 and insert a clustered column chart. 6 We want the clustered column chart to be more visually appealing. Add a title and make it Bold. Select the horizontal axis and make the labels Bold and size 14 font. Move the chart to a new worksheet called Charts/Graphs. 7 Open the Friends worksheet. We want to create a contingency table with Male or Female as the column headings and Feasible: Yes or No as the row heading. Insert a pivot chart and make Gender (Male or Female) as the columns and Feasible (Yes or No) as the row heading. We want to count how many students answered yes or no, so use Count of Students as the Value Field Setting. 8 Pivot tables are good, but we want a better visual! Create a stacked column chart using the data in the pivot table. Select the data in the body of the Pivot Table and insert Stacked Column Chart. 9 We want the chart to be more visually appealing. Add a Bold Centered Title of Friends or Not Just Friends. Using Chart Elements, Data Labels, add the number of respondents to the columns of the chart. Then move the Chart to the Charts/Graphs worksheet. 10 Open the Midterm Grades worksheet. We want to see if there is a relationship between Midterm and Final grades. Select the data and insert a scatter plot. Make the plot more appealing by changing the scale on both the horizontal and vertical axes to range from 25 100 for Midterm and 40-100 for Final Exam. Make the axes numbers Bold Add a title of Relationship of Midterm and Final and make it bold and size 20 font. Add a Gradient Fill to the chart (the background of the chart) (Use chart options, Format chart, Fill, Gradient) Move the chart to the Charts/Graphs worksheet. 11 Open the High School SAT worksheet. Construct a bubble plot that shows math score on the x-axis, writing score on the y-axis, and number of test takers as the size of the bubble. 12 We want to make the Bubble Chart more appealing. Change the horizontal and vertical axes to be scaled better minimum of 300 and maximum of 700. Add a title of Relationship of Size of School to SAT scores and make it BOLD and centered. Move the chart to the Charts/Graphs worksheet. 13 We want to see if there is a relationship between math and writing scores. Construct a scatterplot that shows math score on the x-axis and writing score on the y-axis. Use different colors or symbols to show whether the high school is a private or a public school. You should sort by Public or Private and then Insert Scatter Plot, then Select Data and call the series Public and select Math and Writing scores, then Private and select those Math and writing Scores 14 We want to make the Scatterplot more appealing. Change the range of numbers on the axes to 300-700 and make the numbers Bold. Add a title of Relationship of Math and Writing and make it Bold and centered. Move the chart to the Charts/Graphs worksheet. 15 Open the TShirts worksheet. Construct a contingency table that shows the total quantity sold for each color and size combination. Insert Pivot Table, move Size to Columns and Color to Rows and Quantity to Sum and change Value Field Settings to Count. 16 We still would like to make this more visually appealing! Create a heat map using the pivot table results. Select the data in the pivot table. Go to Home, Conditional Formatting, Color Scales, and choose one you like. Make the titles and labels more appealing and move the heat map to the Charts/Graphs worksheet. 17 Open the Country worksheet. We want to compare the average returns for Latin America and Canada. We want to also compare the riskiness of the funds for each country. Calculate the mean and standard deviation of the funds for each country and then compute the Sharpe Ratios. 21 Save and close file. Exit Excel. Submit the file as directed. 22 Have a great break this is your last Excel Assignment!

  • crj101 Strayer Week 4 Modern Day Policing, Society, AQnd The…

    I,am Aditto Rahman. I,am In 12th Grade. Welcome To My Business Site. Thanks For Visit

    Requirements:

  • Excel Project

    The purpose of this assignment is for you to understand how to export reports from an ERP system and then use Excel to create formula-based templates & pivot charts that allow accountants and managers to analyze the data more easily. After completing this assignment, you should feel comfortable exporting data from an ERP system and using Excel to analyze data further. This assignment will be graded on your ability to export the correct reports and create the templates and pivot charts. Please refer to the recorded for insights on the formulas and templates.

    Part 1

    1. Create a single excel file that includes an exported sales and production report from your ERPSim game. Each report should have their own tab and be labeled Sales Report and Production Report.
    2. Create a tab that will act as a template to analyze the sales report. The tab should include the following details and measures.
      1. Item #: (this can be hard coded for the products in the game) For example AA-F01.
      2. Product (material description): This should be formula-based (xlookup or vlookup).
      3. Total sales orders for each product: Should be formula-based.
      4. Total sales for each product: Should be formula-based.
      5. Total quantity sold for each product: Should be formula-based.
      6. Average sale per order: Should be formula-based.
      7. Average quantity sold per order: Should be formula-based.
      8. Weighted Average Selling Price (WASP): Should be formula-based.
      9. % of total sales by product: Should be formula-based.
    3. Create a tab that will act as a template to analyze sales further for a specific region (No, So, or We). This tab should include the following details and measures.
      1. Item #: (this can be hard coded for the products in the game) For example AA-F01.
      2. Product (material description): This should be formula-based (xlookup or vlookup).
      3. Total sales orders for each product: Should be formula-based.
      4. Total sales for each product: Should be formula-based.
      5. Total quantity sold for each product: Should be formula-based.
      6. Average sale per order: Should be formula-based.
      7. Average quantity sold per order: Should be formula-based.
      8. Weighted Average Selling Price (WASP): Should be formula-based.
      9. % of total sales by product: Should be formula-based.
    4. Create a tab that will act as a template to analyze the relationship between sales and production. This tab should include the following details and measures.
      1. Item #: (this can be hard coded for the products in the game) For example AA-F01.
      2. Product (material description): This should be formula-based (xlookup or vlookup).
      3. Total quantity sold for each product: Should be formula-based.
      4. Total quantity produced for each product: Should be formula-based.
      5. % of inventory sold: Should be formula-based.
      6. Remaining inventory on hand: Should be formula-based.

    Part 2

    Create two pivot charts using the exported sales report from your ERPSim game. 1) Create a pivot bar chart to visualize the top 3 sellers in the North Area, 2) Create a pivot column chart to visualize the bottom 3 sellers in DC12.

    Requirements: Answer the instructions

  • .

    Scenario

    You have been tasked to manage the inventory at your company. Your boss thinks that inventory management is out of control, but is not sure and wants you to perform some analysis of the inventory and provide him with some data. This chart lists your current stock, annual volumes, and unit cost.

    Use Excel OM and the chart to conduct an ABC analysis to determine which category each of the parts falls into. Provide analysis and interpretation of the results.

    In the Excel OM software, the default Percent of dollar volume for A items is 75%, however, it has been determined by your boss that he would like to see the data for the A items set at 80%.

    Use Excel OM to calculate the EOQ and the reorder point for part number 1359 (check reorder point and graph when setting up the software).

    The lead time to reorder parts is 30 days. The set up/ordering cost is $1,000 per order, and the holding cost is fixed at $125 per month (hint: be careful here). The factory runs 365 days per year, and the company wants to maintain a safety stock of 50.

    Provide analysis and interpretation of the results.

    In Step 2, you were given the safety stock of 50; however, this can be calculated for a more accurate number. Assume that the average daily demand for part number 1359 is 30 with a standard deviation of 2 parts per day. If the company wants to make sure that they do not stockout more than 5% of the time, then what level of safety stock should they hold?

    As part of your analysis of these results, how does the safety stock calculated here compare to the safety stock requirement in step 2?

    To calculate the safety stock, use Excel OM and the Safety Stock (Normal Distribution) spreadsheet. Input your data into column H of the model titled Model: Daily Demand and its standard deviation given.

    Be sure to create only one spreadsheet – each activity should be on a separate tab.

    Submit your spreadsheet. Save your assignment using a naming convention that includes your first and last name and the activity number (or description). Do not add punctuation or special characters.

    Do not forget to add a detailed analysis for each of your problems. Your analysis should be inserted on each spreadsheet.

  • Real Estate Data Analysis

    take the xlx file and complete it please!

    Attached Files (PDF/DOCX): Exp22_Excel_Ch05_ML1_RealEstate_Instructions.docx

    Note: Content extraction from these files is restricted, please review them manually.

  • HSA1100SPC

    Let’s learn math easily

    Requirements:

  • A04: Conditional Aggregares and Logical Functions (S1)

    In this assignment, you will apply Excel skills such as conditional aggregates, logical functions and referencing.

    Download file from the link below. Save it on your laptop and work on it.

    Instructions are given in the worksheet Instructions. For your convenience, the same instructions are pasted below.

    Context

    The Data worksheet contains sales data of electronic items (Laptop, Tablet, etc.) from a distributor. Different salespersons make bulk sales to retailers across multiple regions.
    Your task is to use Excel functions to create summary analyses using the specified cells and ranges in the Data worksheet.

    Task 1: Create Unique Lists (6 points)
    Create unique lists of Region, Salesperson, Product, and Month in columns O through R below the respective headers starting row 2.
    Do NOT type the values manually.
    Use Remove Duplicates to generate the lists (copy in an empty area to remove duplicates, then cut and paste in columns O-R).
    Sort each list alphabetically.

    Task 2: Total Sales by Region (4 points)
    Copy the unique Region values to column O starting at O15.
    Calculate total Sales in column P. Write the formula once in P15 and copy down. Use the Region value in column O as the criteria.
    Do NOT retype the formula for each row.

    2B: Region Performance Flag (3 points)
    A regional sales target is provided in cell Q12. In column Q, under Region Flag, create a performance flag for each region.
    If Region Sales target Strong, otherwise Weak. Write formula once and copy down.

    Task 3: Count of Orders by Month and Region (6 points)
    Copy unique Regions to column T starting at T4.
    Transpose the unique Months across row 3 starting at U3 (under <– Months –>)
    In cell U4 write a formula to count orders by:
    Region (column T)
    Month (row 3)

    Requirements:
    Copy the formula across columns and down rows. Do NOT retype the formula.
    Use appropriate referencing so the formula works everywhere.

    3B: Determine if there was a spike in monthly demand (3 points)
    Management wants to identify whether any month experienced a demand spike across regions.
    Compare each regions order count with the Monthly Demand Threshold provided in cell X2. If order count for any region in a given month is more than or equal to the threshold, show Spike in row 11 for that month, otherwise show blank (“”)
    Use IF with OR to check the regional order counts within each month. Write formula once and copy across.
    Do not hardcode threshold value.

    Task 4: Average Units Sold by Salesperson and Product (6 points)
    Place unique Salespersons in column T starting at T16.
    Transpose unique Products across row 15 starting at U15 (below <– Products –>)
    In cell U16, calculate the average Units for:
    Salesperson (column T)
    Product (row 15)
    Show results with 1 decimal point.
    Requirements:
    Use proper referencing so the formula copies correctly across rows and columns. Do NOT retype formulas.

    4B. Find Employee performance tier (4 points)
    Based on the average Laptop units sold in Task 4, determine each employees performance tier in column S (Gold, Silver, Bronze, or Poor).
    The tier thresholds are provided in rows 13 and 14.
    For example, if the average Laptop units sold is 105 or higher, the performance tier should be Gold.
    Use the IFS function to assign the appropriate tier. Reference the threshold cells do NOT hardcode values. Write the formula once and copy it down the column.

    Task 5: Monthly Sales by Salesperson (5 points)
    Place unique Months in column O starting at O28.
    Place unique Salespersons across row 27 starting at P27 (under <– Salespersons –>)
    In cell P28, calculate total Sales by:
    Salesperson (row 27)
    Month (column O)
    Requirements:
    Use correct referencing so formulas copy across and down without modification.
    Do NOT retype formulas.

    Task 6: Commission Calculation (5 points)
    The commission rate is provided in cell T25.
    Using the totals from Task 5:
    Calculate total Sales by each salesperson in row 35 (sum of monthly sales in rows above).
    Calculate the commission per salesperson using the commission rate for each salesperson.
    Do NOT hardcode (i.e. use fixed values for) the commission rate, use proper cell reference.

    Task 7: Format Sales as Currency (3 points)
    Format all Sales values (original and calculated) as Currency ($).
    Ensure Units and Counts remain in General format.

    Submission Requirements

    • Submit .xlsx file only – any other type of file will receive zero credit.
    • Make sure that the file you are submitting is the one you worked on. Check file modification date before uploading.
    • Ensure formulas are used manually typed values will not receive credit
    • File names must not begin with _ or ~
    • Write each formula once and copy whenever possible. Retyping formulas may result in point deductions.

    Requirements: excel

  • pivot table

    Requirements: 200