Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

A and b office supply a small family owned

06/01/2021 Client: saad24vbs Deadline: 10 Days

Assignment: Excel (100 points)


Read the brief case below. Using the Excel spreadsheet provided, complete the questions.


Downtown Office Supply


Downtown Office supply is a small family owned store in a small suburb. In order to maintain and grow the business the owners want to do some analysis to see how their sales are doing. Management wants to see how the company did last year and make some decisions about what products to keep along with staffing for busy times of the year.


Instructions: In the excle spreadheet provided, complete the tasks below. Upon completion you will submit a single, completed, Excel workbook.


15 points each


1. Setup the spreadsheet with proper formatting and formulas so it can be presented to management.


0. Change the tab called “Quarterly” to “Q1 - Quarterly” and move the tab to the first position.


0. Add a column to calculate the total for each product. Use the SUM function.


0. Add rows to calculate the Total, Average and Median sales for each quarter. Use the SUM, AVERAGE, and MEDIAN functions.


0. Format the title at the top so it stands out. Add distinct formatting to the column and row headings. Format the data in the cells to show currency.


0. Format the total sales column to have a green background.


0. Create a bar chart showing for each product (a) the total sales for the year and (b) the proportion of the total contributed by each quarter’s sales. This should be shown in one. You should have 1 bar for each product. Add a title to your chart.


1. Management would like to decide whether they should discontinue any products. Any product which has had 50% less sales over the previous year should be discontinued. Are there any products that they should discontinued? List the product(s) and your recommendation in the worksheet. Which 3 products had the highest increase in sales?


1. Change the tab called 2017 vs 2018 to Q2 – 2017 vs. 2018 and move the tab to the second position.


1. Add a new column to calculate the percentage change from 2017 to 2018. Percentage change is computed using the following formula: (current year sales—last year sales) / (last year sales). Represent the numbers as percentages.


1. Calculate the total, average, and standard deviation for each year.


1. Format the title at the top so it stands out. Add distinct formatting to the column and row headings. Format the data in the cells to show currency.


1. Use conditional formatting to highlight the Percent Change cells that have a negative percent change in red.


1. Use conditional formatting to highlight the top 3 cells that had the highest increase in green.


1. Create a pie chart showing the contribution of each product to 2018 total sales. Make sure the percentages are displayed on the chart and the product names are displayed in the legend. Add a title to the chart.


1. Which product was the highest contributor in 2018? (write the answer directly into the worksheet).


1. Management would to predict sales for next year using an increase estimate that they can change.


2. Change the tab called Quantity Sold to Q3 – Quantity Sold and move the tab to the third position.


2. Compute the 2019 estimates for each group using the following formula: [2018 quantity sold + (2018 quantity sold * the increase estimate)]. The increase estimate is shown in cell B7.


2. Change the increase estimate to .10. The values should change automatically. When student create the formula they need to use an “absolute” reference: =B3+(B3*$B$7)


2. Format the title at the top so it stands out. Add distinct formatting to the column and row headings. Format the data in the cells to be whole numbers.


2. Create a chart showing the distribution of quantity sold in 2018 for each product category. Add a title to the chart.


1. Use the data in the sheet titled “Raw data” to calculate the profit for each product category and product sub category (ability to drill down).


3. Create a new worksheet titled – “Q4 – Profit by Category” and insert a new pivot table for the data in the sheet – “Raw Data”. Move the worksheet to the 4th position.


3. List the total profit for product categories and subcategories in this table


3. Format the numbers to reflect the “$” symbol before the figures and 2 decimal places.


3. Change the column headings to be meaningful.


3. Add a meaningful title to the worksheet at the top and center it across the pivot table.


3. Use conditional bars to highlight gains and losses.


3. Add another column to the pivot table display the profit or loss as a percent of the total.


3. Use conditional formatting to display the top 3 products that had the highest percentage.


10 points each


1. Management would like to know when their busiest month for sales is.


4. Create a new worksheet titled – “Q5 -Sales by Month” and insert a new pivot table for the data in the sheet – “Raw Data”. Move the worksheet after Q4 worksheet.


4. List only the months in the 1st column and the sales in the 2nd column. To do this click on one of the dates. Then do a right mouse click to display a menu. Click the Group option. Another menu will display. Click on Months and Quarters.


4. Sort the sales in Descending order.


4. Format the numbers to reflect the “$” symbol before the figures and 2 decimal places.


4. Change the column headings to be meaningful.


4. Add a meaningful title to the worksheet at the top and center it across the pivot table.


4. Use to conditional formatting to highlight the top month for sales in green, the bottom month for sales in red.




4. Next, add a filter at the top so that you can filter out product sub categories.


1. Management would like to know – what is the split in terms of profit, across the Product Categories, for each quarter. Use a Line Chart to show the numbers. Rank the Product categories in terms of profit.


5. Create a new worksheet titled – “Q6 – Qtr. Profit” and insert a new pivot table for the data in the sheet – “Raw data”. Move the worksheet after Q5 worksheet.


5. List all the Product Categories in the 1st column.


5. Split the Quarter across the categories [ Hint: Drag the Date field in the “Column Label” area] . To group the date by Quarter – Click on the date field. Next, right mouse click to display a menu. Click the Group option. Another menu will display. Click on Months and Quarters.


5. Add the profit by category and quarter. Format the data in currency format.


5. Add meaningful column labels.


5. Add a meaningful title to the worksheet at the top and center it across the pivot table.


5. Select the Pivot Table and Click on Options> Pivot Chart


5. Insert a Column Chart of your choice.


1. One useful function of spreadsheet software is the ability to change input values and see the impact on outcome variables. For example, you can change sales projections and see the impact on net income. Excel’s Scenario Manager makes performing these sorts of sensitivity analyses much easier. Goal-Seek Analysis Scenario Manager helps examine how changes in input values impact outcome variables. To perform this analysis, go to the Data ribbon and select What-If Analysis, then Goal-Seek.


6. Change the title of worksheet “Revenue Analysis” to “Q7 - Revenue Analysis” and move it after Q6 worksheet.


6. Answer the following questions directly into the worksheet:


1. What do sales need to be in order to reach 100,000 in revenue?


1. What does the profit margin need to be to reach 100,000 in revenue?


1. Prepare your analysis for Management. Create a Table of Contents.


7. Create a new worksheet and move it to the beginning. This worksheet should list the contents of the workbook along with the purpose of the workbook, the current date and the name of the person who created the workbook. Be sure to have titles for each of these.


7. Make each list item a hyperlink to that particular workbook. For example, in the table of contents you will have an item titled “Q1 - Quarterly Sales”. Create a hyperlink to link to that particular workbook.


7. Move the Raw Data to the last position.


7. User proper elements to add the file name and current date to the header.

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Top Essay Tutor
Helping Hand
University Coursework Help
Writer Writer Name Offer Chat
Top Essay Tutor

ONLINE

Top Essay Tutor

I have more than 12 years of experience in managing online classes, exams, and quizzes on different websites like; Connect, McGraw-Hill, and Blackboard. I always provide a guarantee to my clients for their grades.

$115 Chat With Writer
Helping Hand

ONLINE

Helping Hand

I am an Academic writer with 10 years of experience. As an Academic writer, my aim is to generate unique content without Plagiarism as per the client’s requirements.

$110 Chat With Writer
University Coursework Help

ONLINE

University Coursework Help

Hi dear, I am ready to do your homework in a reasonable price.

$112 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Matthew kelly dynamic catholic - Peter walsh doona cover - Casestudy - How to analyse a text - How to document head to toe assessment - The house on mango street hips - Allroad parts competitive strategy - Mary kay agreement pdf - Secrets of house music production book pdf - Alex wants to measure the nominal 1998 gdp - QUIZ 8 - Daphnia heart rate lab answers - What is the vice president's only constitutional responsibility - John henrik clarke a great and mighty walk - Thermal analysis of disc brake ppt - Credit card debt on balance sheet - Hunger games movie classification - 12 hours - 5vz fe wiring diagram - Fisher decoding the ethics code - Burnside rugby union club - Yitang zhang new hampshire - En las farmacias pequeñas de ecuador se venden ____. hierbas medicinales remedios caseros - Foreign nationals enrolment biometric residence permit - Summary Paper (goes with 15 page paper) - Little stevie and the tail fins - Pret a manger food on the move pdf - DQ w1 650 - Oedipus rex short version - An apology for raymond sebond - Bar mpa to psi - Force exerted by magnetic field equation - IT-project management QN5b - U.S. UNIIONS - Arb part 3 criteria - Csec english sba plan of investigation - What is donum vitae - Multi spindle drilling machine - How does the conflict between the US and China affect the volume of trade between the two countries - Spartan society to the battle of leuctra - Why is ge so successful - Worldwide, _____ percent of students report periodic binge eating or self-induced vomiting. - Public administration discussion 3 - 7th grade triangles worksheet - Total cost of merchandise purchased - Motorcycle accessories supermarket keilor east & mca indian motorcycle - Module 04: Annotated Bibliography - Discussion - How to calculate long term constant growth in fcf - Fiu librar - Index - Voluntary chain store example - Coca cola product portfolio analysis - Minoans and mycenaeans compare and contrast - What year did nehemiah rebuild the wall - American society for public administration code of ethics - Umass dartmouth health services - Authentic leadership self assessment questionnaire citation - Six sigma case study example cost reduction - Krell industries has a share price of - Where does the cyclops live - Jumbled flow process - Https www rug nl ggdc productivity pwt - OL discussion 2 - Gtech ht20 hedge trimmer - Business Research - Comrey & lee 1992 - Discussion.. - Roasted plantain with groundnut - Nectar in a sieve lesson plans - Psy 315 week 3 practice worksheet - Total sum of squares - Greasy lake tc boyle pdf - WEEK 7 FORUM PERCEPTION - Ethiomedia forum amharic news - Standardization of naoh with potassium hydrogen phthalate - Data table for cleaning coins - New assignment - Advantages and disadvantages of posttest only design - ME 3010 Project 1 Consider the planar linkage shown below - The story of an hour audience - Chapter 1 of the hobbit - The photograph by n scott momaday answer key - Ametek rotron regenerative blower - Landis+gyr e350 user manual - Some lessons from the assembly line andrew braaksma - Decision Criteria - Bio 220 environmental science gcu - Kevin tomorrow when the war began - Worksheet 5.4 market changes in demand answer key - What is the method of joints - North pole of inaccessibility - Principles of Macroeconomics - The ecuadorian rose industry case study answers - Need 600+ words with no plagiarism and 1+scholarly references in APA7 - Words with im prefix - Positive and Negative Impact of electronic devices in the classroom. - Open university malaysia phd - Nsw bar association v hamman - The great gatsby chapter 7 literary devices