The Snazzy Pup Company makes many items and one of them is a special dog collar....

Transcribed Image Text:

The Snazzy Pup Company makes many items and one of them is a special dog collar. The following information pertains to the product: ORIGINAL DATA Collars Sales in units 40,000 Per item: Sales price $17.00 Product Costs: Materials $ 3.50 Labor $2.50 Factory Overhead $ 2.00 Total Cost $8.00 In total: Selling costs $150,000 Administrative costs $100,000 REQUIRED; On the basis of the facts as given above, prepare the following schedules using a spreadsheet software package (preferably Excel). Each situation is independent of the others. A. Prepare the income statement using the original data. B. Snazzy Pup is thinking of adding more sales people. This will double the selling costs. Assume they can double the number of items sold with this change. Prepare a new income statement showing these changes. C. Snazzy Pup is wondering what would happen if they only sell half as many items. Assume selling and administrative costs stay the same. Prepare a new income statement showing these changes. D. Snazzy Pup is considering cutting sales prices to $15.00 per collar in hopes of doubling sales. Assume selling and administrative costs stay the same. Prepare a new income statement showing these changes. D E. Use Goal Seek in Excel (Data/What If Analysis Goal Seek) to determine the following: Each situation is independent of the others. 1) Assume the only change to the original data is that sales double, what sales price per item is needed to get the same net income as in Part B? 2) Assume the original data including unit sales, what is the total cost per unit needed to get the same net income in Part B? as 3) Assume the only change to the original data is that the sales price decreases to $15.00 per unit and sales in units double, what would selling costs need to be to get the same net income as in Part B? F. GO BACK TO THE ORIGINAL DATA. Use Scenario Manager in Excel Prepare and print a Scenarios C. Snazzy Pup is wondering what would happen if they only sell half as many items. Assume selling and administrative costs stay the same. Prepare a new income statement showing these changes. D. Snazzy Pup is considering cutting sales prices to $15.00 per collar in hopes of doubling sales. Assume selling and administrative costs stay the same. Prepare a new income statement showing these changes. E. Use Goal Seek in Excel (Data What If Analysis/Goal Seek) to determine the following: Each situation is independent of the others. 1) Assume the only change to the original data is that sales double, what sales price per item is needed to get the same net income as in Part B? 2) Assume the original data including unit sales, what is the total cost per unit needed to get the same net income as in Part B? 3) Assume the only change to the original data is that the sales price decreases to $15.00 per unit and sales in units double, what would selling costs need to be to get the same net income as in Part B? F. GO BACK TO THE ORIGINAL DATA. Use Scenario Manager in Excel. Prepare and print a Scenarios Summary Report for net income with the following options: Each situation is independent of the others. 1. Big Sale - Both sales in units and selling expenses double 2. Worst case - Sales in units are half as much as the original amount. 3. Lower prices - Sales price per unit decrease to $15.00 and sales in units double. 4. Lower unit costs - Product cost per unit decreases to $5.00. 5. Lower administrative costs - Administrative costs decline to $60,000 which causes a decrease in unit sales of 10% 6. Change two-Sales in units increase by 50% and sale price per unit decreases to $15.00 per unit. 7. Makeover - An exclusive model is introduced. This causes sales in units to increase by 10,000, the sales price per unit to increase to $20.00, unit costs increase by $1.00, and selling costs increase to $175,000. For your Scenarios Summary Report, be sure to format your Scenarios Summary by putting titles in for the variables. Conditional format your net income by highlighting with your choice of color any incomes above $200,000. Also format your net income with another color choice for those incomes below the current net income. The Snazzy Pup Company makes many items and one of them is a special dog collar. The following information pertains to the product: ORIGINAL DATA Collars Sales in units 40,000 Per item: Sales price $17.00 Product Costs: Materials $ 3.50 Labor $2.50 Factory Overhead $ 2.00 Total Cost $8.00 In total: Selling costs $150,000 Administrative costs $100,000 REQUIRED; On the basis of the facts as given above, prepare the following schedules using a spreadsheet software package (preferably Excel). Each situation is independent of the others. A. Prepare the income statement using the original data. B. Snazzy Pup is thinking of adding more sales people. This will double the selling costs. Assume they can double the number of items sold with this change. Prepare a new income statement showing these changes. C. Snazzy Pup is wondering what would happen if they only sell half as many items. Assume selling and administrative costs stay the same. Prepare a new income statement showing these changes. D. Snazzy Pup is considering cutting sales prices to $15.00 per collar in hopes of doubling sales. Assume selling and administrative costs stay the same. Prepare a new income statement showing these changes. D E. Use Goal Seek in Excel (Data/What If Analysis Goal Seek) to determine the following: Each situation is independent of the others. 1) Assume the only change to the original data is that sales double, what sales price per item is needed to get the same net income as in Part B? 2) Assume the original data including unit sales, what is the total cost per unit needed to get the same net income in Part B? as 3) Assume the only change to the original data is that the sales price decreases to $15.00 per unit and sales in units double, what would selling costs need to be to get the same net income as in Part B? F. GO BACK TO THE ORIGINAL DATA. Use Scenario Manager in Excel Prepare and print a Scenarios C. Snazzy Pup is wondering what would happen if they only sell half as many items. Assume selling and administrative costs stay the same. Prepare a new income statement showing these changes. D. Snazzy Pup is considering cutting sales prices to $15.00 per collar in hopes of doubling sales. Assume selling and administrative costs stay the same. Prepare a new income statement showing these changes. E. Use Goal Seek in Excel (Data What If Analysis/Goal Seek) to determine the following: Each situation is independent of the others. 1) Assume the only change to the original data is that sales double, what sales price per item is needed to get the same net income as in Part B? 2) Assume the original data including unit sales, what is the total cost per unit needed to get the same net income as in Part B? 3) Assume the only change to the original data is that the sales price decreases to $15.00 per unit and sales in units double, what would selling costs need to be to get the same net income as in Part B? F. GO BACK TO THE ORIGINAL DATA. Use Scenario Manager in Excel. Prepare and print a Scenarios Summary Report for net income with the following options: Each situation is independent of the others. 1. Big Sale - Both sales in units and selling expenses double 2. Worst case - Sales in units are half as much as the original amount. 3. Lower prices - Sales price per unit decrease to $15.00 and sales in units double. 4. Lower unit costs - Product cost per unit decreases to $5.00. 5. Lower administrative costs - Administrative costs decline to $60,000 which causes a decrease in unit sales of 10% 6. Change two-Sales in units increase by 50% and sale price per unit decreases to $15.00 per unit. 7. Makeover - An exclusive model is introduced. This causes sales in units to increase by 10,000, the sales price per unit to increase to $20.00, unit costs increase by $1.00, and selling costs increase to $175,000. For your Scenarios Summary Report, be sure to format your Scenarios Summary by putting titles in for the variables. Conditional format your net income by highlighting with your choice of color any incomes above $200,000. Also format your net income with another color choice for those incomes below the current net income.