Memo To: Chief Financial Officer From: Chief Executive Officer Re: 20X2 Profit Planning ABC Company | would like your help creating the budgeted balance sheet and income statement for next Before | 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 20X2 expected sales of our three products to our high-end customer base are: SALES PRODUCTS PRICE UNITS PER UNIT SOLD MODEL A $1,500,000 50 MODEL B $1,125,000 150 MODEL C $750,000 100 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 20X1 consists of the 5% uncollected balance of 20X1 sales. Inventory Assumptions In 20X2 we will continue to outsource the manufacturing of our products and purchase the finished product. Inventory information for 20X2 is: DESIRED BEGINNING ENDING COST PRODUCTS INVENTORY INVENTORY PER UNIT MODEL A 5 units 10 units $1,000,000 MODEL B 5 units 10 units $750,000 MODEL C 5 units 10 units $500,000 The costs listed above apply for both 20X1 and 20X2. 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 Expense Assumptions EXPENSES UTILITIES, MAINTENANCE AND FACILITIES EXECUTIVE AND ADMIN SALARIES DEPRECIATION MARKETING AND PROMOTIONS BAD DEBT EXPENSE SALES COMMISSIONS WARRANTIES SHIPPING AND HANDLING COSTS INSURANCE $850,000 $9,000,000 $2,432,000 10% of Sales 10% of Sales 3% of Sales 3% of Sales 1% of Sales 3% of Purchases All above budgeted expenses except warranties, depreciation and bad debts will be paid in cash during 20X2. Based on historical data, 100% of warranty claims are paid in the year following the sale. The warranty liability on the year-end 20X1 balance sheet is the estimate for 20X1. And, as you know, depreciation and bad debts are non-cash expenses. Other Assumptions The year-end 20X1 balances of Accounts Payable, Accrued Expense and Other Current Liabilities will be paid with cash during 20X2. 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/20X2. Our profits are taxed at a rate of 20% and will be paid in cash during 20X2. If we generate a net loss, assume zero income tax. Please provide an Excel file with the 20X2 budgeted balance sheet and income statement. My goal is to work through several what-if scenarios stemming from the financial statements and another tab with the financial statements that are created by linking to the data sheet. In other words, | 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, | would like a memo that includes 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 | should know to make good business decisions