Final Exam Assignment: financial buying
Final Exam Assignment: (Total pts.: 50)
Submit: Submit Excel file and PDF file on BB.
- Follow all instructions closely and utilize all concepts learned from wk1-wk10 to develop your excel work.
- Save file as: Student name_Final Exam_AMM251A.xlsx.
- Attempt each Question on a new worksheet
- In addition to the required information for each case study, all tables will be evaluated on the following parameters:
- Correct embedded formulas,
- Alignment
- Uniform font formatting
- Number formatting
- Borders and Shading
- Bleeding
Question1(15 points)
In preparation for a buying trip, a buyer determined a 54% markup was required on all purchases which amounted to $750,000 at retail. Upon completion of the trip, the buyer reviewed the orders placed. At the end of the season, a vendor analysis revealed the sales results shown below:
Cost Initial retail Final retail
Resource A $ 25,000 $ 50,000 $ 50,000
Resource B 40,000 85,000 83,000
Resource C 60,000 135,000 130,000
Resource D 55,000 135,000 135,000
Resource E 75,000 170,000 167,000
Resource F 90,000 175,000 165,000
Total $345,000 $750,000 $730,000
Compute the following: (Excel table: 10 pts)
(a.) Initial markup percentage for each resource.
(b.) Final markup percentage achieved for each resource.
(c.) Compare the anticipated markup percentage of the entire purchase with the actual markup percentage achieved.(2 pts)
(d.) Which vendor(s) markup percentage performance was superior to the overall markuppercentage and by how much?(3 pts)
The format should look like:
Question 2(15 points)
Ms. Kane, the China and Glass buyer of Crystal Clear, Inc., was asked to present a yearly profit and loss in skeletal form to the divisional merchandise manager. In addition, she wanted to prepare a summary of comments that included a comparison of her performance this year with that of her major competitor, China Seas, Ltd. Ms. Kane obtained the following information from
Crystal Clear’s statistical department:
Gross sales $135,000
Alteration and workroom costs $ 1,000
Opening inventory, at cost $ 49,500
Closing inventory, at cost $ 61,000
New purchases, at cost $ 77,500
Inward freight $ 1,500
Cash discounts 4% (of new purchases)
General overhead $ 11,000
Advertising $ 9,000
Salaries $ 20,000
Rent $ 8,600
Customer returns and allowances $ 15,000
Ms. Kane obtained, from outside research, profit and loss data on her competitor, China Seas, which had the following results for the same year:
Profit = 6% or $7,350 | Operating expenses = 40%
Ms. Kane then prepared a skeletal profit and loss statement for her department and for that of her competitor China Seas. Upon completion of this task, she compared the two statements. Support the suggested action mathematically.
- Develop 2 P & L statement to compare Crystal Clear and China Seas performance.
(Note: Use concepts from RMI to calculate Total Cost of Goods Sold. Calculate operating profit based on the direct/indirect expenses mentioned above. Use the format given below)(10 pts)
- Answer the following question in no less than 100 words.
- Compare the two statements and suggest what actions should Ms. Kane take to get immediate results and have an impact on the department’s profitability?
Support your response mathematically.(5 pts)
Question 3((20 points)
Recreate the table given below in Excel. Add columns to the table below to complete needed information.
- Determine the selling cost % for:
- Each sales associate and (3 pts.)
- For the entire department based on the data given below: (1 pts.)
- Format your worksheet using line formatting, shading headers, alignment and number format. (5 pts.)
- Using conditional formatting, highlight the cost% cell for employees with cost% >6% (3 pts.)
- Sort the table in descending order of selling cost % per employee (3 pts.)
- Plot a graph showing Wages per hour versus selling cost % for all employees. Format and label both axis with the appropriate information. (5 pts.)
Note: X-axis (selling cost%) and Y axis (wages per hr), Show employees on X-Axis.
