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.
    1. 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:
    1. Each sales associate and (3 pts.)
    2. 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.

 

error: