please help us understand why our budgeted financials are not balancing out.
I would like your help creating the budgeted balance sheet and income statement for next year. I am exploring expanding to new locations in an effort to increase profit margins. Before I look at the impact of these different scenarios, I'd like to start with the basics. Could you please put together the budgeted financial statements with the following assumptions for next year? Attached is an Excel file with the financial statements for the year just ended to use as a starting point. Sales Assumptions 202 expected sales of our three products to our high-end customer base are: Based on historical data, 85% of sales revenue is collected in the year of the sale with 5% collected in the year following the sale. 10% of sales is estimated as bad debt. As you know, we make this estimate at year end and record the expense in the year of sale. The net receivables balance at year-end 201 consists of the 5% uncollected balance of 20X1 sales. In 202 we will continue to outsource the manufacturing of our products and purchase the finished product. Inventory information for 202 is: The costs listed above apply for both 201 and 202. The current sales prices are based on cost plus 50% markup. Based on historical data, 85% of inventory is paid for in the year of purchase with the remaining 15% paid for in the year following the purchase. Our accounts payable account is used specifically for inventory purchases. Selling. General and Administrative Expensa Ascumntinne All above budgeted expenses except warranties, depreciation and bad debts will be paid In cash during 202. Based on historical data, 100% of warranty claims are paid in the year following the sale. The warranty liability on the year-end 201 balance sheet is the eatimate for 20X1. And, as you inow, deprecialion and bad debts are non-cash expenses. The year-end 201 balances of Accounts Payable, Accrued Expense and Other Current Liabilities will be paid with cash during 202. On January 1,5% of the long-term debt balance will be paid off. At year end, interest on long-term debt is accrued at an annual rate of 12% but is not paid until the following year. We're budgeting an annual 3% return on short-term investments and an annual 7% return on long-term investments; both are expected in cash at year-end. We expect to pay out dividends of 15% of net income on 12/31/202. Our profits are taxed at a rate of 20% and will be paid in cash during 202. If we generate a net loss, assume zero income tax. Please provide an Excel file with the 202 budgeted balance sheet and income statement. My goal is to work through several what-if scenarios stemming from the financial statements you create. In order to do that, please make one tab a data sheet with all the 'hard' numbers and another tab with the financial statements that are created by linking to the data sheet. In other words, I want to make changes to the numbers in the data sheet and have the financial statements automatically change accordingly. In the attached Excel file, I've given you a structure that you may choose to use. In addition to creating the Excel file, I would like a memo with a list of the critical financial information and your analysis of our financial situation. First, give me a quick summary of the following information: net income, cost of goods sold, total assets, cash balance, gross margin ratio, operating income ratio, debt-to-equity ratio, current ratio, inventory turnover, and net profit ratio. Second, analyze this information to help me understand the big picture. Are we profitable? Why or why not? Discuss operating income versus net income. Basically, anything you think 1 should know to make good business decisions. The year-end 201 balances of Accounts Payable, Accrued Expense and Other Current Liabilities will be paid with cash during 202. On January 1,5% of the long-term debt balance will be paid off. At year end, interest on long-term debt is accrued at an annual rate of 12% but is not paid until the following year. We're budgeting an annual 3% return on short-term investments and an annual 7% return on long-term investments; both are expected in cash at year-end. We expect to pay out dividends of 15% of net income on 12/31/202. Our profits are taxed at a rate of 20% and will be paid in cash during 202. If we generate a net loss, assume zero income tax. Please provide an Excel file with the 202 budgeted balance sheet and income statement. My goal is to work through several what-if scenarios stemming from the financial statements you create. In order to do that, please make one tab a data sheet with all the 'hard' numbers and another tab with the financial statements that are created by linking to the data sheet. In other words, I want to make changes to the numbers in the data sheet and have the financial statements automatically change accordingly. In the attached Excel file, I've given you a structure that you may choose to use. In addition to creating the Excel file, I would like a memo with a list of the critical financial information and your analysis of our financial situation. First, give me a quick summary of the following information: net income, cost of goods sold, total assets, cash balance, gross margin ratio, operating income ratio, debt-to-equity ratio, current ratio, inventory turnover, and net profit ratio. Second, analyze this information to help me understand the big picture. Are we profitable? Why or why not? Discuss operating income versus net income. Basically, anything you think 1 should know to make good business decisions. Sheet View Default 13 fx Keep . Exit \& New A B C D E F 6 Intermediate Budgets and Schedules ALL CELLS WILLUNK TO OTHERTABS OR WILL HAVE FORMULASI Schedule of Cash Receipts from Investments Interest recelved from short-term investm Interest recelved from long-term invest Total cash collections on investments ALL CELLS WILL LINK TO OTHER TABS OR WILL HAVE FORMULAS! ABC Company Budgeted Cost of Goods Sold Schedule Year ending 12/31/20X2 \begin{tabular}{l|r|r|} \hline Beginning Inventory & 15 \\ \hline Add Purchases & 315 \\ Cost of goods available for sale & $ & 246,250,000 \\ Less ending inventory & & 30 \\ \hline Cost of goods sold & $ & 223,750,000 \\ \hline \end{tabular} ABC Company Budgeted Statement of Retained Earnings Year ending 12/31/20X2 I would like your help creating the budgeted balance sheet and income statement for next year. I am exploring expanding to new locations in an effort to increase profit margins. Before I look at the impact of these different scenarios, I'd like to start with the basics. Could you please put together the budgeted financial statements with the following assumptions for next year? Attached is an Excel file with the financial statements for the year just ended to use as a starting point. Sales Assumptions 202 expected sales of our three products to our high-end customer base are: Based on historical data, 85% of sales revenue is collected in the year of the sale with 5% collected in the year following the sale. 10% of sales is estimated as bad debt. As you know, we make this estimate at year end and record the expense in the year of sale. The net receivables balance at year-end 201 consists of the 5% uncollected balance of 20X1 sales. In 202 we will continue to outsource the manufacturing of our products and purchase the finished product. Inventory information for 202 is: The costs listed above apply for both 201 and 202. The current sales prices are based on cost plus 50% markup. Based on historical data, 85% of inventory is paid for in the year of purchase with the remaining 15% paid for in the year following the purchase. Our accounts payable account is used specifically for inventory purchases. Selling. General and Administrative Expensa Ascumntinne All above budgeted expenses except warranties, depreciation and bad debts will be paid In cash during 202. Based on historical data, 100% of warranty claims are paid in the year following the sale. The warranty liability on the year-end 201 balance sheet is the eatimate for 20X1. And, as you inow, deprecialion and bad debts are non-cash expenses. The year-end 201 balances of Accounts Payable, Accrued Expense and Other Current Liabilities will be paid with cash during 202. On January 1,5% of the long-term debt balance will be paid off. At year end, interest on long-term debt is accrued at an annual rate of 12% but is not paid until the following year. We're budgeting an annual 3% return on short-term investments and an annual 7% return on long-term investments; both are expected in cash at year-end. We expect to pay out dividends of 15% of net income on 12/31/202. Our profits are taxed at a rate of 20% and will be paid in cash during 202. If we generate a net loss, assume zero income tax. Please provide an Excel file with the 202 budgeted balance sheet and income statement. My goal is to work through several what-if scenarios stemming from the financial statements you create. In order to do that, please make one tab a data sheet with all the 'hard' numbers and another tab with the financial statements that are created by linking to the data sheet. In other words, I want to make changes to the numbers in the data sheet and have the financial statements automatically change accordingly. In the attached Excel file, I've given you a structure that you may choose to use. In addition to creating the Excel file, I would like a memo with a list of the critical financial information and your analysis of our financial situation. First, give me a quick summary of the following information: net income, cost of goods sold, total assets, cash balance, gross margin ratio, operating income ratio, debt-to-equity ratio, current ratio, inventory turnover, and net profit ratio. Second, analyze this information to help me understand the big picture. Are we profitable? Why or why not? Discuss operating income versus net income. Basically, anything you think 1 should know to make good business decisions. The year-end 201 balances of Accounts Payable, Accrued Expense and Other Current Liabilities will be paid with cash during 202. On January 1,5% of the long-term debt balance will be paid off. At year end, interest on long-term debt is accrued at an annual rate of 12% but is not paid until the following year. We're budgeting an annual 3% return on short-term investments and an annual 7% return on long-term investments; both are expected in cash at year-end. We expect to pay out dividends of 15% of net income on 12/31/202. Our profits are taxed at a rate of 20% and will be paid in cash during 202. If we generate a net loss, assume zero income tax. Please provide an Excel file with the 202 budgeted balance sheet and income statement. My goal is to work through several what-if scenarios stemming from the financial statements you create. In order to do that, please make one tab a data sheet with all the 'hard' numbers and another tab with the financial statements that are created by linking to the data sheet. In other words, I want to make changes to the numbers in the data sheet and have the financial statements automatically change accordingly. In the attached Excel file, I've given you a structure that you may choose to use. In addition to creating the Excel file, I would like a memo with a list of the critical financial information and your analysis of our financial situation. First, give me a quick summary of the following information: net income, cost of goods sold, total assets, cash balance, gross margin ratio, operating income ratio, debt-to-equity ratio, current ratio, inventory turnover, and net profit ratio. Second, analyze this information to help me understand the big picture. Are we profitable? Why or why not? Discuss operating income versus net income. Basically, anything you think 1 should know to make good business decisions. Sheet View Default 13 fx Keep . Exit \& New A B C D E F 6 Intermediate Budgets and Schedules ALL CELLS WILLUNK TO OTHERTABS OR WILL HAVE FORMULASI Schedule of Cash Receipts from Investments Interest recelved from short-term investm Interest recelved from long-term invest Total cash collections on investments ALL CELLS WILL LINK TO OTHER TABS OR WILL HAVE FORMULAS! ABC Company Budgeted Cost of Goods Sold Schedule Year ending 12/31/20X2 \begin{tabular}{l|r|r|} \hline Beginning Inventory & 15 \\ \hline Add Purchases & 315 \\ Cost of goods available for sale & $ & 246,250,000 \\ Less ending inventory & & 30 \\ \hline Cost of goods sold & $ & 223,750,000 \\ \hline \end{tabular} ABC Company Budgeted Statement of Retained Earnings Year ending 12/31/20X2
