I need an Excel worksheet for the 5.0 section that is on my business proposal. It is for the pro forma based on the values in the income statement provided.

Papa Geo's Restaurant Budget Proposal for [2017] BUSN-278 July 2017 Professor Angela Duck DeVry University Your Company Name Your Company Name Table of Contents Section Title Subsection Title Page Number Your Company Name 1.0 Executive Summary 2.0 Sales Forecast 3.0 Capital Expenditure Budget 4.0 Investment Analysis 5.0 2.1 Sales Forecast 2.2 Methods and Assumptions 4.1 Cash Flows 4.2 NPV Analysis 4.3 Rate of Return Calculations 4.4 Payback Period Calculations 5.1 Pro Forma Income Statement 5.2 Pro Forma Balance Sheet 5.3 Pro Forma Cash Budget 7.1 Appendix 1: [description] 7.2 Appendix 2: Pro Forma Financial Statements 6.0 Works Cited 7.0 Appendices Your Company Name [description] (Please put page numbers in the last column of the table of contents above, because they apply to your finished assignment. Do this after your project is complete. Also, remove this text and all text that is in italics in this template when finished your project.) (Also, please submit your Excel spreadsheet that shows your supporting calculations.) Your Company Name 1.0 Executive Summary Papa Geo's Restaurant targets the middle-class income families in the big apple. The restaurant will be buffet style; here for a low price, individuals will eat from the serving bars that are uniquely designed. The main food served in this establishment will be authentic Italian food composed of pizza, pasta, soup, desserts, bar and a self-serve soda bar. There will be a game room as well which will produce another supply of revenue for the eating house. Because of the reasonably priced food within the space families can hopefully deter from the fast food restaurants. Though we will be in direct competition with the identical lines of food, we will still obtain an excellent weight in selections the client would possibly like that Papa Geo's does cater too. The Capital Expenditure Budget provides an estimated cost of what will be initially needed for the restaurant to run properly. It is separated by sections so that finding items that are necessary for specific tasks are easier to locate and update as the business progresses. The budget will allow for the minimum items necessary to be ordered but it is not by any means final as a change in menu or additional items necessary will be checked over biweekly to make certain all requirements have been met. Strategy of the business: The anticipated strategy for this business would be to initially promote the business by targeting the local population and providing nourishment that is suited for not only the families in the area but as well as the tourism. The projected site for this restaurant would be near the center of the city quite close and convenient to the traffic of people who are shopping, taking a lunch break, grabbing something on the go, or celebrating a special occasion meal. The investment for this business is going to be in step by step phases, and expected to provide a regular growth of 8 to 10 percent over a period of time. The marketing and pricing strategy would be based on competitive analysis and we would also offer regular discounts on the food menu to those who frequent our establishment. The long term plans remain of expansion to other cities and to make it into a large food chain. The ongoing proposal would also include fast foods and home delivery at later stages which would make this profitable in the upcoming years. Financial: The budget of $100,000 is being proposed for the following reasons: Your Company Name Break up of cost factors: 1. Initial costs of renting and landscaping would incur about $20,000, which would rather a sunk cost altogether 2. Overhead expenses like crockery, furniture,water and electricity would tune up to another $20,000 3. Staff salary and remunerations would primarily staff from the third month onward as the business gets stabilized, which might tune up to another $ 50,000 4. Expenses regarding the purchase of food materials and breakages if any, provision of $10,000 would be provided. This is how the break up for the given business proposal is designed, so that we start the business with a decent minimum investment and try to break even by the period 5 to 10 years NPV and IRR: $9,090.91 YEAR 1 NPV RETURN 28% IRR 1 -100,000 2 5,000 3 10,000 4 15,000 5 20,000 6 40,000 7 65,000 8 80,000 9 120,000 10 150,000 THE CASH FLOW STATEMENT: Your Company Name OPERATION ACTIVITIES: Cash receipts from sales or for the performance of services Payroll and other payments to employees Payments to suppliers and contractors Rent payments Payments for utilities Tax payments INVESTMENT ACTIVITIES: Purchases of property, plant and equipment Proceeds from the sale of property, plant and equipment Purchases of stock or other securities (other than cash equivalents) Proceeds from the sale or redemption of investments FINANCIAL ACTIVITIES: Proceeds from loans, notes, and other debt instruments Installment payments on loans or other repayment of debts Your Company Name Cash received from the issuance of stock or equity in the business Dividend payments, purchases of treasury stock, or returns of capital BALANCE SHEET: LIABILITIES ASSETS Capital $100,000 Sales Overhead expenses $20,000 Debtors Purchases $20,000 Gross profit Salaries $50,000 Tax Creditors Papa Geo's mission is to be a full service, family Italian restaurant offering affordable, high quality Italian cuisine inspired by authentic family recipes. Our goal is to provide our customers with an entire dining experience that exceed their expectations on every visit. We do this by recommending add-on items that will enhance their dining experience. Our restaurant is clean and the quality is always high. We value the people who work for us. Quality employees make quality food, keep the restaurant cleaner, give better service, and stay employed longer because they like working at Papa Geo's. We have found that friendly managers hire friendly staff and friendly staff attract loyal customers. THE OBJECTIVES ARE: Provide the highest quality Papa Geo's food and service that the community has been receiving over the years in this destination of food. Create an atmosphere where each person can work as a team member, with clear goals and high standards that profit everyone. Combine the corporate marketing strategies with my own to build volume quickly. Your Company Name High gross margins. Maintain food and labor costs consistent with our competition. Maintain and expand our reputation. Remain a neighborhood family restaurant. KEYS TO SUCCESS Papa Geo's Restaurant name and reputation will be known and progress with advertising. The products we serve are of the highest quality. We combine this quality with great service and atmosphere. We then add menu items that appeal to all ages of the family. Current management and staff has a great deal of knowledge and experience. Location, Location, Location! Here in New York City you can't exactly go wrong with location, because of the tourism and the bushels of people are everywhere. Food is known in this area. A frequent diner program and birthday club will be available to promote repeat customers as well as a tool to track sales. Management that treats every employee equally. We create an atmosphere where employee's love coming to work and can earn good money. Great employees make for a great restaurant. 2.0 Sales Forecast 2.1 Sales Forecast The financial year for the company is from January to December, the restaurant will anticipate to open by December 2017. During those 3 months at the end of the business year will be the \"Grand Opening\" for Papa Geo's. With any new opening customers will first have to discover the place and then the popularity of the restaurant will spread and bring in more customers. We are first assuming that a quarter of the population 8,000 of 10,000 million will first come in to dine at Papa Geo's at least twice a month. At the beginning of 2018 we will start Your Company Name to see an increase in customers. By the end of the year we are hoping that from the original 10,000 families that 75% will end up eating at different rates but regularly at Papa Geo's. The first quarter will continue to come in at least twice a month and then the second quarter will come in at least once every 6 months and the third quarter will come in at least once every 4 months. We are estimating that at least 1,000 families will come in once throughout the year as a special occasion dinner. In the third year we are projecting that at least 80% of the population will be dinning at Papa Geo's on an average of 11times a year from then on. When times are hard because the economy takes a down turn families will often cook at home so as not to waste money on outside food. However statistics show that once the economy improves the number of families that go out to eat will increase. Year Sales Year 1 $168,000 Year 2 $177,600 Year 3 $188,760 Year 4 $203,890 Year 5 $224,220 2.2 Methods and Assumptions As there was no past data to rely on a conservative estimate was made to project the sales that would occur. These figures are not definite and will likely change over time as figures are being actualized every year. These assumptions were based on an article written by the National Restaurant Association where they stated that as long as the economy keeps doing well the estimated sales in restaurants will exceed $608 Billion in 2017 alone (National Restaurant Association, 2017). From the proposed forecast of sales for 2017 we are assuming that the economic condition in growth will continue each year. Nonetheless with any economy nothing is for sure and sales should be monitored and growing prices to keep up with costs will most likely need to be reviewed bi-annually. Your Company Name 3.0 Capital Expenditure Budget The capital expenditure budget below is based on projected costs, expenses and depreciation for Papa Geos Restaurant. Based on research of the demographic area the average costs of leasing a space large enough for the restaurant needs will costs $24,000 annually for a 5 year lease agreement. Food supply budget of $48,000 is based on the amount of project customer transactions annually. The straight line depreciation method was used to determine the annual depreciation for appliances, office equipment, restaurant furniture and cooking tools. Below you will find a breakdown of the monthly costs of each itemized costs and expense. Rent: $2,000 monthly, Food supply: $4,000 monthly, Utilities Expense: $550, Administrative Costs: $109-135, Office Equipment for initial start-up: $1,500 Insurance: $48-54, Kitchen Appliances: $23,971, Restaurant Furniture: $5480, Cooking Tools: $2100 and Depreciation of $2859 annually for 5 years. Item Cost of registering a business Renovation of facility Cost Quantit y 300 1500 0 Total cost 300 1 15000 Source Notes and Assumptions ehow.com Cost of registering includes: actual cost of registering ($70), legal fees and misc expenses (approx $230) Given This includes all kinds of beautification etc that the restaurant needs to start operations Your Company Name Soda fountain bar 2 pizza ovens salad and Pizza/dessert bar Commercial Refrigerator Cash Register 3621 849 1450 3529 170 1 2 1 1 2 Soda-dispenser.com Prices quoted may or may not be the same at later dates ebay Prices quoted may or may not be the same at later dates ebay Prices quoted may or may not be the same at later dates Coldtechcommercial.co m Prices quoted may or may not be the same at later dates ebay Prices quoted may or may not be the same at later dates 1100 ebay Prices quoted may or may not be the same at later dates Prices quoted may or may not be the same at later dates 3621 1698 1450 3529 340 Video game vending machine Type 1 550 2 Type 2 750 2 1500 ebay Laptop for management 275 1 275 ebay Laptop purchased with Your Company Name Warranty, Price quoted may or may not be the same at later dates desk for mgmt 25 1 25 ebay Prices quoted may or may not be the same at later dates Prices quoted may or may not be the same at later dates Staff Microwave 319 1 319 ebay Staff cupboard 100 1 100 Assumed staff refriferator Tables for the restaurant Chairs for the restaurant Busing cart for restaurant Commercial dishwasher 700 279 55 1 20 80 700 5580 4400 ebay Prices quoted may or may not be the same at later dates tableschairsbarstools.co m Prices quoted may or may not be the same at later dates restaurant-services.com Prices quoted may or may not be the same at later dates Prices quoted may or may not be the same at later dates 50 1 50 ebay 2500 1 2500 ebay Prices quoted may or may not be the Your Company Name same at later dates Restaurant signage 124 Total 1 124 brightledsigns.com Although this is a form of advertising, this wouldn't be considered as an operating cost given the long term (more than a year) nature of its use 42611 The above table provides an estimate of the capital budget required for Papa Geo's restaurant. For each item, sources and assumptions used are explained towards the right side of the table. Other generic assumptions beyond those already mentioned are provided below: Miscellaneous cooking and handling equipment cost will be considered operational expenses and will not be capitalized given the short term (less than a year) use of such assets. Cutlery, drinking cups etc would be considered to fall into the same category The total cost of registration is considered as capital expenditure due to its long term nature of use Any fixtures like lighting etc are considered as operational expenses and will not be capitalized The total capital budget required is US$ 42,611. Major component of this budget are renovation and equipment costs, which is true of restaurant businesses. The total capital budget required is US$ 42,611. Major component of this budget are renovation and equipment costs, which is true of restaurant businesses. 4.0 Investment Analysis Your Company Name 4.1 Cash flows Year 1 Year 2 Year 3 Year 4 Year 5 9,38,944 15,61,492 16,22,390 16,85,664 17,51,404 Sources of Cash Total income (from food and vending machines) Uses of Cash Cost of Food 282880 294195.2 305963.008 318201.528 3 330929.589 5 depreciation on equipment 3069.2 3069.2 3069.2 3069.2 3069.2 Salaries 348636 362581.44 377084.697 6 392168.085 5 407854.808 9 employee benefits 21887.2 22762.688 23673.1955 2 24620.1233 4 25604.9282 7 24000 24000 24000 24000 24000 utilities credit card fees inventory holding costs advertising and marketing 23,473.60 39,037.30 40,559.76 42,141.59 43,785.11 2,347.36 3,903.73 4,055.98 4,214.16 4,378.51 15,000 10000 10000 10000 10000 Your Company Name Rent Insurance interest income - opex (for purposes of tax calculation) Taxes add back depreciation (non-cash expense) Net Cash Flows 630000 630000 630000 630000 630000 1000 1000 1000 1000 1000 13320 13320 13320 13320 13320 (4,26,669) 1,57,623 1,89,665 2,22,929 2,57,462 - 42,558.10 51,209.42 60,190.80 69,514.82 3069.2 (4,23,600) 3069.2 1,18,134 3069.2 1,41,524 3069.2 1,65,807 3069.2 1,91,017 Highlights and Assumptions: For this cash flow analysis, only operating activities are considered and it is assumed that there are no investing and financing cash flows that may have material impact on the business Average meal cost $4 in materials and labor, costs expected to increase about 4 percent a year -- the same as increase in sales. From the sales forecast page, it is assumed that 1360 families eat at the restaurant per week Equipment cost depreciated over a 5 year period, on a straight line basis Salaries assumed to grow at about 4 percent per year Cost of benefits to increase at 4 percent - in proportion to salaries Utility bills assumed to average out for the year and over the years Given : 2.5% of sales 10% of following month's sales is given. For the whole year, assumed at 10% of the year's sales of food only. Vending machine sales not included Assumed to be higher in the first year and then average out at lesser cost over the next 4 years Rent assumed to be 15 dollars per sq foot Your Company Name It is assumed that the entrepreneur take a 100k loan Assumed tax rate to be 27 percent. Actual tax rate is slab-based and may vary Includes all capex items except costs of registration and renovation expenses 4.2 NPV Analysis Interest rate 15% Year 1 Cash Flow (4,23,600) PV factor PV of cash flow 2 1,18,134 100% 3 1,41,524 87% 4 1,65,807 76% 5 1,91,017 66% 57% (4,23,600) 1,02,725 1,07,013 1,09,021 1,09,214 Cumulative PV (4,23,600) (3,20,875 ) (2,13,863 ) (1,04,842 ) 4,373 Net Present Value 4,373 Highlights and Assumptions: As can be seen from the above table, the net present value for the project is positive which shows that the project should be pursued. 4.3 Interest rate of 15% has been taken for calculation, assuming that the project would be financed with debt taken at the rate Rate of Return Cost of Capital 6% Year Net Cash Flow 1 (4,23,600) 2 1,18,134 3 1,41,524 4 1,65,807 5 1,91,017 Your Company Name PV Factor PV of net cash flow 100% 94% 89% 84% 79% (4,23,600) 1,11,447 1,25,956 1,39,215 1,51,303 Cumulative PV (4,23,600) (3,12,153 ) (1,86,197 ) (46,982) 1,04,321 Net present value 1,04,321 IRR (Internal rate of return) 15% Highlights and Assumptions: The project presents a rate of return on 15% which can said to be attractive, as against a cost of capital rate of 6% 4.4 Payback Period Year Cash Flow Payback 0 (4,23,600) NA 1 1,18,134 N/A 2 1,41,524 N/A 3 1,65,80 7 N/A 4 1,91,017 2.99 Highlights and Assumptions: The project has a payback period of 2.99 years, essentially it would pay back for itself in about three years' time. 5.0 Pro forma Financial Statements Your Company Name The Performa income statement is prepared using the contribution format. The sale price per year grows by $0.5 per year in order to adjust the inflationary factor and expected return. The variable cost comprise of direct material, direct labor and commission charge. Commission charge is a variable expense because it is directly related to sales at the rate of 2.5% of sales. The insurance cost remains the same and the fixed operating cost includes the deprecation, administrate and selling expenses. The salary of the owner is also included in the operating cost and it grows due to increment in salary of owner and other operating expenses although the depreciation expense will decline due to decline balance method of depreciation. Your Company Name Performa Income Statement Particulars Year 1 Units Sold Year 2 20,000 7.0 Year 3 20,600 $ 7.5 Year 4 21,218 $ 8.0 Year 5 21,855 $ 8.5 22,510 Sales Price $ $ 9.0 Revenues $ 140,000 $ 154,500 $ 169,744 $ 185,764 $ 202,592 Material Cost $ 80,000 $ 82,400 $ 84,872 $ 87,418 $ 90,041 Wages $ 12,000 $ 13,000 $ 14,000 $ 15,000 $ 16,000 Credit Card Commission $ 3,500 $ 3,863 $ 4,244 $ 4,644 $ 5,065 Total Variable Cost $ 95,500 $ 99,263 $ 103,116 $ 107,062 $ 111,105 Contribution Margin $ 44,500 $ 55,238 $ 66,628 $ 78,701 $ 91,486 Insurance $ 1,000 $ 1,000 $ 1,000 $ 1,000 $ 1,000 Fixed operating cost $ 12,000 $ 13,000 $ 14,000 $ 15,000 $ 16,000 Total Fixed Cost $ 13,000 $ 14,000 $ 15,000 $ 16,000 $ 17,000 Net Profit $ 31,500 $ 41,238 $ 51,628 $ 62,701 $ 74,486 Variable Cost Fixed Cost Your Company Name 5.2 Pro Forma Balance Sheets Provide a screenshot of your balance sheets, and describe key figures they contain. 5.3 Pro Forma Cash Budget Provide a screenshot of the cash budget and describe the impact of the budget on cash balances. 6.0 Works Cited List any sources you cited in the body of your report. 7.0 Appendices NOTE: Start this section at the top of a new page. This section of the budget proposal is where you'll attach all of the supporting materials that you've referenced in the preparation of your plan, and that is too detailed or extensive to be included in the body of the report. Use this page to separate the appendices from the text in the body of your report. Make certain that you update the table of contents to include the title of each exhibit in the appendix and its page number. Your Company Name 7.1 Appendix 1: [put a description here] 7.2 Appendix 1: [put a description here] 7.3 Appendix 1: [put a description here]