Excel Case Study: Myrons Department Store (MDS) The purpose of this assignment is for you to review a business situation and some management requests for data analysis, and then create an Excel spreadsheet that summarizes the information in an easy-to-read format for the Myrons Department Store management team. To learn the business situation and the requirements management has set for the creation of the Excel spreadsheet, read through the Business Situation and the Steps for Completion sections of this document. After reading through this document: 1. Open a blank Excel file. 2. Save the
View complete question Excel Case Study: Myrons Department Store (MDS) The purpose of this assignment is for you to review a business situation and some management requests for data analysis, and then create an Excel spreadsheet that summarizes the information in an easy-to-read format for the Myrons Department Store management team. To learn the business situation and the requirements management has set for the creation of the Excel spreadsheet, read through the Business Situation and the Steps for Completion sections of this document. After reading through this document: 1. Open a blank Excel file. 2. Save the file as Lastname_Firstname_MDS.xlsx. 3. Enter the needed data, formulas, and functions to create your worksheet based on the Business Situation and Steps for Completion information. 4. Submit your completed file into the Myrons Department Store and Excel Questions D2L Assignment dropbox by the assignment deadline. Business Situation Myrons Department Store is a well-known business in the Midwest. They are looking to expand their locations. Specific employees are being considered for key positions at the new locations. Management has asked you to prepare a worksheet, based on the employee payroll information listed below. Management will base some decisions regarding who to promote to the new locations based on these employee records. The company currently pays each employee a weekly base salary and a 5% commission on their sales for the week. You will use the following information when you create your Excel worksheet. Employee Base Salary Weekly Sales Samantha Core $600 $1200 Natalie Bunker $600 $1300 Kyle Leeway $700 $1250 Kelli Post $500 $1500 * businesses usually present employee rosters in alphabetical order by last name, then first name Management has asked for a number of additional items to be added into your worksheet. Read the next section to learn the specific design items to include in your worksheet and hints for some of the needed formulas in your worksheet. Myrons Department Store Excel Case Study Page 2 of 4 Steps for Completion Hint to help you get started Read through the steps below, and before starting on step 1, make a rough sketch of how you envision your worksheet will look when you have finished the assignment. (There are many correct layouts for this assignment.) Designate your column and row headings, but do not enter actual amounts here, just use XXXs in place of amounts. You will enter the values and formulas when you use Excel to create your worksheet. This approach gives you a plan as you create your worksheet. You may find that your final worksheet does not exactly match your original plan, and that is fine, as long as your worksheet provides the needed information to management. 1. Open a blank Excel file, name the file Lastname_Firstname_MDS.xlsx, and save the file to your computer. 2. Place a title (you determine what the title is) for the worksheet in row 1. Since you dont know how wide this spreadsheet will be; formatting will be completed after all work is entered. 3. Place a sub-title (that includes the phrase Employee Weekly Salary Information) in row 2. Format this row after inserting all the needed information and calculations. 4. Starting in row 3, place your column headings in row 3, and your row headings in column A for the employee data on page 1. a. Then enter the corresponding amounts for each employee. Double-check your entries to see that all information is accurate. b. Do not include blank rows or columns in the range of cells that holds your employee information for the worksheet. c. If it is helpful for you adjust your column widths, but do not format the numbers at this time. i. You first make sure your worksheet includes all the needed information and the numbers are accurate, then you format the information to make it easy-to-read. 5. To the right of your Weekly Sales column, insert a Commission Amount column and a Total Salary column. 6. On the four rows immediately below your last employee name, add the following row headings (one heading per row) Total, Average Value, Highest Value, and Lowest Value. 7. Once management has your completed file, they want to conduct What-If analysis regarding employee commissions. They want your worksheet set up so they can easily change the amount in the one cell that holds the commission Myrons Department Store Excel Case Study Page 3 of 4 amount and then have all the employee calculations automatically adjust to the new commission rate. To help make this happen: a. Enter Commission Rate in cell A18 and enter 5% in cell B18. 8. Enter an Excel formula to calculate the first employees Commission Amount by using the following rules: a. Commission Amount = Commission Rate times Weekly Sales. b. Do not enter 5% in your formula; instead enter the absolute cell reference for the cell in your worksheet containing the 5% rate in your formula. c. Do not enter the actual number (or amount) for an employees weekly sales; instead enter the cell reference for the employees weekly sales in the formula. i. Hint: the commission amount for Natalie Bunker is $ XXXXXXXXXXAfter calculating the first Commission Amount, use the fill handle to copy down the formula for the other employees. Make sure all Commission Amount formulas include the same absolute cell reference (but each employee will have a different weekly sales cell reference in their formula). 10.Enter an Excel formula to calculate the first employees Total Salary by using the formula: Total Salary = Base Salary plus Commission Amount. a. The formula may not contain any employee numeric amounts. The formula must consist of cell references. b. Hint: the total salary for Natalie Bunker is $ XXXXXXXXXXAfter calculating the first Total Salary amount, use the fill handle to copy the formula down for the other employees. 12.In the cells to the right of the cells displaying the labels Total, Average Value, Highest Value, and Lowest Value enter the appropriate Excel functions to display the Base Salary total, average, highest, and lowest amounts. a. Hint: When you enter the function to total the Base Salary entries, the worksheet should display $2,400 for the total amount. 13. Select the four values created in the step above, and then use the fill handle to display the corresponding amounts in the last three columns. 14.Double-check your final information for accuracy. For example, scan the items being calculation created by using Excel formulas and functions to see if the amounts seem accuratethis is called the test of reasonableness which asks, Does this amount seem correct based on the numbers given for the problem? 15. Spellcheck your worksheet and correct any keying errors. 16.After you are sure your worksheet accurately displays all needed information, then format the worksheet to make it easy to read. Use the formatting techniques that were used in your previous Excel exercises. Myrons Department Store Excel Case Study Page 4 of 4 a. Typical formatting includes merging some cells across all columns, using cell styles for the title, subtitle, column headings and row headings. If needed, wrapping text of column headings. Using the Accounting format for the dollar values in the top and bottom rows of the data block, using the Comma format for the dollar values between the top and bottom rows of the data. Making sure all numeric entries in a column display the same number of decimal places. Etc. b. Make sure all data on your worksheet fits on one Landscape orientation page. 17.Create a column chart that shows each employees weekly sales and total salaries. (Choose the selections for your chart carefully.) Make sure the chart legend lists the color of the Weekly Sales columns and the color of the Total Salary columns. Give your chart a title. a. Place the chart on a chart sheet tab. 18.Rename your sheet tabs appropriately. 19.Vertically and horizontally center the worksheet data on the page. 20.Add a footer that includes the file name. 21.Observe your file with the human eye. Does it look professional and presentable? Make any needed changes. 22. Save your file, and then submit your file into the Myrons Department Store and Excel Questions D2L Assignment dropbox by the assignment deadline. ======================================================================== Assignment Evaluation This assignment is worth 10 points. One point will be deducted for each missing or incorrect item in the worksheet or chart. ======================================================================== Optional (Non-Graded) Step Conducting What-If Analysis What-If analysis gives management a chance to ask, what if we changed something like the commission rate for the sales people. How would that one change effect the commission amount and the total salaries for the employees? Because you used cell references, instead of numbers in your Commission Amount and Total Salary columns, you can quickly show the results of changing the commission rate in cell B18 of your worksheet. For example, if you were to enter 8% into cell B18, you should see that Natalie Bunker earned $108 in commission and a total salary of $704. Try a few different commission rates and see how your Commission Amount and Total Salary cells automatically update once the new commission rate is entered into your work
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more