ACTG64 FC Cara’s Party Emporium Excel Spreadsheet

Description

4 attachmentsSlide 1 of 4attachment_1attachment_1attachment_2attachment_2attachment_3attachment_3attachment_4attachment_4

Unformatted Attachment Preview

Alphabet Industries
Cash Budget
For the Year Ending December 31, 2027
Quarter #4
Beginning Cash Balance
Add:
Cash Receipts from In-Store Customer Sales
Cash Receipts from Online Customer Sales
Cash Receipts from Consulting Services Rendered
Reduction in Notes Receivable
Interest Received
Sale of Equipment
Total Cash Receipts:
Total Cash Available
Less: Cash Disbursements for Merchandise Purchases
Cash Disbursements for Operating Expenses
Loan Payments (Principal)
Loan Payments (Interest)
Total Cash Disbursements:
Ending Cash Balance:
Quarter #3
Quarter #2
Quarter #1
Alphabet Industries
Purchases Budget
For the Year Ending December 31, 2027
Budgeted Cost of Goods Sold
Add: Desired Ending Inventory
Total Inventory Required
Less: Beginning Inventory
Required Merchandise Purchases
Alphabet Industries
CVP Income Statement
For the Year Ending December 31, 2027
Total
Sales Revenue
Variable Costs
Contribution Margin
Fixed Costs
Net Income
Units Sold:
Per Unit
Cara’s Party Emporium
Cash Budget
For the Year Ending December 31, 2027
Quarter #4
Beginning Cash Balance
Add:
Cash Receipts from In-Store Customer Sales
Cash Receipts from Delivery Sales
Reduction in Accounts Receivable
Interest Received
Sale of Furniture
Total Cash Receipts:
Total Cash Available
Less: Cash Disbursements for Merchandise Purchases
Cash Disbursements for Operating Expenses
Loan Payments (Principal)
Loan Payments (Interest)
Total Cash Disbursements:
Ending Cash Balance:
Quarter #3
Quarter #2
Quarter #1
Cara’s Party Emporium
Purchases Budget
For the Year Ending December 31, 2027
Budgeted Cost of Goods Sold
Add: Desired Ending Inventory
Total Inventory Required
Less: Beginning Inventory
Required Merchandise Purchases
Cara’s Party Emporium
CVP Income Statement
For the Year Ending December 31, 2027
Total
Sales Revenue
Variable Costs
Contribution Margin
Fixed Costs
Net Income
Units Sold:
Per Unit
EA10-R2 Complete Budgets and a CVP Analysis for Cara’s Party Emporium
In this exercise, you will create budgets and perform CVP analyses for Cara’s Party Emporium. You will first create a
cash budget, adjusting settings to allow for efficient data examination. You will then create a purchases budget and
adjust print settings. Last, you will perform CVP analyses using Goal Seek and Solver.
1. Open EA10-R2-Budget from your Chapter 10 folder.
To begin, you will populate the budget with data and formulas.
2. On the Cash Budget tab, enter 8500 in cell J6.
3. Enter the first quarter data:
Cell J8
151000
Cell J9
15000
Cell J10
600
Cell J11
900
Cell J12
©
Cell J13
=SUM (J8:J12)
Cell J15
=J6+J13
Cell J17
116000
Cell J18
5600
Cell J19
3000
Cell J20
700
Cell J21
=SUM (J17:J20)
Cell J23
=J15-J21
4. Enter the second
data:
Cell H6
=J23
Cell H8
141000
Cell H9
11000
Cell H10
820
Cell H11
750
Cell H12
Cell H17
100500
Cell H18
42000
Cell H19
3000
Cell H20
700
In steps 5-8, you will copy the formulas created for Quarter 1 to the appropriate locations in the columns for
Quarters 2-4. Use Ctrl to select the destination cells and paste the formulas to multiple cells at once.
5. Copy cell J13 to cells H13, F13, and D13.
6. Copy cell J15 to cells H15, F15, and D15.
7. Copy the range J21:J23 to the ranges H21:H23, F21:F23, and D21:023.
8. Copy cell H6 to cells F6 and D6
9. Enter the third and fourth quarter data:
Quarter #4
Quarter #3
Cash Receipts from In-Store Customer Sales
125500
165000
Cash Receipts from Delivery Sales
11500
21500
Reduction in Accounts Receivable
200
Interest Received
620
700
Sale of Furniture
2650
Cash Disbursements for Merchandise Purchases
106300
120800
Cash Disbursements for Operating Expenses
44000
59000
Loan Payments (Principal)
Loan Payments Interest)
©
10. Select cell C6 and choose View-Window-Split.
11. Drag the vertical split bar to the left of column A.
12. Scroll so that row 17 is the first visible row in both the top and bottom portions of the split window.
13. In the top portion of the window, type 124000 in cell 017 and note that the change is also made in the
bottom portion of the window.
14. Click Undo on the Quick Access toolbar and then choose View-Window-Split to remove the split.
CHECK FIGURE Cell D23 = $55,640
Adjust Margins, Orientation, and Print Area
15. Choose Page Layout-Page Setup-Adjust Margins-Narrow and then choose Change Page
Orientation Landscape
16. Choose File-Print and review the preview.
17. Switch back to the Cash Budget tab; change the margins to Normal and set the orientation to Portrait.
18. Highlight the range A1:J21 and choose Page Layout-Page Setup–Print Area-Set Print Area.
19. Choose Page Layout-Scale to FitWidth1 Page.
20. Choose FilePrint and in the Settings section choose Portrait Orientation Landscape Orientation.
Apply Print Titles, Headings, and Gridlines
21. Switch back to the Cash Budget tab and choose Page Layout-Scale to Fit-Width -Automatic.
22. Highlight the range A1:A3 and choose Home-Alignment-Merge & Center.
23. Select cell E1 and choose Page Layout-Page Setup-Breaks Insert Page Break. Repeat for cells G1 a
11
24. Replace the contents of cell A3 with: For Quarters Ending in 2027
25. Highlight the range A1:03, choose Home-Alignment-Merge & Center -Merge Across, and then
center-align the content.
26. Choose File-Print and use the Next Page button to review all pages in the preview.
27. Switch back to the Cash Budget tab and choose Page Layout –Page Setup-Print Titles.
28. Click in the Rows to Repeat at Top box and select rows 1-3. Click in the Columns to Repeat at Left box,
click anywhere in column A, and then change the D to B and click OK.
29. Choose File-Print and use the Next Page button to review all pages in the preview.
30. Switch to the Purchases Budget tab; enter 439000 in cell C5 and 22000 in cell C6.
31. In cell C7, type=SUM ( and highlight the range C5:06, and then type ) and tap Enter
32. Enter 17400 in cell C8 and =C7-C8 in cell 09.
33. Select the Purchases Budget and Cash Budget tabs, choose Page Layout-Sheet
Options GridlinesPrint Gridlines, and choose Page Layout-Sheet Options Headings Print
Headings.
Use Goal Seek and Solver
34. Switch to the CVP Analysis #1 tab.
35. Enter 120 in cell F6,97 in cell F7, and 123625 in cell 09.
36. Enter =$B$13*F6 in cell D6; copy that cell, click cell 07, and choose Home-Clipboard–Paste
Formulas & Number Formatting
37. Enter =D6-D7 in cell D8 and then use Paste Formulas to copy that formula to cell F8.
38. Enter =D9/B13 in cell F9 and then choose HomeNumber-Accounting Number Format $ in cell F9.
39. Enter =D8-D9 in cell D10 and then use Paste Formulas to copy that formula to cell F10
40. Select cell D10 and choose Data-Forecast-What-lf Analysis-Goal Seek.
41. Change the To Value box to and click in the By Changing Cell box, select cell B13, and click OK twice.
42. Right click the CVP Analysis #1 tab and choose Move or Copy, choose (move to end), check the box for
Create a Copy, and click OK.
43. Rename the CVP Analysis #1 (2) tab: CVP Analysis #2
44. Choose Data- Forecast-What-If Analysis Goal Seek, ensure that the Set Cell entry is D10, and then set
the To Value to 52000 and enter 313 in By Changing Cell box, click OK twice.
45. Hold Ctrl and drag the CVP Analysis #2 tab to the right to drop a copy of the worksheet to the right of all
tabs and then rename the new tab: CVP Analysis #3
46. Choose Data Analysis-Solver and select cell D10; then click in the By Changing Variable Cells box,
select cell F6 and type a comma, select cell F7 and type a comma, and select cell D9. Click the Add button.
47. Set up four constraints, one at a time, as indicated:
Hint: Click Add to begin a new constraint and click OK after entering the details for each constraint.
Cell Reference
Constraint
Cell D9
121000
Cell F6
125
Cell F7
95
Cell F8
F6*23%
48. Click Solve and then click OK in the Solver Results dialog box.
49. Save and close your file.
EA10-R1 Complete Budgets and a CVP Analysis for Alphabet Industries
In this exercise, you will create budgets and perform CVP analyses for Alphabet Industries. You will first create a cash
budget, adjusting settings to allow for efficient data examination. You will then create a purchases budget and adjust
print settings. Last you will perform CVP analyses using Goal Seek and Solver.
1. Open EA10-R1-Budget from your Chapter 10 folder.
To begin, you will populate the budget with data and formulas.
2. On the Cash Budget tab, enter 5700 in cell J6.
3. Enter the first quarter data:
Cell J8
37200
Cell J9
41300
Cell J10
58400
Cell J11
4100
Cell J12
300
Cell J13
17000
Cell J14
=SUM (J8:J13)
Cell J16
=J6+J14
Cell J18
47200
Cell J19
63400
Cell J20
7200
Cell J21
2100
Cell J22
=SUM (J18:J21)
Cell J24
=J16-J22
4. Enter the second quarter data:
Cell H6
=J24
Cell H8
21800
Cell H9
20600
Cell H10
41900
Cell H11
400
Cell H12
250
Cell H13
©
Cell H18
27300
Cell H19
40000
Cell H20
7200
Cell H21
2100
In steps 5-8, you will copy the formulas created for Quarter 1 to the appropriate locations in the columns for
Quarters 2-4. Use Ctrl to select the destination cells and paste the formulas to multiple cells at once.
5. Copy cell J14 to cells H14, F14, and D14.
6. Copy cell J16 to cells H16, F16, and D16
7. Copy the range J22:J24 to the ranges H22:H24, F22:F24, and D22:D24.
8. Copy cell H6 to cells F6 and D6.
9. Enter the third and fourth quarter data:
Quarter #4
Quarter #3
Cash Receipts from In-Store Customer Sales
31500
17300
Cash Receipts from Online Customer Sales
34800
23000
Cash Receipts from Consulting Services Rendered
17300
29800
Reduction in Accounts Receivable
1600
850
Interest Received
320
270
Sale of Equipment
Cash Disbursements for Merchandise Purchases
41000
31000
Cash Disbursements for Operating Expenses
54600
51300
Loan Payments (Principal)
7200
7200
Loan Payments Interest)
2100
2100
10. If necessary, scroll until cell A1 is visible; then select cell C6 and choose View-Window-Freeze
Panes-Freeze Panes
11. Scroll to the right so only budgetary data for the first quarter is displayed and then scroll down so only cash
disbursements and ending cash balance are displayed.
12. Choose View-Window-Freeze Panes-Unfreeze Panes.
CHECK FIGURE Cell D24 = $12,690
0
Adjust Margins, Orientation, and Print Area
13. Choose FilePrint and in the Settings section choose Normal Margins-Narrow.
14. Switch back to the Cash Budget tab, choose Page Layout Page Setup-Change Page
Orientation Landscape, and then review the print preview in Backstage view.
15. Switch back to the Cash Budget tab, change the margins to Normal, and set the orientation to Portrait.
16. Highlight the range A1:J16 and choose Page Layout Page Setup-Print Area Set Print Area.
17. Choose FilePrint and in the Settings section choose No Scaling-Fit All Columns on One Page and then
choose Portrait Orientation Landscape Orientation
Apply Print Titles, Headings, and Gridlines
18. Switch back to the Cash Budget tab and choose Page Layout-Scale to Fit-Width -Automatic.
19. Highlight the range A1:A3 and choose Home-Alignment-Merge & Center.
20. Select cell E1 and choose Page Layout Page Setup-Breaks Insert Page Break. Repeat for cells G1 and
11.
21. Replace the contents of cell A3 with: For Quarters Ending in 2027
22. Highlight the range A1:D3, choose Home-Alignment-Merge & Center Merge Across, and then
center-align the content.
23. Choose FilePrint and use the Next Page button to review all pages in the preview.
24. Switch back to the Cash Budget tab and choose Page Layout Page Setup-Print Titles.
25. Click in the Rows to Repeat at Top: box and select rows 1-3. Click in the Columns to Repeat at Left box,
click anywhere in column A, and then change the D to B and click OK.
26. Choose File-Print and use the Next Page button to review all pages in the preview.
27. Switch to the Purchases Budget tab, enter 159100 in cell C5 and 18000 in cell C6.
28. In cell c7, type =SUM( and highlight the range C5:C6, and then type ) and tap Enter
29. Enter 30600 in cell C8 and =C7-C8 in cell 09.
30. Select the Purchases Budget and Cash Budget tabs and then choose File-Print.
31. Click the Page Setup link, switch to the Sheet tab, click the box for Gridlines, and click OK; review all pages
in the preview.
Use Goal Seek and Solver
32. Switch to the CVP Analysis #1 tab.
33. Enter 100 in cell F6, 81 in cell F7, and 74290 in cell D9
34. Enter =$B$13*F6 in cell D6; copy that cell, click cell D7, and choose Home-Clipboard-Paste
Formulas & Number Formatting.
35. Enter =D6-D7 in cell D8 and then use Paste Formulas to copy that formula to cell F8.
36. Enter =D9/B13 in cell F9 and choose HomeNumber-Accounting Number Format in cell F9.
37. Enter =D8-D9 in cell D10 and then use Paste Formulas to copy that formula to cell F10.
38. Select cell D10 and choose DataForecast-What-If Analysis-Goal Seek.
39. Change the To Value to and then click in the By Changing Cell box, select cell B13, and click OK twice.
CHECK FIGURE Cell B13 = 3,910
40. Right click the CVP Analysis #1 tab and choose Move or Copy, choose move to end), check the box for
Create a Copy, and click OK.
41. Rename the CVP Analysis #1 (2) tab to: CVP Analysis #2
42. Choose Data-Forecast-What-If Analysis-Goal Seek, ensure that the Set Cell entry is D10, set the To
Value to 21200 and then enter B13 in the By Changing Cell box, click OK twice.
CHECK FIGURE Cell B13 = 5,026
43. Make a copy of the CVP Analysis #2 tab that will appear to the far right of the existing tab; name it: CVP
Analysis #3
Hint: See steps 40-41.
44. Choose Data Analysis Solver and select cell D10; then click in the By Changing Variable Cells box,
select cell F6, type a comma, select cell F7, type a comma, and select cell D9. Click the Add button.
45. Set up four constraints, one at a time, as indicated:
Hint: Click Add to begin a new constraint and click OK after entering the details for each constraint.
Cell Reference
Constraint
Cell D9
73000
Cell F6

Purchase answer to see full
attachment

Tags:
accounting

Cash Budget

Caras Party Emporium

User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.