BUS 185 Assignment Fourteen- Capstone One-- due thr 29 April 2004

 

This assignment is based upon an example provided in class. The specific expectations are that you first investigate the validity of the assumptions made in class, second, to create an excel spreadsheet model that determines the optimal marketing versus inventory costs, and third to write a report based on your findings.

  LINK TO EXCEL SHEET

Background

In class we discussed creating a company that purchased computer equipment, assembled the parts and then sold the finished product via online sales. The company was given a $20,000.00 grant to get started. During class we made several assumptions:

 

  1. The cost of creating the final product was $200.00
  2. We expected to make a profit of $10 per unit sold.
  3. Shipping charges were $40.00.
  4. We also made assumptions about the relationship between marketing dollars spent and actual sales, i.e., $500 spent on marketing equated to 20 computers sold, whereas $1000 spent on marketing equated to 45 computers sold.
  5. We also made the assumption that the company did not receive sales from each quarter until the next quarter.

 

Once these assumptions were made we then generated a scenario to determine how to balance out our inventory and capital reserves (see Figure below):

 


 


Your task is to first establish the validity of the assumptions. For example, can a computer be realistically built for $200? It is realistic to expect only $10 profit per unit? Can we make some kind of calculation or estimate of marketing dollars spent versus computers sold?

 

Secondly, you are to replicate the excel sheet provided and extend the data for five more quarters to determine the optimum condition between marketing expenditures, sales, and inventory costs. (Hint: It might be best to select a supply chain type before plugging the numbers in). Your excel sheet should also allow easy manipulation to determine different scenarios. Remember the main goal here is to have as little inventory as possible, while still having the ability to meet demand to maximize our profit potential. Part of your grade on this assignment will be related towards how much profit you have remaining after the eight quarters.

 

Lastly, you are to provide a one to two page memo report that explains your findings and any assumptions that you made.