In business you often perform the same activity on a regular basis. When you are going the same activity many times it is more efficient to set up a worksheet to do the calculations for you. In general, you want the worksheet to have two areas. One area is for input. In this area you will enter the information which will change for each problem/accounting period. The second area performs the required activity. No numbers (unless they are a constant for all instances) are entered in this area. Formulas are used that reference the data in the input area to perform the required calculations.In this project you will be setting up all the budgets, from chapter 09, for a small business that is applying for a loan at a local bank in order to expand.1. Download and save the Excel File for the case 2. Open the file3. At the top of the worksheet you will see a bar that says: Security Warning Macros have been disabled Options4. Click on Enable Content5. A Pop-up box will appear that says Please enter your name Enter your name as requested and click on OK If you do not get the pop-up box please e-mail me and I will send you a file.6. Save the file as an Excel Macro-Enabled Workbook (filename.xlsm)

Background: William "Wrenches" Jones is contemplating opening a second Garage. He has an opportunity to purchase a garage in Schenectady right off the NYS Thruway. He believes this would give him the opportunity to expand sales significantly.Currently, he has a central location in the Capital District and expects that with a new location he could expect a steady trade of independent truck operators that will purchase diesel fuel. He will require them to pay cash when the fuel is purchased or use a credit card for corporate repeat customers.Wrenches has negotiated a price of $165,000 for the new garage. Sandy Eagle on behalf of his bank says they are willing to lend $50,000, based on the stories Wrenches has been telling them about the business. However, Sandy requires that Wrenches prepare an operating budget, a cash budget, and budgeted financial statements that document the claims of growth and the opportunity presented by the additional location.Sandy explained that most budgets are broken into either months or quarters. She suggested that Wrenches prepare a budget on a quarterly basis with an annual summary column. Task One Sales Budget: Additional Background Sandy reminds Wrenches that he will need to plan the gallons of diesel he will sell each quarter for both the original and the proposed new location. Wrenches estimated he would have the following sales in gallons:Original LocationNew LocationFirst Quarter 900,0000 Second Quarter 1,000,0000 Third Quarter 1,200,000, 75,000 Fourth Quarter 1,050,000, 125,000 Over the course of the year he expects that the selling price per gallon of diesel fuel that he will charge will be $0.08 over the purchase price per gallon (purchase price is given in Task two - Purchases Budget).1. Prepare a one-year Sales Budget. Refer to Illustration 9.3 of the text on page 9-8 for an example of a sales budget in Excel (note: Wrenches does not break out sales by cash and credit).1.1. Open the worksheet named Sales Budget1.2. In Rows 1, 2, and 3 insert the following title information: Gasoline Alley GarageSales BudgetFor the year ending December 31, 20201.3. Starting in Cell B5 and going across insert the following Column headings and center the headings

First QuarterSecond QuarterThird QuarterFourth QuarterTotal

1.4. Starting in Cell A6 and going down insert the following Row headings: Gallons Sold in Original Garage, Gallons Sold in New Garage, Total Sales in Gallons, Selling Price per Gallon. A blank row Total Sales in $1.5. Starting in Cell A31 insert the following Row headings:Input SectionMark-up on a gallon of Diesel FuelTotal Gallons to be sold in original garageTotal Gallons to be sold in new garageEnter the sales forecast and the markup per gallon, from the additional background above, in the columns corresponding to the first through fourth quarters.1.6. Starting in cell B6 to cell E7 reference (link to) the appropriate cell in the Input section1.7. Insert a formula in B8 to F8 to calculate total gallons sold, a formula in Cell F6 and a formula in Cell F7 to calculate the total gallons of diesel fuel sold for the year. (Use the sum function, , located in the upper right hand portion of the worksheet)1.8. At this time you should complete steps 2.1 to 2.6 from Task 2, the purchases budget1.9. Insert a formula in Cell B9 to calculate the Selling Price per Gallon (note: you will have to link to the Purchases Budget Input section)1.10.Insert a formula in Cell B11 to calculate the total dollar amount of sales for the quarter. 1.11.Copy the formula in Cell B11 to the appropriate cells in Row 11 for each additional quarter.1.12.Insert a formula in Cell F11 to calculate the total dollar amount of sales for the Year. (Use the sum function, 1.13.Detailed formatting instructions are given for this budget below. All other budgets should be formatted in the same manner.1.14.Select Row 5 in the Sales Budget worksheet1.14.1.Under the Format > Cells > Alignment1.14.1.1.In the Horizontal : select Center1.14.1.2.In the Vertical: select Center1.14.1.3.Check the box for Wrap text. Click OK to close the Dialog Box.

1.15.Select the Cell Range B6:F81.15.1.Under the Format > Cells > Number1.15.2.In the Category section; select Accounting, select 0 Decimal places, and select None for the Currency Symbol. Click OK to close the Dialog Box1.16.Select the Cell Range B9:F91.16.1.Under the Format > Cells > Number1.16.2.In the Category section; select Accounting, select 2 Decimal places, and select None for the Currency Symbol. Click OK to close the Dialog Box1.17.Select the Cell Range B11:F111.17.1.Under the Format > Cells > Number1.17.2.In the Category section; select Accounting, select 0 Decimal places, and select $ for the Currency Symbol. Click OK to close the Dialog Box1.18.Select the Cell Range B32:E321.18.1.Under the Format > Cells > Number1.18.2.In the Category section; select Accounting, select 2 Decimal places, and select None for the Currency Symbol. Click OK to close the Dialog Box1.19.Select the Cell Range B33:E341.19.1.Under the Format > Cells > Number1.19.2.In the Category section; select Accounting, select 0 Decimal places, and select None for the Currency Symbol. Click OK to close the Dialog Box1.20.Use a single Accounting Underline for the cells containing numbers in rows 7 and 91.21.Use a double Accounting Underline for the cells containing numbers in row 111.22.Center the title information in Rows 1, 2, an 3 over the total columns used in the sheet1.22.1.1.Under the Format > Cells > Alignment1.22.1.1.1. In the Horizontal : select Center1.22.1.1.2. Check the box to Merge Cells1.22.1.1.3. Click OK to close the Dialog Box. 1.23.Save the Workbook