1. Using Excel - recreate this spreadsheet and then do the following: a. Create two new columns and calculate and compare a 3 month moving average and a weighted average forecast for months 4 through 12, using the weights 3, 5, and 8. b. Create two new columns showing the AIA-FI of each method c. Determine the MAD for each forecast and note which is more accurate. d. Using the Linear Regression - forecast months 13-24. This is the forecast for the business WITHOUT the new product. e. In a column next to your 13-24 forecast, create a new forecast for 13-24 by adding Marty's desired increase in sales from the sale of new hoverboards in order to earn the extra $30,000 profit (Sales dollars from BE part #5). This is the forecast for the business WITH the new product. PART III - Effective Rate of Interest (15 points) 1. The Raw Materials Marty McFly purchases cost $50 each. Using the units you determined in your Break Even Analysis for the $30,000 profit (from BE part #5). Calculate the total Marty McFly needs to spend each month in order to purchase those raw materials. 2. Marty McFly purchases these materials from Holomax Inc. under sales conditions of 3/10 N/30. a. What would the Effective Rate of Interest be if payment were made on day 107 b. Day 11? c. And on day 307 PART IV - Memo to Marty (15 points) Write your 1 page business memo to Marty McFly explaining what you discovered in your above analysis. Specifically write about the following, but cumulative terminology and supporting information from the first half of the course is expected: a. How many units he needs to move of his new product in order to generate the additional $3,000 b. How many employees he will need to maintain the desired level c. How forecasting can help him manage his business and explain how you determined which method (that he had you examine) is more accurate. d. Finally reinforce to him why working capital management is important. Suggest methods of securing his asset Parts Inventory, and managing the liability Accounts Payable for those parts by reviewing the Effective Rates of Interest he would be paying if paid on various dates. e. Properly and professionally open and close your memo, Remember - you work for this company. f. Use the following header below. The > brackets are not needed, 1. Using Excel - recreate this spreadsheet and then do the following: a. Create two new columns and calculate and compare a 3 month moving average and a weighted average forecast for months 4 through 12, using the weights 3, 5, and 8. b. Create two new columns showing the AIA-FI of each method c. Determine the MAD for each forecast and note which is more accurate. d. Using the Linear Regression - forecast months 13-24. This is the forecast for the business WITHOUT the new product. e. In a column next to your 13-24 forecast, create a new forecast for 13-24 by adding Marty's desired increase in sales from the sale of new hoverboards in order to earn the extra $30,000 profit (Sales dollars from BE part #5). This is the forecast for the business WITH the new product. PART III - Effective Rate of Interest (15 points) 1. The Raw Materials Marty McFly purchases cost $50 each. Using the units you determined in your Break Even Analysis for the $30,000 profit (from BE part #5). Calculate the total Marty McFly needs to spend each month in order to purchase those raw materials. 2. Marty McFly purchases these materials from Holomax Inc. under sales conditions of 3/10 N/30. a. What would the Effective Rate of Interest be if payment were made on day 107 b. Day 11? c. And on day 307 PART IV - Memo to Marty (15 points) Write your 1 page business memo to Marty McFly explaining what you discovered in your above analysis. Specifically write about the following, but cumulative terminology and supporting information from the first half of the course is expected: a. How many units he needs to move of his new product in order to generate the additional $3,000 b. How many employees he will need to maintain the desired level c. How forecasting can help him manage his business and explain how you determined which method (that he had you examine) is more accurate. d. Finally reinforce to him why working capital management is important. Suggest methods of securing his asset Parts Inventory, and managing the liability Accounts Payable for those parts by reviewing the Effective Rates of Interest he would be paying if paid on various dates. e. Properly and professionally open and close your memo, Remember - you work for this company. f. Use the following header below. The > brackets are not needed
