Comprehensive Excel Budgeting Assignment ACCT 310 You are the controller for Lakes Repair an auto repair shop currently operating in Baxter with 9 mechanics. Lakes Repair is planning to open a second shop on Jan 2 in Little Falls, MN. It is currently November 10th, and you have to develop the budgets and the cashflow forecast for the store for the first 12 months of operation. Although the new shop will be smaller than the Baxter facility, it will offer basically the same services, so you can use much of your data on the Baxter location to help you project Little Falls costs and revenues. Lakes Repair has decided to open the new location with 1 service manager, 4 mechanics and 1 office manager/accounting assistant. By July, the company intends to add 1 more mechanic, bringing the total number of mechanic technicians up to 5. You have negotiated a loan with 3rd National Bank for $350,000 to fund all start-up inventory, equipment, furniture and fixtures. This loan has a 5-year term, and is to be repaid in equal monthly installments. You have also obtained a bank line of credit for $50,000 to help the shop stay solvent during the slower start-up period. Assignments: Since you will need to show monthly data for 12 months set up your spreadsheet in landscape format. You may use table formatting for this. Do not show all cell boundaries only show cell borders inside each budget spreadsheet. Show each budget on a different worksheet within one workbook and label your worksheets tabs accordingly. Do not use the $ sign symbol on any cell within the workbook. All worksheet cells need to be linked to the key data sheet [Example: =('Key Data'!$B$3*'Key Data'!$B$2)*'Key Data'!$B$4*'Sales Budget'!B5] . (Note: any cells that have a hard-keyed number in will result in a reduction of points.) 1. Key Data: Set up a Key Data spreadsheet. Set-up and label cells to record the following information: (You will fill in this information as you develop the budgets) Shop Rate per hour Billable Hours per Mechanic per Day Technician days worked per month Technician days paid per month Parts Sales as % of Labor Sales Cost of Goods Sold % - Parts Mechanic Wage Rate Office Manager Salary Service Manager Salary Social Security Tax Rate Unemployment Tax Rate Work Comp Rate Mechanics Work Comp Rate Office & Mgr Supplies % of Sales Credit Card Discount Cost Cash Sales % of Total Sales Credit Card Sales % of Total Sales A/R Sales % of Total Sales Payments Term Loan Interest Rate Credit Line 2. Sales Budget: Sales are in 2 areas Parts and Labor. Labor Sales: The normal capacity of the shop is for each mechanic to generate 8 hours per day of billable labor hours. (NOTE: because auto repair shops bill on book time the average amount of time a repair should take - an experienced mechanic could bill out more than 8 hours of labor in an 8-hour day, but we are using 8 hours in order to be conservative in our projections.) Experience at the Baxter store has shown that mechanics work an average of 20 days per month after subtracting weekends, vacation days and holidays. Lakes Repair bills labor at a rate of $60 per hour. Parts Sales: An analysis of Baxter store work tickets has shown that parts sales tend to average about 120% of Labor Sales. In other words, if labor on a job ticket was $100, the parts would be $120. Ramp-Up: You realize that a brand new shop is not likely to start out with enough customers to be busy 100% of the time in the first few months of operation. After talking at trade association meetings to other shop owners who opened new operations in other towns, you have decided to assume that the shop will start up operating at 50% of capacity in January, working up 10% each month until reaching 100% of normal capacity in June. November has typically been a very slow month also, so you will budget sales at only 80% of capacity in that month also. Create a monthly Sales Budget for the whole year in the following format: Month Jan Feb Mar April Total for Year # of Mechanics Labor Sales max capacity Parts Sales max capacity Total Sales Capacity Ramp up % Budgeted Labor Sales Budgeted Parts Sales Budgeted Total Sales 42,240 NOTE: Set your cells to round all Sales figures to the nearest dollar omit cents. 3. Purchases Budget: Lakes Repair will have a base level of inventory purchased prior to opening on January 2, so this plan assumes that in each month you will only have to purchase parts to replace those sold. Cost of Goods Sold on parts has generally run 70% of actual Parts Sales in the Baxter store so regular parts purchases in each month can be budgeted at this amount. The only additional purchase will come in July, when parts inventory will be increased by $5,000 in preparation for starting the new 5th mechanic. Prepare a Purchases Budget using the following format: Month Jan Feb Mar April Total for Year Regular Parts Purchases 1-Time Inventory Increase Total Parts Purchases 16,128 4. Labor Budgets Shop Labor Budget: Management desires to get a fully loaded labor cost, with all taxes and work comp insurance included. Prepare a budget in the following format set up a simple formula to bring down the # of mechanics from the Sales Budget above. Although mechanics generally only work 20 days per month (as used in the sales budget), they get paid for an average of 22 days per month at 8 hours per day, once paid time off for holidays, sick days, etc. are factored in. Mechanic wages will average $15 per hour. All taxes should be calculated using the following tax rates (key these into your key figures) Social Security Tax: 7.65% (Employers Share) Unemployment Tax: 1.5% Work Comp Mechanics: $8 per $100 of wages, or 8% of wages Month Jan Feb Mar April Total for Year Number of Mechanics Shop Labor Cost Mechanic Wages Gross Social Security Tax Mechanics Unemployment Tax Mechanics Work Comp Insur. Mechanics Total Shop Labor Cost 12,371 Office/Mgmt Labor Budget: The office manager will be paid a salary of $2200 per month, and the service manager will be paid $3000 per month. The social security and unemployment taxes for office/mgmt are the same as for mechanics, but the work comp insurance rate is at only 2% of gross wages ($2 per $100 of wages) due to the much lower injury rate. Set up the Office/Mgmt Labor Budget as shown on the following page: Month Jan Feb Mar April Total for Year Office/Mgmt Labor Cost Service Manager Salary Office Manager Salary Social Security Tax Office/Mgmt Unemployment Tax Office/Mgmt Work Comp Insur. Office/Mgmt Total Office/Mgmt Labor Cost 5,780 5. Cashflow Projection (Cash Budget) Use the information from the sales budget and purchases budget, along with the other information presented on the following page, to prepare a full cashflow projection using the following format: Beginning Cash: The initial investment by the owners will result in a $15,000 beginning cash balance. Collections from Sales: Using the information from the Sales Revenue Budget, project cash collections from sales. Sales are 50% cash, 40% credit card, and 10% on account. Credit Card sales have a 2.5% discount, so only 97.5% is received and the cash is received in the same month as the sale occurs. Sales on account are subject to n/30 credit terms, so assume all sales on account are received in the month following the sale. (Will there be any collections on A/R sales in January?) A/P Payments Parts Purchases: Use the information from the Purchases Budget assume that payment terms are n/30. Shop Labor Cost and Office/Mgmt Labor Cost: From the labor budget assume all labor costs are paid out in the month incurred. Shop Supplies: Assume Shop supplies are 10% of Budgeted Total Sales. Input a line in the key data section Shop Supplies as % of Sales and use this in your formula. Office Supplies: Assume flat $250 per month. Advertising: Mgmt wants to budget $5000 of month 1 advertising for a grand opening, then $2500 per month for months 2 & 3, and then $1000 per month thereafter. Insurance: Lakes Repairs lease requires Lakes Repair to carry the insurance on the building. A quote from the insurance company puts regular liability & fire insurance at $24,000 per year the bill is to be paid in equal monthly installments. Utilities: The Baxter shop utility bills average $1000 per month for heat, electricity and phone. The Little Falls shop is smaller, so it is expected to average 85% of the Baxter shop cost. Accounting and Legal: The firm retains an attorney and CPA for a monthly fee. Addition of services for the Little Falls store is expected to add $200 per month to this cost. Rent: $3500 per month. Vehicle Expense: Lakes Repair has purchased a truck to run daily parts and paperwork between the Baxter and Little Falls stores. The truck insurance of $200 per month is also charged to this account. This truck costs $0.40 per mile to run, and will also be used to do local part pickups, and to pick-up and drop off customers who leave their cars for repairs. Management estimates the following usage: - 20 round-trips per month from Little Falls to Baxter @ 70 miles per trip. - 600 miles per month of local trips and miscellaneous parts runs. NOTE: PLEASE SHOW YOUR CALCULATION OF THE MILEAGE AMOUNT IN A SEPARATE MINISPREAD BELOW THE CASH BUDGET Dues and Subscriptions: $50 per month for automotive association dues, magazines for waiting room. Computer System Lease: Lakes Repair is leasing a nice integrated computer system for the office and technicians to use. The system tracks perpetual inventory and allows technicians to record their service using touch screen computers on the shop floor. The full system of hardware and software is being leased under a capital lease for $1500 per month over 5 years. After 5 years, Lakes Repair will own the system. Management Fee: The Lakes Repair corporate office is assessing a $5000 per month charge to the Little Falls store to cover corporate management, accounting, HR and other related services. This also allows corporate to bring back some of the profit of the Little Falls store. Payments Term Loan: Use Excel to calculate the monthly payments on the $350,000 start-up loan. The loan is to be repaid over 5 years (60 equal monthly payments) and carries an interest rate of 6% per year. Add lines in the Key Data section for Loan Amount, Loan Term, Loan Interest Rate and Payments Term Loan. In the cell where you want the payment to appear, do the following: - Input the loan amount, loan term in months and the annual interest rate. - Click on the Formula tab on the top menu - Under Function Library choose Financial - From the drop down list, choose PMT (calculates payments on a normal loan) - In Rate reference the input from above and divide by 12 to get the monthly interest rate (Hint: annual int rate is for 12 months) - Nper is the number of payments for the loan. Reference that amount from the input above. - PV is the present value of the loan for a straight loan this is simply the original principal balance, which also should be referenced from above. NOTE: Once you have created the function to get the loan payment, go to cashflow and pull down the loan payment using a cell reference. If you are entering all of your cash disbursements as positive numbers, you will need to change the negative loan payment into a positive number. This can be done by simply putting a minus (-) sign before the cell address. For instance, if the loan payment function is in cell C25, the formula to bring the payment into the cashflow would be =C25. CREDIT LINE: This involves the following lines: Interest Payments Credit Line, Borrowing Credit Line, Principal Repayment Credit Line and Line of Credit Balance: - Interest Payments Credit Line: Create a Key Data line labeled Interest Rate Credit Line. The credit line carries a rate of 8% annual interest. Interest payments Credit Line in the cashflow will be equal to the line of credit balance from the previous month times the rate from the credit line times 1/12 for time. (NOTE: There will be no credit line interest in month 1 since interest is always on the previous month.) - Borrowing Credit Line: The company wants a minimum Ending Cash Balance of $10,000. In each month where Cash Balance Before Financing falls below this amount, borrow enough $$ needed on the credit line. Manually key in borrowing as needed in $1000 increments. - Principal Repayment Credit Line: Assume principal is repaid only in increments of $5,000 ($5000, $10,000, $15,000, etc.) when the company has sufficient cash. In the last principal repayment, however, you can repay the entire amount owed. - Line of Credit Balance: In the first month, the Line of Credit Balance will simply be set up to equal the $8,000 amount borrowed in month 1. In subsequent months, you will need a formula to calculate the new balance. The formula will need to take the previous month Line of Credit Balance + Borrowing in the current month Principal Repayment in the current month. Miscellaneous Expenses: Budget $2000 in month 1, then $200 per month thereafter. II. PRO-FORMA FINANCIAL STATEMENTS: After you have completed the budgets, then complete a set of pro-forma financial statements within the same workbook. Label the worksheets accordingly. You will use proper form to classify accounts into short and long term. Use a classified balance sheet approach in report form. Note: You will need to create an amortization schedule for the equipment loan. You will need to create an amortization schedule for the computer lease. (The loan amount will be the present value of the minimum lease payments. Use 7.25% interest rate; 5 year term; payments are $1,500 per month). Classify the lease as other long-term assets. You will need to calculate depreciation on the assets. Use Straight-line depreciation over the life of the asset. The loan and computer lease should be classified as current portion of long-term debt and long-term debt for principal payments