Write up a brief conclusion on your scenario analysis result in the sheet of Scenario Analysis, below the summary report. Original Answers Only

Databases

MIS 303 Spring 2023

Excel Assignment – Instructions

In this assignment, you are to follow the instructions to complete and submit the assignment as individuals. Please make sure you follow the instructions closely and complete all tasks on multiple worksheets.

  1. Download the file Excel Assignment.xlsx from to your computer.
  2. Rename the file as YourFirstNameYourLastName.xlsx. You should fill your own first and last names to replace the parts of YourFirstName and YourLastName in the file name. E.g., John-Smith.xlsx
  3. Open the file using Excel 2016. Rename the worksheets as suggested below. In the following instructions, each worksheet will be referred with the new name only.
Sheet1 à 2016 Orders
Sheet2 à Income Statements
Sheet3 à Pivot Tables
Sheet4 à Business Charts
Sheet5 à Goal Seek
Sheet6 à Solver Analysis
  1. On the sheet of 2016 Orders, complete the following tasks.
    1. Make of list on the entire dataset.
    2. Sort the dataset by Product first and then by Region. (Other idea: By Salesperson first and then by Month)
    3. Add a column at the right of Product. Name it Price. Use vLookup function to pull product prices from the sheet Income Statements (D4:D7) and show prices for all orders.
    4. Add a column at the right of Unit. Name it Total. The order totals = Price * Unit.
    5. At the right of Total, add a column called Discount. Use a nested If function to decide the discounts. The company gives 10% discount to all orders from the West region, and the other regions only enjoy 10% discount in December.
    6. At the right of Discount, add a column called Totalw/Dis. It is the order totals – discounts.
    7. In cell L2, use the AVERAGE function to calculate the average number of units sold from the column Unit. Next, use conditional formatting tool and highlight the orders with unit sold greater than average unit sold. Select the green fill and dark green text color option if the cells meet this rule.
  2. On the sheet of Income Statements, complete all the green-colored cells using Excel functions or formulae.
  • Revenueis Units Sold * Unit Price and COGS is Units Sold * Unit Cost.
  • Total Revenueis the sum of the revenues for all products.
  • Total COGSis the sum of the COGS for all products.
  • Gross Profit= Total RevenueTotal COGS
  • Salariesis 12% of Total Revenue
  • Advertisingis 4% of Total Revenue
  • Miscellaneous expenses are 1% of Total Revenue
  • Total Operating Expense= Salaries + Advertising + Miscellaneous
  • Earning Before Taxes= Gross ProfitTotal Operating Expense
  • Calculate Taxesbased on tax rate of 25% of Earning Before Taxes
  • Net Profit= Earnings Before TaxesTaxes
  1. On the sheet of Pivot Tables, create a pivot table. You should start with the sheet of 2016 Orders as it contains all your raw data. But when you choose the location to place your pivot table, make sure you choose an existing sheet – the sheet of Pivot Tables, and click on the cell A1 to place the pivot table separately. Here are some suggested ideas for pivot tables.
  • Product sales by Salespersons
  • Seasonal sales by Regions
See also  In which situation is Sal better off? In terms of consumers’ surplus which situation do people in LA prefer and which do people in NY prefer? Why?

Requirements

– Add meaningful title for your pivot table

– Use proper grouping fields and summary fields.

– Use proper structure of the pivot table to ensure readability

– Use proper type and format for summarized values

– Add a short paragraph below or on the side of the pivot table and describe 3 major findings from this table.

  1. On the sheet of Business Charts, create two charts using the summary data provided. Suggested ideas are:

– Salesperson performance

– Monthly sales

– Regional sales, etc.

Requirements

– Use proper chart type based on your data and purposes.

– Add meaningful chart title

– Use proper labels and/or legend to ensure readability

– Add a short paragraph below the charts and describe your major findings from each chart.

 

  1. On the sheet of Goal Seek, find the way to achieve your 2017 profit goals.
    1. Go back to Income Statement It must be completed by now. Copy the gray area (A3:G23). Go to the sheet of Goal Seek, and paste it at the cell A3.
    2. At the blank Rows 1 & 2, merge some cells and add a title 2017 Sales Estimates.
    3. The cell J3 should show a label Price Increasing Rate:, and J4 should show 5%. If they are not there because of your copying and pasting, please add them to your sheet at proper locations.
    4. Edit your data sheet and include 5% increase on Unit Price of all products. Make sure you use formula with reference to J4, where it shows the increasing rate of prices.
    5. Use the Goal Seek tool and see if we want to achieve a $125,000 net profit, what is the increasing rate of price we should apply in 2017?
  2. Scenario Analysis: Go back to the sheet of Income Statement. Use the scenario manager to create three scenarios stated below and create a scenario summary, which will be a separate worksheet.
    1. You will create 3 different scenarios by changing the product pricing mix in order to determine their impacts to Net Profit.
  • The First Scenario is to raise the price of Product B by $5.00. However, this would cause sales of Product B to fall by 800 units and sales of Product C to increase by 700 units. Title the scenario name as Product B Price Change
  • The Second Scenario is to raise the price of Product C by $4.00. However, this would cause sales of Product C to fall by 550 units and sales of Product B to increase by 400 units. Title the scenario name as Product C Price Change
  • The Third Scenario is to raise the price of both Product B and Product C by $6.00. This would cause sales for Products B and C to both decrease by 350 units each. Title the scenario name as Product B and C Price Changes
    1. Create a Scenario summary report, which will become a new and separate worksheet. Make sure the Results Cells include Earnings Before Taxes and Net Profit. Rename this worksheet as Scenario Analysis, and move the sheet to the right of the sheet of Goal Seek.
    2. Write up a brief conclusion on your scenario analysis result in the sheet of Scenario Analysis, below the summary report. Which scenario will bring the company the optimum outcome of 2017 sales?
See also  Describe at least one strategy you plan to use with your partner/client this semester to improve their "grit" when it comes to exercise? Original Answers Only

 

  1. Solver:
    1. Go back to Income Statement It must be completed by now. Copy the gray area (A3:G23). Go to the sheet of Solver Analysis, and paste it at the cell A3.
    2. At the blank Rows 1 & 2, merge some cells and add a title 2017 Income Statement Projections
    3. Change the label Unit Sold (at the cell C3) to Units to Sell.
    4. Within the Solver Analysis worksheet, input the following Solver Parameters:
  • Objective cell: Net Profit (C22) to the value of $80,000.
  • Constraint 1: Product A must sell at least 6000 units
  • Constraint 2: Total Operating Expenses is less than or equal $215,000.
  • Constraint 3: All unit sold quantities (C4:C7) are integers.
    1. Write up a brief conclusion on the major findings from the solver analysis.

 

Custompaper.help is an academic writing platform that provides students and professionals with quality services to ease their assignment challenges. With a wide range of services, the platform has become one of the leading academic writing service providers in the market due to its reliability and affordability. Below, we shall discuss some of the services which Custompaper.help provides:

Essay Writing Service
The essay writing service provides customers with quality essays on any subject or topic they requested within a given deadline. The writers at custompaper are experts in their respective fields, and they ensure customer satisfaction by producing original content with thorough research involved backed up with strong evidence from reliable sources available online or offline. All essays come with free plagiarism reports as well as revisions if needed so that customers can trust that they are getting high-quality work for their money’s worth.

Research Paper Writing Service
This is another popular service offered by custom paper help wherein customers are assisted in conducting research papers according to their needs and requirements within specified deadlines. Writers at custom paper help have vast knowledge about various topics belonging to different disciplines such as Management, Arts & Humanities, History etc., allowing them to craft impeccable research papers every time regardless of the complexity or urgency level requested by customers. Additionally, all our research papers come along with proper citation formats which ensures customer’s safety when it comes to plagiarism issues while also helping them save time on formatting procedures thus improving overall efficiency even further!

See also  THE TAMING OF THE SHREW-According to the links, how was Shrew was performed in the 1600's?-How did the audience of that time react?

Dissertation Writing Service
CustomPaperHelp also offers dissertation writing assistance for those who need it most – students preparing for graduation or professionals wanting to further refine their skills through higher education levels such as Master/PhD degrees etc, Our team of writers consists of experienced individuals having Master’s/PhD degrees themselves so you can rest assured that your dissertation will be handled expertly from the start till the end! Furthermore, we offer unlimited revisions until your complete satisfaction is achieved, along with regular checkups being done throughout the process just so everything goes according to course without any hiccups!

Editing And Proofreading Services
If you already have written an essay but want professional feedback before submitting it, then CustomPaperHelp also offers editing & proofreading services which helps in identifying & rectifying errors present in assignments like grammar mistakes, spelling mistakes, punctuation problems etc., thereby ensuring top-notch quality work, before submission takes place! We understand how important accuracy & consistency are when it comes to grading systems used at universities nowadays, so taking extra care during this phase becomes absolutely essential if good grades are desired afterward!

Coursework Help
For those who find coursework difficult due to complications of being quite technical in nature; CustomPaperHelp extends its support here too, by providing quality solutions every single time – starting from understanding concepts properly up until composing flawless answers following university guidelines closely enough making sure no loopholes remain whatsoever! Whether it’s related math problem-solving algorithms or lengthy home assignments – our expert panel has got you covered from head-to-toe leaving no stone unturned during this very crucial stage since final grades depend largely upon the success rate achieved here successfully!

Assignment Help
Assignments form an integral part of getting better scores & ultimately, a degree itself (in case pursuing a masters/PhD) – something only possible if precision is made a priority right away; otherwise, chances of failure increase exponentially over a time period detrimentally affecting performance evaluation systems later down road drastically hence stressing timely importance completion prior commencement other operations associated same project altogether consistently meeting high standards expected each situation respectively! Here too, CustomPaperHelp presents comprehensive solution package exceeding expectations beyond point offering optimal values therein ensuring maximum satisfaction reached under circumstances prevailing accordingly without fail…proving once more why people trust us implicitly without second thought whatsoever!!

Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose Us

Quality Papers

We value our clients. For this reason, we ensure that each paper is written carefully as per the instructions provided by the client. Our editing team also checks all the papers to ensure that they have been completed as per the expectations.

Professional Academic Writers

Over the years, our Acme Homework has managed to secure the most qualified, reliable and experienced team of writers. The company has also ensured continued training and development of the team members to ensure that it keep up with the rising Academic Trends.

Affordable Prices

Our prices are fairly priced in such a way that ensures affordability. Additionally, you can get a free price quotation by clicking on the "Place Order" button.

On-Time delivery

We pay strict attention on deadlines. For this reason, we ensure that all papers are submitted earlier, even before the deadline indicated by the customer. For this reason, the client can go through the work and review everything.

100% Originality

At Custom Paper Help, all papers are plagiarism-free as they are written from scratch. We have taken strict measures to ensure that there is no similarity on all papers and that citations are included as per the standards set.

Customer Support 24/7

Our support team is readily available to provide any guidance/help on our platform at any time of the day/night. Feel free to contact us via the Chat window or support email: support@acmehomework.com.

Try it now!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Custom Paper Help has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.

Essays

Essay Writing Services

At Custom Paper Help, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.

Admissions

Admission and Business Papers

Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.

Editing

Editing and Proofreading

Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.

Coursework

Technical papers

We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.