This is the Excel file to use with different worksheets at the bottom based on the instructions that will follow:
The following is the background information:
Following the background information is the question:
Following this question is the instructions to put onto the excel file found at the top:
A1 X B C D E F G H I J L M N 0 O P Q R 2 3 4 5 6 7 8 9 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Sales Purchases Operating Expenses Labor BudgetedIS CashBudget Segmentis + Ready D + 100% Gasoline Alley Garage 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 Location New Location First Quarter 900,000 0 Second Quarter 1,000,000 0 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 Budget 1.2. In Rows 1, 2, and 3 insert the following title information: Gasoline Alley Garage Sales Budget For the year ending December 31, 2020 1.3. Starting in Cell B5 and going across insert the following Column headings and center the headings First Quarter Second Quarter Third Quarter Fourth Quarter Total 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 Section Mark-up on a gallon of Diesel Fuel Total Gallons to be sold in original garage Total Gallons to be sold in new garage Enter 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 section 1.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, E, 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 budget 1.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, E) 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 worksheet 1.14.1. Under the Format > Cells > Alignment 1.14.1.1. In the Horizontal : select Center 1.14.1.2. In the Vertical: select Center 1.14.1.3. Check the box for Wrap text. Click OK to close the Dialog Box. 1.15.Select the Cell Range B6:F8 1.15.1. Under the Format > Cells > Number 1.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 Box 1.16. Select the Cell Range B9:F9 1.16.1. Under the Format > Cells > Number 1.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 Box 1.17.Select the Cell Range B11:F11 1.17.1. Under the Format > Cells > Number 1.17.2. In the Category section; select Accounting, select O Decimal places, and select $ for the Currency Symbol. Click OK to close the Dialog Box 1.18.Select the Cell Range B32:E32 1.18.1. Under the Format > Cells > Number 1.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 Box 1.19.Select the Cell Range B33:E34 1.19.1. Under the Format > Cells > Number 1.19.2. In the Category section; select Accounting, select O Decimal places, and select None for the Currency Symbol. Click OK to close the Dialog Box 1.20. Use a single Accounting Underline for the cells containing numbers in rows 7 and 9 1.21. Use a double Accounting Underline for the cells containing numbers in row 11 1.22. Center the title information in Rows 1, 2, an 3 over the total columns used in the sheet 1.22.1.1. Under the Format > Cells > Alignment 1.22.1.1.1. In the Horizontal : select Center 1.22.1.1.2. Check the box to Merge Cells 1.22.1.1.3. Click OK to close the Dialog Box. A1 X B C D E F G H I J L M N 0 O P Q R 2 3 4 5 6 7 8 9 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Sales Purchases Operating Expenses Labor BudgetedIS CashBudget Segmentis + Ready D + 100% Gasoline Alley Garage 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 Location New Location First Quarter 900,000 0 Second Quarter 1,000,000 0 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 Budget 1.2. In Rows 1, 2, and 3 insert the following title information: Gasoline Alley Garage Sales Budget For the year ending December 31, 2020 1.3. Starting in Cell B5 and going across insert the following Column headings and center the headings First Quarter Second Quarter Third Quarter Fourth Quarter Total 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 Section Mark-up on a gallon of Diesel Fuel Total Gallons to be sold in original garage Total Gallons to be sold in new garage Enter 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 section 1.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, E, 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 budget 1.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, E) 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 worksheet 1.14.1. Under the Format > Cells > Alignment 1.14.1.1. In the Horizontal : select Center 1.14.1.2. In the Vertical: select Center 1.14.1.3. Check the box for Wrap text. Click OK to close the Dialog Box. 1.15.Select the Cell Range B6:F8 1.15.1. Under the Format > Cells > Number 1.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 Box 1.16. Select the Cell Range B9:F9 1.16.1. Under the Format > Cells > Number 1.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 Box 1.17.Select the Cell Range B11:F11 1.17.1. Under the Format > Cells > Number 1.17.2. In the Category section; select Accounting, select O Decimal places, and select $ for the Currency Symbol. Click OK to close the Dialog Box 1.18.Select the Cell Range B32:E32 1.18.1. Under the Format > Cells > Number 1.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 Box 1.19.Select the Cell Range B33:E34 1.19.1. Under the Format > Cells > Number 1.19.2. In the Category section; select Accounting, select O Decimal places, and select None for the Currency Symbol. Click OK to close the Dialog Box 1.20. Use a single Accounting Underline for the cells containing numbers in rows 7 and 9 1.21. Use a double Accounting Underline for the cells containing numbers in row 11 1.22. Center the title information in Rows 1, 2, an 3 over the total columns used in the sheet 1.22.1.1. Under the Format > Cells > Alignment 1.22.1.1.1. In the Horizontal : select Center 1.22.1.1.2. Check the box to Merge Cells 1.22.1.1.3. Click OK to close the Dialog Box
