(LG) regularly buys ammunition from Ammotec (a manufacturer). This week, LG purchased the following quantities from Ammotec at the following prices: • 9mm – 50,000

(LG) regularly buys ammunition from Ammotec (a manufacturer). This week, LG purchased the following quantities from Ammotec at the following prices: • 9mm – 50,000 rounds @ \$240.00/1,000 rounds • 40 S&W – 20,000 rounds @ \$300.00/1,000 rounds • 45 ACP – 10,000 rounds @ \$370.00/1,000 rounds LG is responsible for paying for the cost of inbound freight and they have negotiated a competitive flat rate with a freight company for \$350.00 per pallet with a maximum weight of 2,500 lbs. per pallet. LG allocates the cost of shipping the ammunition based on its dollar value. The weights per 1,000 rounds are as follows: • 9mm – 30 lbs. • 40 S&W – 40 lbs. • 45 ACP – 50 lbs. LG retails Ammotec’s ammunition at the following prices: • 9mm – \$280.00/1,000 rounds • 40 S&W – \$350.00/1,000 rounds • 45 ACP – \$430.00/1,000 rounds All of LG’s customers pay by credit card which results in LG paying credit card fees of 3% of the total sales price (product price plus shipping price). Additionally, LG incurs a \$5.00 fulfillment cost to handle each case of 1,000 rounds of ammo it sells. calculate the total gross profit in \$ as well gross margin as a % of total product sales for each of the three products individually from this purchase assuming that all of the ammo sells. Assume that there is no cost of capital. Additionally, assume that LG charges customers for shipping on top of the retail prices listed above at a rate of 6% which represents 0% markup. show all of your steps in Microsoft Excel formatted in a presentable manner and attach to your email. highlight your answers in Excel in Blue for question 1. For any information that you are unsure of, just list your assumptions and show your work.. Problem 2 – Excel: LG also sells accessories that are related to the shooting industry. Assume that LG has \$10,000 (cost basis including inbound freight) in accessories currently in inventory available for sale for a total retail price of \$15,000.00.. Assume the same credit card fees from problem 1, the same outbound shipping fees and shipping margin, and assume a fulfillment cost of 5% of the product sales price (not including shipping) for this segment. calculate the total gross profit in \$ as well gross margin as a % of total product sales for the accessory division and highlight your answers in green. Problem 3 – Excel: For the last 30 days, LG sold the following at the prices listed in problem one: 1. 9mm – 50,000 rounds 2. 40 S&W – 20,000 rounds 3. 45 ACP – 10,000 rounds 4. Accessory Division – \$1,500 Retail (not including shipping) Calculate the return on investment (ROI) separately for each of the above four investments and show your answers in Excel highlighted in red.