hi, i would like someone to check my work and if you can provide the formulas so i could see if i meed to fix mine. i am also having trouble eith goal seek, questions 4 and 5
"Your Name "FroYoToGo is a small business that operates a mall kiosk featuring gourmet frozen yogurt. The owner is interested in projecting sales revenues, cost of goods sold, gross profit, expenses and operating income for a five-year period. She has hired you as a consultant to prepare this projection using MS Excel. Year 1 estimates (based on the previous year numbers) have been provided for you. Projected changes for years 2-5 have also been provided. The owner requires an easy to read worksheet that should allow for quick analysis if projections for certain numbers change, such as the commission percentage or cost of kiosk rental. Year 1 Information (shou ld be linked to assumptions page): $30,000 peryear 35% of current year Revenue 15% of current year Revenue $250 per month $1500 per year $500 per year Sales Revenue: Cost of Goods Sold: Commission: Kiosk Rental: Marketing: Equipment Repair and Maintenance: Changes for future years: Revenue will increase 4 % per year from the previous year amount Marketing will decrease 5 % per year from the previous year amount Equipment repair and maintenance will increase 5 % per year from the previous year amount REQUIREMENTS: 1. Open a new work book and save it using the proper naming convention 2. Construct three worksheets within this file. The first worksheet will be your cover worksheet. The second worksheet will be for the assumptions which should be titled and labeled ASSUMPTIONS. The third worksheet will conta in the financial projections based on your paper design. The third worksheet shou ld contain an appropriate title. All dollars are to "display" to the nearest dollar. All percentages are to "display" to two decimals. Where appropriate, use mixed references unless an absolute reference is necessary. 3. Re-use-ability and user-friendly "assumptions", the financial projection outputs should calculate correctly and present the results in a professional manner. The user shou ld NEVER have to change anything or enter anything in the financial projection sheet no matter the change in assumptions. All output cells in the financial plan output worksheet will contain formulas with NO numbers in the formulas. are the main objectives. If there are any changes in any of the Five Year Financial Projection 5 Year 1 6 Year 3 Year 2 Year 4 Year 5 7 Sales Revenue 8 Cost of goods sold 9 Gross profit 10 11 Expenses: 12 Commission $ 30,000.00 $31,200.00 $ 32,448.00 $ 33,745.92 $ 35,095.76 10,500.00 $ 10,920.00 $11,356.80 $11,811.07 $12,283.51 19,500.00 $20,280.00 $ 21,091.20 $ 21,934.85 $ 22,812.24 4,500.00 $ 4,680.00 $ 4,867.20 $ 5,061.89 $ 5,264.36 250.00 $ 1,500.00 $ 1,425.00 $ 1,350.00 $ 1,275.00 $ 1,200.00 500.00 $ 6,750.00 $ 6,880.00 $7,018.45 $ 7,165.70 $ 7,322.12 250.00 $ 250.00 $ 250.00 $ $ 250.00 Kiosk rental 13 14 Marketing Equipment repar & maint. $ 16 Total Expenses $ 525.00 $ 551.25 $ 578.81 607.75 15 $ 17 $ 12,750.00 $ 13,400.00 $ 14,072.75 $ 14,769.15 $15,490.13 18 Operating income 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 4. Given the assumptions above, will the owner meet her goal of $20,000 Operating Income in Year 5? Explain. (Type your answer in the area below your data on the Financial Projection worksheet). Now use Goal Seek to determine the percentage increase in revenue "Your Name" FroYoToGo would need to have to achieve an operating income of $20,000 in Year 5. (Do not change any of the assumptions except for the % revenue increase). 5
