EA$10-$E$2$ PREPARE BUDGETS AND A CVP ANALYSIS FOR KPF INDUSTRIES

In this exercise, you will prepare cash and purchases budgets and CVP analyses for KPF Industries. You will first create a cash budget, adjusting its appearance to facilitate data examination. You will then create a purchases budget and adjust print settings to ensure readability and ease of use. Last, you will use Goal Seek and Solver to create three CVP analyses.

Open EA$10-$E$2-$Budget. On the Cash Budget tab, create a cash budget based on a beginning cash balance of $$9,400$ and this data:

Quarter #$4$

Quarter #$3$

Quarter #$2$

Quarter #$1$

Cash Receipts from In$-$Store Customer Sales

$$23,100$

$$28,600$

$$37,200$

$$31,300$

Cash Receipts from Online Sales

$$3,700$

$$6,300$

$$5,100$

$$4,200$

Cash Receipts from Services Rendered

$$1,550$

$$1,300$

$$1,200$

$$1,300$

Sale of Fixed Assets

$$850$

$$0$

$$0$

$$0$

Cash Disbursements for Merchandise Purchases

$$13,500$

$$21,600$

$$22,700$

$$23,200$

Cash Disbursements for Operating Expenses

$$15,000$

$$16,200$

$$18,500$

$$18,000$

Loan Payments $($Principal$)$

$$0$

$$1,500$

$$1,500$

$$1,500$

Loan Payments $($Interest$)$

$$0$

$$320$

$$320$

$$320$

Also enter the necessary formulas. Set up the budget to print with wide margins and in Landscape orientation. Scale the printed worksheet so all columns fit on a single page. Set a print area to include only the cash data through row $14.$ Create a horizontal split that displays$$the itemized cash receipts directly above the itemized cash disbursements $($only the split bar between them$).$

On the Purchases Budget tab, create a purchases budget based on this data:

Budgeted Cost of Goods Sold

$$80,000$

Desired Ending Inventory

$$7,500$

Beginning Inventory

$$6,500$

Also enter the necessary formulas. Simultaneously adjust the cash budget and purchases budget so they display headings and gridlines when printed.

On the CVP Analysis #$1$ tab, create a CVP analysis based on this data:

Sales Revenue

$$100$ per unit

Variable Costs

$$77$ per unit

Fixed Costs

$$32,200$

Also enter the necessary formulas but leave the Units Sold cell blank. Use Goal Seek to determine the breakeven number of units. Create a second CVP analysis tab and use Goal Seek to determine the required unit sales to achieve a net income of $$11,300.$ Create a third CVP analysis tab that assumes unit sales equal to those calculated in the second CVP analysis. Assume that variable costs per unit cannot change, sales revenue per unit cannot exceed $$103,$ fixed costs cannot fall below $$31,500,$ and contribution margin as a percentage of sales revenue cannot exceed $25\%.$ Name each CVP analysis tab appropriately. Last, for each CVP analysis, freeze the three$-$line header at the top.