Overview
In this exercise, you will act as a consultant to Dirt Bikes USA and apply spreadsheet software to improve management decisions. You have been tasked with: 1) analyzing transactional data on suppliers based on several different criteria to select the best suppliers for the company, and 2) modeling profit to estimate the price for which profit is maximized.
You must submit two files for this assignment: 1) an Executive Summary of your analysis and recommendations, and 2) your Excel file with your analysis, modeling, calculations, and such. The Executive Summary must be submitted as a Word or PDF file, and your Excel work must be provided as an Excel file (*.xls or *.xlsx file; no PDF or other formats).
Supplier Analysis
Dirt Bikes USA uses quality components from all over the world in manufacturing its products. The Moto 300 and Moto 450 use the same braking system, which consists of 11 parts. Dirt Bikes USA has excellent relationships with its various suppliers, and to-date it has not experienced significant variance in performance or quality for parts acquired from approved vendors. But Dirt Bikes USA wants to ensure it is selecting the best suppliers for each part.
The worksheet SupplierOrders (in the Excel file Project1_SpreadsheetModeling_DirtBikes.xlsx Download Project1_SpreadsheetModeling_DirtBikes.xlsx ) contains a list of all Moto 300 and Moto 450 braking system parts that were ordered between September and November. For each order, the fields in the worksheet include vendor name, purchaser order number, vendor’s item number, item description, cost per item, quantity ordered, total cost of the order, the vendor’s accounts payable terms (the number of days allowed to pay for the order), order date, and actual arrival date.
Use the data in the worksheet to prepare recommendations to Dirt Bikes USA management. Determine at least three data items that are meaningful to select the best supplier(s) for each part. For example, price is one way for compare suppliers. For each data item you identify, use Excel tools and functions to summarize the data. Consider also how you will use the data to determine the best supplier.
In your Executive Summary, explain how you assessed the suppliers and provide your recommendations with supporting evidence. Your Excel file contents should reflect your Executive Summary (meaning that the recommendations in your Executive Summary should have supporting analysis/calculations/etc. in your Excel file).
Very Important Note: Your recommendations should be based solely on your analysis of the data and information provided in this assignment. Do not make assumptions or apply information that has not provided in the scope of this assignment. For example, COVID is a current event that has impacted many supply chains, but it should not be referenced in your recommendations since there are no facts provided in this assignment regarding the impact of COVID on Dirt Bikes USA or its suppliers. The objective of this assignment is for you to apply Excel to perform spreadsheet analysis.
Profit Analysis
The Moto 300 currently retails for $7295. Dirt Bikes USA analysts have determined that demand for the Moto 300 is dependent on the retail price, and they formulated that Moto 300 demand can be represented by:
Demand = 16,000 – 2 * Price
The accountants estimate that the total cost for the Moto 300 can be represented by:
Total Cost = $750,000 + 4 * Demand^2
(Note that Demand^2 is Demand squared, or Demand * Demand)
Develop a mathematical model for Total Profit from the Moto 300 in terms of the price of the Moto 300.
In Excel file provided with this assignment, on the ProfitModel worksheet, implement the model developed above and construct a what-if data table to estimate the price for which total profit is maximized. Some basic entries have been made for you to get you started. Be sure to follow the guidelines for good spreadsheet design and quality (formatted, understandable, verified, etc.).
Suppose also that Dirt Bikes USA management is hesitant to change the price of the Moto 300 too much, but they want to increase the total profit by 15%. Use Excel what-if analysis to determine the retail price of the Moto 300 needed to achieve this profit goal.
Executive Summary:
Your Executive Summary should look professional, be well-written, and clearly and concisely present your analysis and recommendations. There is no page count, font/spacing/margin requirements for the Executive Summary. You will be graded on the quality of your work, not the quantity of your words.
Notes from professor:
Note that there are two worksheets in the file; one with the supplier data to be analyzed and one with a start for the profit model. Be sure upload your Excel file, showing your work for the project, when you submit your project.
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but you proved you are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 11***| Rating: ⭐⭐⭐⭐⭐
"This company is the best there is. They saved me so many times, I cannot even keep count. Now I recommend it to all my friends, and none of them have complained about it. The writers here are excellent."
"Order a custom Paper on Similar Assignment at essayfount.com! No Plagiarism! Enjoy 20% Discount!"
