Category: Business Plans

Instructions:

This assignment requires you to evaluate a cash budget for a startup bakery on Georgia Hwy 400 near Dawsonville, GA which is located near the mountains in Georgia. You will need to have a basic understanding of MS Excel, but nothing advanced. You will be doing what is called sensitivity or “what if” analysis. The Workbook is set up so you literally do not have to do any math calculations. The spreadsheet has formulas built into Cells that require a calculation (e.g., monthly sales for January are shown in B6). That calculation is based on information from the Assumptions Worksheet.

You are given an MS Excel file which is called a workbook. A workbook contains worksheets. Think of worksheets as pages in a MS Word document.

This assignment takes advantage of cell references in MS Excel. In a formula on one worksheet, I can reference a cell from another worksheet rather than re-typing the number from that cell or typing a different number. By doing that, the calculation will change if I change the value in the referenced cell in the other worksheet. The referenced values are on the Assumptions Worksheet. They are the initial values the entrepreneur has used to create an initial (draft) forecast sales. They are assumed values. That is why they are on the Assumptions Worksheet.

As an example, the formula for Sales in January in the ProForma Cash Budget Worksheet is as follows: =

B3*(1+Assumptions!B30), where B3 is the value of Sales in January and Assumptions!B30 is the assumed monthly growth rate for sales. If I change the value (number) in Assumptions!B30 from the Assumptions worksheet, then the formula in the ProForma Cash Budget Worksheet will automatically do the calculations an change the number in Cell B3 on the ProForma Worksheet.

SCENARIO: This assignment asks you to help your friend Ashley. Ashley makes incredible baked goods. While she was in college she would bake cookies, cakes, and other baked items and take them to parties. Since college, she has baked them and sold them at fairs and festivals for three years. She has decided to start a new Bakery in Dawsonville, GA because the area has experienced growth. Dawsonville is a small town, but it has had growth near the intersection of GA Hwy 53 and GA Hwy 400, a four-lane highway that intersects I-85 and I-285 in Atlanta.

After creating her pro forma cash budget (her initial forecast), Ashley is disappointed that the net income for the first year is less than $19,000 (Cell O35 in the Worksheet “ProForma”). Help Ashley out. She really needs to earn at least $35,000 in the first year because she has personal expenses that cannot be reduced anymore (e.g., rent, car payment, etc.). For example, she also has to buy her own health insurance because she is no longer eligible for her parents’ health insurance policy (she is 27). She will be the only owner of the business and the bakery will be her only source of income.

Ashley doesn’t have a degree in business, but thinks the business will succeed based on consultations with both a Mentor at a SCORE Chapter and a consultant with the University of Georgia’s Small Business Development Center located in nearby Gainesville, GA. Unfortunately, her proforma cash budget does not forecast a very good annual, net cash flow. Your job is to help her make reasonable changes to her forecast (proforma cash budget) to help her improve the forecasted profitability of the bakery. To complete the assignment you will need to answer four questions on the last worksheet (Questions). You will need to reference the pro forma cash budget to answer the questions. Remember that the pro forma cash budget is set up so that you dont have to do any math on your own. However, you do have to show your changes will help Ashley achieve Sales greater than $35,000.

Please ignore things like depreciation and interest expense because we are focusing on cash flow which is literally the flow of money through the business. Be careful that what you propose creates a ripple affect that you do not account for. Several of the monthly costs are fixed in the short run (e.g., rent, loan, etc.). By fixed I mean you can’t change them anytime soon. So, dont recommend doing that. For example, do not mess with Cost of Goods Sold because it is based upon industry averages. Ashley cant change the price she pays for things like flour, sugar, milk, and eggs. Her rent and utility bills are not flexible. Your answer to EACH question (#1 – #3) must raise Ashley’s net cash flow from $19,000 to something larger than $35,000.

Use the attached MS Excel Workbook to complete the assignment. Submit the assignment using MS Excel. The Questions worksheet has the four questions you must answer for this assignment. However, you need to review ALL of the other four worksheets to answer the 4 questions in the assignment. The Assumptions worksheet will be especially important. It will be the basis for the changes you recommend to help Ashley improve cash flow in the first year of business. You don’t need to get too creative. There is sufficient information in the Assumptions Worksheet to make at least three different recommended changes. IMPORTANT: Each recommended change needs to be distinctly different from the other one. The first three questions are independent of one another. So, again, EACH of them must help Ashley reach $35,000 in net cash flow.

HINT: Assumptions are just that. They are a “guess” into the future. They describe the world as Ashley sees it when the business is actually opened. Right now, her forecast (Pro forma cash budget) doesn’t look promising. However, she has never created a forecast and might have been a little too inexperienced to do so very well. She also thinks too much like the college student making baked goods as a hobby for friends rather than as a business woman selling baked goods to eager and willing customers.

Please thoroughly read each question before you provide a response. You need to show the math to demonstrate that each recommendation will help Ashley reach her goal: Net Cash Flow of $35,000 in Year 1. However, remember that Excel will do all of the work for you.

Save the attached MS Excel spreadsheet as follows: LastName_Bakery.xlsx before submitting it. Do not submit anything other than an Excel spreadsheet.

COMMENTS:

1. This assignment is easy if you take your time and read the instructions WHILE you look at the worksheets.
2. Do not overthink it. There are quite a number of ways Ashley can increase her Net Cash Flow to $35,000 or more.