PROBLEM 10-8. "What If" Analysis Using a Spreadsheet Cynthia Morton is in the process of developing a spreadsheet to budget annual sales and purchases of in ventory for her company, Morton Fine Carpets, a retail store that sells handmade car. pets from the Middle East. In 2006, sales were as follows, Quarter I $200,000 Quarter 2 $210,000 Quarter 3 $220,000 Quarter 4 $185,000 Inventory at the end of 2006 is $66,000. Required Help Cynthia build a spreadsheet that will allow her to examine the impact on pur- chases of inventory of the following three items: 1. Sales growth from 2006 to 2007. Cynthia believes that quarterly sales will grow by 10 percent in 2007 compared to the corresponding quarter in 2006. And sales in the first quarter of 2008 will be 10 percent higher than in 2007. However, she would also like to explore the effect on purchases of alternative growth rates. Thus, your spread- sheet must allow you to change this value and observe the effect on all other values. 2. Inventory on hand at the end of each quarter. Cynthia is tentatively planning on having ending inventory equal to 20 percent of the amount needed to meet next quarter's sales. She would also like to explore the effect on purchases of alternative rates. Thus, your spreadsheet must allow you to change this value and observe the effect on purchases. 3. Cynthia estimates that cost of goods sold as a percent of sales will be 30 percent. She would also like to explore the effect on purchases of different rates. Thus, your spreadsheet must allow you to change this value. After you have developed your spreadsheet, calculate purchases in the first through the fourth quarter of 2007 for the following combinations Combination 1-Sales growth of 10 percent. Ending inventory of 22 percent, Cost of goods sold of 33 percent. Combination 2-Sales growth of 15 percent, Ending inventory of 24 percent, Cost of goods sold of 28 percent. Combination 3-Sales growth of 9 percent, Ending inventory of 34 percent, Cost of goods sold of 40 percent.