Could I please get help with the homerwork attached. The instructions are in the document tab 1 and the problems tab 2. Can I can an estimate on howmuch is going to be? Thanks
Unit 10 Assignment Complete M6, M7, M19, and M29, creating your own Excel template for each. Make sure you also complete the additional requirements for M29: Use the Vlookup function to populate the name of the department within your problem. You may wish to review the tutorials on Vlookup located in the additional resources area before you start this problem. These problems can be found in the back of your textbook. For these problems you will NOT print the document as I will print your documents myself when I am grading them. Make sure your document will print as required in the problem. To verify this you can click on print preview. Once you are satisfied with your documents save the file on your computer. You should only submit one file, in which each spreadsheet is labeled on a separate tab within the file. Submitting more than one file will result in a loss of 10% of the earned point value. NOTE: The check figure given in the problem for M19 is incorrect. Correct sales needed for desired net income is $87,500, not $87,000. MODEL-BUILDING PROBLEM CHECKLIST Before submitting any model-building solution to your instructor, review the following list to ensure that your worksheet is presented in a clear, concise manner. M6 INVENTORY VALUATION: LOWER OF COST OR MARKET Quality Reclining Chairs has inventory at June 30 as follows: Chair Extreme Deluxe Fancy Regular Inventory Quantity 75 165 43 120 Unit Cost $310 240 150 105 Unit Market $340 235 155 85 You have been asked to develop a worksheet that will determine the value of the inventory on an item-by-item basis at the lower of cost or market value (file name LCOM). You should find it helpful to use the =MIN function discussed in Appendix A of Excel Quick. Use the provided data as input for your worksheet. Review the Model-Building Problem Checklist on page 154 to ensure that your worksheet is complete. Print the worksheet when done. Check figure: Lower of cost or market inventory value, $78,675. To test your model, use the following information about Quality Reclining Chairs inventory at September 30: Chair Extreme Deluxe Fancy Regular Inventory Quantity 90 145 45 160 Unit Cost $310 240 150 105 Unit Market $315 245 145 100 Print the worksheet when done. Check figure: Lower of cost or market inventory value, $85,225. From the test data worksheet, design a 3-D column chart demonstrating the relationship between total cost and total market value for each commodity. Print the chart when done. M7 LUMP-SUM PURCHASE Peach Management owns several apartment complexes throughout the city of Atlanta. It just acquired a new piece of property that has been appraised at $775,000. The assets and their values are listed as follows Asset Building Furnishings Land Land improvements Total Value $475,000 50000 150000 100000 $775,000 Peach Management purchased the property in a foreclosure for $600,000. Create a worksheet (file name BASKET) that will allocate the purchase price among the assets acquired. Review the Model-Building Problem Checklist on page 154 to ensure that your worksheet is complete. Print the worksheet when done. Check figure: Allocated cost of the building, $367,742. Use the following purchase price to test your model: $700,000. Print the worksheet when done. Check figure: Allocated cost of the building at the purchase price of $700,000, $429,032. M19 COST-VOLUME-PROFIT ANALYSIS Spruce Enterprises anticipates fixed costs of $25,000. Variable costs and expenses are expected to be 60% of sales. The president has asked you to develop a worksheet to calculate sales needed to break even and sales needed to achieve any desired net income (file name DESNI). Your worksheet should include a Data Section that contains fixed costs, desired net income, and variable costs as a percentage of sales. Assume as initial input for your model that the company wishes to achieve a net income of $10,000. Review the Model-Building Problem Checklist on page 154 to ensure that your worksheet is complete. Print the worksheet when done. Check figure: Sales needed for desired net income, $87,000. To test your model, use the following projections with a different cost structure: fixed costs of $32,000 and variable costs and expenses to equal 45% of sales. The company wishes to have a net income of $12,000. Print the worksheet when done. Check figure: Sales needed for desired net income, $80,000. Using the test data worksheet, develop a standard break-even line chart that plots total revenue and total costs from a sales level of $0 to $90,000. Use $10,000 increments on the X-axis. M29 EMPLOYEE PIVOT TABLE The following data come from an employee data for Husker Industries. You have been asked to create a worksheet file of this information (file name EMPLOYEE). See Appendix C in Excel Quick for a discussion of pivot tables. Employee No, 1 2 3 4 5 6 7 8 9 10 Name Tammy Adriane Logan Star Jeremy Matthew Joshua Emily Shannon Cody Department Accounting Purchasing Marketing Payroll Accounting Purchasing Marketing Finance Accounting Marketing Gender F F M F M M M F F M Salary 40000 52000 80000 37000 92000 44000 75000 41000 68000 30000 Bonus $4,000 2500 3200 1000 $4,400 1000 $1,000 1000 5200 1000 Review the Model-Building Problem Checklist on page 154 to ensure that your worksheet is complete. Print the worksheet when done. No check figure provided. Test your model (and your pivot table skills) as follows: Ms. Husker would like three more reports. 1. Printout of a table showing total salaries by gender. 2. Printout of a table showing total salaries by gender and by department. 3. Printout of a table showing employee name and bonus by gender for the Accounting department
