Assignment
Dynamic Dog Company is planning on introducing a state of the art treadmill for dogs. This treadmill will track steps, heart rate, and have a video screen of animals a dog would typically chase, to motivate the dog to run on the treadmill. Because of the features they plan to spend a significant amount of up-front costs prior to launching the product in the marketplace.
The company anticipates the product will have a 5 year life cycle. Below are a list of projected costs, unit sales projections and anticipated unit selling price. Management desires a return on sales of 35% on new products over their lifecycle.
Prepare a life cycle budget for each of the five years of the treadmill's product lifecycle AND the total of all five years. Calculate average profit for the five years.
Calculate Return on Sales for this product's entire life cycle.
Note: there were no beginning or ending inventories each year, so product costs = CGS
Prepare your model on the worksheet below, under the forecasted data. The forecasted data section can serve as the "input section" for your model.
Here are some Excel features you should use in your model:
1) ABSOLUTE REFERENCES IN FORMULA: You will use the unit selling price in the yellow cell below for all years when computing total sales for each year. When you use that cell in your formula for Sales for Year 1, be sure to make it an absolute refererence before copying it over to the remaining years. If you don't know how to do that.
It's important that you do it this way, so that when you go to use the next feature, GOAL SEEK, your model will work as needed to complete the quiz.
2) Investigate how to use the GOAL SEEK feature in Excel. GOAL SEEK is a menu option under "TOOLS" on the top of the EXCEL menu bar. You don't have to program this into your spreadsheet, rather, it is a tool you will need to use when taking quiz B.
3) Use the AVERAGE function to compute the average profit over the product life. This is a "function" in Excel.
4) Use the SUM function to add columns or rows. See the same link as #3 for how to insert a SUM function. This avoids having to type many cell references into your addition formulas when you are adding numbers that appear continuously in a column or row.
NOTE: be sure to use these features. Test 4 will contain some Excel specific questions of techniques used in this model.
Attachment:- Excel-Model.rar