Small Sporting Goods Company - Economics Assignment Help

Assignment Task:

Read the following case. Using the information provided create a spreadsheet model to answer all 5 of the following questions. On a separate tab in the worksheet, summarize each answer indicating what question you are referring to. For example, “4. The linear trend fit produces an intercept of ___ and a slope of ____. Using this model vs the company forecast results in a MAPE of ___.” All formulas are given at the bottom of the assignment. If you do not summarize your answers in sentence form on a separate tab in the worksheet you will suffer an automatic 15% grade reduction.

Partial Marks may be given for partially producing the model required. After completing, upload the completed spreadsheet to the Forecasting Assignment dropbox under “Assignments” on Conestoga.

A small sporting goods company is considering investing $2500 in a project that will produce volleyball over the next five years. The company plans to produce and sell 300 volleyball in the first year and expects that volume to grow by 10% each year thereafter. The unit selling price forecast the company has developed is $21 in year 1, $22 in year 2, $25 in year 3, $28 in year 4, and $31.50 in year 5. Variable costs are forecast to be a rate of $15 per unit produced, and there will be a fixed overhead cost in each year of $500.

Use the above information to develop a simple cash flow sheet, and then apply Excel's NPV function to calculate the project value assuming a 10% discount rate. What is your answer?

Hint 1: Make sure your rates (discount, growth, variable cost) are in separate cells and built into formulas, not manually input into the formulas or else goal seek will not work!
Hint 2: Your column rows should look like this:

• Year
• Units Produced
• Price
• Revenue
• Variable Cost
• Fixed Cost
• Net Cash Flow

Suppose the company thinks it may be able to produce and sell more than currently planned. What growth rate of production would produce an NPV of $15,000? Hint: Goal Seek

Suppose instead that the company thinks it can reduce its variable cost rate. What rate would produce an NPV of $15,000? 

Use the graphing function in Excel to construct a scatterplot of forecasted price versus time, and fit a linear trendline to the data. What are the coefficients (intercept and slope) of the linear model, and what is the MAPE of a linear model forecasted prices, compared to the company's forecasted prices?

Using the data above
Provide the R-squared value for each independent variable, using Units Sold as your dependent variable.

• What variable is the best predictor of Units Sold?
• What variable is the worst predictor of Units Sold?
• Provide 2 actions you would take with this information?

Compare the actual units sold table with the initial prediction of the Unit Sold growth of 10% per year. 

• What is the total MAE across the 5 years? 
• What is the total MAPE across the 5 years?
• Was their initial forecast and forecasted growth accurate or inaccurate, why?
•  

Possible Formulas
Net Cashflow = Revenue – Costs
Revenue = Selling Price * Units
Costs = Fixed Costs + Variable Costs
Variable Costs = Variable Cost rate * Units
Net Present Value = NPV (discount rate, value1, value2, etc.) – Initial Investment
Next Year with Growth = Present * (1+Growth Rate)
Y = a+bx
a = y-intercept
b = slope
Simple Linear Regression done as describe in class -> Data -> Data Analysis -> Regression
Goal Seek done as described in class -> Data -> What-if Analysis -> Goal Seek

This Economics Assignment has been solved by our Economics Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.