Answered step by step
Verified Expert Solution
Question
1 Approved Answer
These worksheets must be interactive using the formulas- I am in need of assistance with determining which formulas need to be included in the worksheets.
These worksheets must be interactive using the formulas- I am in need of assistance with determining which formulas need to be included in the worksheets.
Required: Prepare a budgeting spreadsheet that is interactive. It should automatically update when changes are made to the input data, such as changes in sales forecast, equipment purchases, etc. Spreadsheets Hints 1. Create a worksheet for inputs that includes all potential variables that can be changed. Label the worksheet tab as "inputs." 2. Create a worksheet for each of the different budgets. Label the tabs appropriately. The following budgets should be included: a. Sales Budget b. Inventory Purchases Budget c. Selling and Administrative Budget d. Cash Collections from Customers Schedule e. Cash Paid for Inventory Purchases Schedule f. Cash Budget g. Budgeted Income Statement h. Budgeted Balance Sheet 3. Each budget should: a. be on a separate worksheet b. have a heading centered over the rest of the budget that includes the following: i. Name of Company ii. Name of Budget iii. Date: "March 31, 2019" or "For the Quarter ended March 31, 2019" c. be prepared on the monthly basis with a total column for the quarter. The budgeted income statement and budgeted balance sheet should be on the quarter basis (not monthly). Budget Project - Due Sunday, April 23, 2023 Gardner Corporation prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparation of the master budget for the first quarter of 2019 : a. As of December 31, 2018 (the end of the prior quarter), the company's general ledger showed the following account balances: b. Actual sales for December and budgeted sales for the next four months are as follows: c. Sales are 35 percent for cash and the rest on account. All sales on account are collected the month following sale. The accounts receivable at December 31 are a result of December credit sales. d. The company's gross profit rate is 45 percent of sales. e. Monthly expenses are budgeted as follows: salaries and wages, $16,000 per month; property taxes, $8,000 per month; rent, $15,000 per month; utilities, 3 percent of sales; depreciation, $12,000 per month; other expense, 5 percent of sales f. At the end of each month, inventory is to be on hand equal to 40 percent of the following month's sales needs, stated at cost. g. Fifty-five percent of a month's inventory purchases is paid for in the month of purchase; the rest is paid for in the following month. h. The company will purchase a new computer for $12,000 cash in January, a new copier for $20,000 cash in February, and other equipment for $8,000 cash in March. i. The company will declare and pay $10,000 in cash dividends per month. j. The company must maintain a minimum cash balance of $15,000. An open line of credit is available at a local bank for any borrowing that may be needed during the quarter. All borrowing is done at the beginning of a month, and all repayments are made at the end. Borrowings and repayments of principal must be in multiples of $1,000. Interest is paid at the end of each month. The interest rate is 12 percent per annum. (Figure interest on whole months, e.g., 1/12, 2/12.) Required: Prepare a budgeting spreadsheet that is interactive. It should automatically update when changes are made to the input data, such as changes in sales forecast, equipment purchases, etc. Spreadsheets Hints 1. Create a worksheet for inputs that includes all potential variables that can be changed. Label the worksheet tab as "inputs." 2. Create a worksheet for each of the different budgets. Label the tabs appropriately. The following budgets should be included: a. Sales Budget b. Inventory Purchases Budget c. Selling and Administrative Budget d. Cash Collections from Customers Schedule e. Cash Paid for Inventory Purchases Schedule f. Cash Budget g. Budgeted Income Statement h. Budgeted Balance Sheet 3. Each budget should: a. be on a separate worksheet b. have a heading centered over the rest of the budget that includes the following: i. Name of Company ii. Name of Budget iii. Date: "March 31, 2019" or "For the Quarter ended March 31, 2019" c. be prepared on the monthly basis with a total column for the quarter. The budgeted income statement and budgeted balance sheet should be on the quarter basis (not monthly). Budget Project - Due Sunday, April 23, 2023 Gardner Corporation prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparation of the master budget for the first quarter of 2019 : a. As of December 31, 2018 (the end of the prior quarter), the company's general ledger showed the following account balances: b. Actual sales for December and budgeted sales for the next four months are as follows: c. Sales are 35 percent for cash and the rest on account. All sales on account are collected the month following sale. The accounts receivable at December 31 are a result of December credit sales. d. The company's gross profit rate is 45 percent of sales. e. Monthly expenses are budgeted as follows: salaries and wages, $16,000 per month; property taxes, $8,000 per month; rent, $15,000 per month; utilities, 3 percent of sales; depreciation, $12,000 per month; other expense, 5 percent of sales f. At the end of each month, inventory is to be on hand equal to 40 percent of the following month's sales needs, stated at cost. g. Fifty-five percent of a month's inventory purchases is paid for in the month of purchase; the rest is paid for in the following month. h. The company will purchase a new computer for $12,000 cash in January, a new copier for $20,000 cash in February, and other equipment for $8,000 cash in March. i. The company will declare and pay $10,000 in cash dividends per month. j. The company must maintain a minimum cash balance of $15,000. An open line of credit is available at a local bank for any borrowing that may be needed during the quarter. All borrowing is done at the beginning of a month, and all repayments are made at the end. Borrowings and repayments of principal must be in multiples of $1,000. Interest is paid at the end of each month. The interest rate is 12 percent per annum. (Figure interest on whole months, e.g., 1/12, 2/12.)Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started