Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave
Question:

You have agreed to do the following for Luke:

  • Prepare the income statements using a spreadsheet program, with data he will provide. A chart must also be produced in the spreadsheet as detailed below.
  • Prepare a short business report using a word processor program, with answers to specific questions as detailed below. Luke wants to use it to show the plan and his projections to his business partners and employees.

Write a paragraph explaining to Luke Rendalls how he can use your spreadsheet to do his own “what if” analyses or scenarios. Your explanation should be as clear as possible, in language suitable for someone who is not familiar with spreadsheets, to help them understand this feature of spreadsheeting.

Write another paragraph briefly explaining the “absolute referencing” feature – be sure to mention when it is used and why.

Answer:
Introduction

The report aims to depict the changes in the income as per the various scenarios in Maxi’s Food Mart. The main changes have been considered with the variations in the net income as per the growth in sales, Mr. Rendall salary, hourly wage and hiring additional employees. The study has also compared the items for sales in 2017 by creating a pie chart compared to the different items held for sale in 2017.

 
Discussion
Worksheet A

Maxi's Food Mart

Pro Forma Income Statement

 

 

Prepared on:

February 1, 2018

 

 

 

 

2017

2018

2019

2020

Sales

 

 

 

 

 

Deli

 

$240,000.00

$257,400.00

$277,348.50

$300,229.75

Dairy

 

$720,000.00

$772,200.00

$832,045.50

$900,689.25

Canned Goods

 

$480,000.00

$514,800.00

$554,697.00

$600,459.50

Frozen Foods

 

$800,000.00

$858,000.00

$924,495.00

$1,000,765.84

Meats

 

$840,000.00

$900,900.00

$970,719.75

$1,050,804.13

Produce

 

$500,000.00

$536,250.00

$577,809.38

$625,478.65

Dry Goods

 

$360,000.00

$386,100.00

$416,022.75

$450,344.63

Gift Card

 

$60,000.00

$64,350.00

$69,337.13

$75,057.44

Total Sales

 

$4,000,000.00

$4,290,000.00

$4,622,475.00

$5,003,829.19

Cost of Goods Sold (CGS)

 

 

 

 

 

Deli

 

$2,000,000.00

$2,145,000.00

$2,311,237.50

$2,501,914.59

Dairy

 

$2,000,000.00

$2,145,000.00

$2,311,237.50

$2,501,914.59

Canned Goods

 

$3,000,000.00

$3,217,500.00

$3,466,856.25

$3,752,871.89

Frozen Foods

 

$2,600,000.00

$2,788,500.00

$3,004,608.75

$3,252,488.97

Meats

 

$2,000,000.00

$2,145,000.00

$2,311,237.50

$2,501,914.59

Produce

 

$2,600,000.00

$2,788,500.00

$3,004,608.75

$3,252,488.97

Dry Goods

 

$2,640,000.00

$2,831,400.00

$3,050,833.50

$3,302,527.26

Gift Card

 

$1,200,000.00

$1,287,000.00

$1,386,742.50

$1,501,148.76

Total Cost of Goods Sold (CGS)

 

$18,040,000.00

$19,347,900.00

$20,847,362.25

$22,567,269.64

Gross Profit

 

$14,040,000.00

$15,057,900.00

$16,224,887.25

$17,563,440.45

Operating Expenses

 

 

 

 

 

Sales & Marketing

 

$220,000.00

$235,950.00

$254,236.13

$275,210.61

General & Administrative

 

$350,000.00

$375,375.00

$404,466.56

$437,835.05

Depreciation

 

$20,000.00

$20,000.00

$20,000.00

$20,000.00

Wages

 

$503,000.00

$503,000.00

$503,000.00

$503,000.00

Common Costs

 

$2,106,000.00

$2,258,685.00

$2,433,733.09

$2,634,516.07

Total Operating Expenses

 

$3,199,000.00

$3,393,010.00

$3,615,435.78

$3,870,561.73

Gross Income (Income Before Taxes)

 

$10,841,000.00

$11,664,890.00

$12,609,451.48

$13,692,878.72

Income Taxes

 

$3,794,350.00

$4,082,711.50

$4,413,308.02

$4,792,507.55

Net Income

 

$7,046,650.00

$7,582,178.50

$8,196,143.46

$8,900,371.17

Input Data: Assumptions and Additional Information

Base Period Sales

$4,000,000.00

$4,290,000.00

$4,622,475.00

$5,003,829.19

 

Store Items

% of Sales

CGS

 

 Rates

Deli

6.00%

50.00%

 

2018 Growth

7.25%

Dairy

18.00%

50.00%

 

2019 Growth

7.75%

Canned Goods

12.00%

75.00%

 

2020 Growth

8.25%

Frozen Foods

20.00%

65.00%

 

Tax

35.00%

Meats

21.00%

50.00%

 

 

 

Produce

12.50%

65.00%

 

 

 

Dry Goods

9.00%

66.00%

 

 

 

Gift Card

1.50%

30.00%

 

 

 

 

100.00%

 

 

                  Salaries

Operating Expenses

 

 

 

Luke

15.00%

Sales and Marketing

5.50%

 

 

Manager

$55,000.00

General and Administrative

8.75%

 

 

Asst. Manager

$40,000.00

Depreciation Expense

$20,000.00

 

 

Total Working Hours in a year

2000

Wage

$12.00

 

 

No. of Employees

17

Answer to Question No. 1

Maxi's Food Mart

Pro Forma Income Statement

 

Prepared on:

February 1, 2018

 

 

 

2017

2018

2019

2020

Sales

 

 

 

 

Deli

$240,000.00

$261,600.00

$286,452.00

$315,097.20

Dairy

$720,000.00

$784,800.00

$859,356.00

$945,291.60

Canned Goods

$480,000.00

$523,200.00

$572,904.00

$630,194.40

Frozen Foods

$800,000.00

$872,000.00

$954,840.00

$1,050,324.00

Meats

$840,000.00

$915,600.00

$1,002,582.00

$1,102,840.20

Produce

$500,000.00

$545,000.00

$596,775.00

$656,452.50

Dry Goods

$360,000.00

$392,400.00

$429,678.00

$472,645.80

Gift Card

$60,000.00

$65,400.00

$71,613.00

$78,774.30

Total Sales

$4,000,000.00

$4,360,000.00

$4,774,200.00

$5,251,620.00

Cost of Goods Sold (CGS)

 

 

 

 

Deli

$2,000,000.00

$2,180,000.00

$2,387,100.00

$2,625,810.00

Dairy

$2,000,000.00

$2,180,000.00

$2,387,100.00

$2,625,810.00

Canned Goods

$3,000,000.00

$3,270,000.00

$3,580,650.00

$3,938,715.00

Frozen Foods

$2,600,000.00

$2,834,000.00

$3,103,230.00

$3,413,553.00

Meats

$2,000,000.00

$2,180,000.00

$2,387,100.00

$2,625,810.00

Produce

$2,600,000.00

$2,834,000.00

$3,103,230.00

$3,413,553.00

Dry Goods

$2,640,000.00

$2,877,600.00

$3,150,972.00

$3,466,069.20

Gift Card

$1,200,000.00

$1,308,000.00

$1,432,260.00

$1,575,486.00

Total Cost of Goods Sold (CGS)

$18,040,000.00

$19,663,600.00

$21,531,642.00

$23,684,806.20

Gross Profit

$14,040,000.00

$15,303,600.00

$16,757,442.00

$18,433,186.20

Operating Expenses

 

 

 

 

Sales & Marketing

$220,000.00

$239,800.00

$262,581.00

$288,839.10

General & Administrative

$350,000.00

$381,500.00

$417,742.50

$459,516.75

Depreciation

$20,000.00

$20,000.00

$20,000.00

$20,000.00

Wages

$503,000.00

$503,000.00

$503,000.00

$503,000.00

Common Costs

$2,106,000.00

$2,295,540.00

$2,513,616.30

$2,764,977.93

Total Operating Expenses

$3,199,000.00

$3,439,840.00

$3,716,939.80

$4,036,333.78

Gross Income (Income Before Taxes)

$10,841,000.00

$11,863,760.00

$13,040,502.20

$14,396,852.42

Income Taxes

$3,794,350.00

$4,152,316.00

$4,564,175.77

$5,038,898.35

Net Income

$7,046,650.00

$7,711,444.00

$8,476,326.43

$9,357,954.07

Input Data: Assumptions and Additional Information

Base Period Sales

$4,360,000.00

$4,774,200.00

$5,251,620.00

 

Store Items

CGS

 

 Rates

Deli

50.00%

 

2018 Growth

9.00%

Dairy

50.00%

 

2019 Growth

9.50%

Canned Goods

75.00%

 

2020 Growth

10.00%

Frozen Foods

65.00%

 

Tax

35.00%

Meats

50.00%

 

 

 

Produce

65.00%

 

 

 

Dry Goods

66.00%

 

 

 

Gift Card

30.00%

 

 

 

 

 

 

                  Salaries

Operating Expenses

 

 

Luke

15.00%

Sales and Marketing

 

 

Manager

$55,000.00

General and Administrative

 

 

Asst. Manager

$40,000.00

Depreciation Expense

 

 

Total Working Hours in a year

2000

Wage

 

 

No. of Employees

17

Answer to Question No. 2

Maxi's Food Mart

Pro Forma Income Statement

 

 

Prepared on:

##############

 

 

 

 

 

 

 

 

 

 

2017

2018

2019

2020

Sales

 

 

 

 

 

Deli

 

$240,000.00

$249,600.00

$262,080.00

$276,494.40

Dairy

 

$720,000.00

$748,800.00

$786,240.00

$829,483.20

Canned Goods

 

$480,000.00

$499,200.00

$524,160.00

$552,988.80

Frozen Foods

 

$800,000.00

$832,000.00

$873,600.00

$921,648.00

Meats

 

$840,000.00

$873,600.00

$917,280.00

$967,730.40

Produce

 

$500,000.00

$520,000.00

$546,000.00

$576,030.00

Dry Goods

 

$360,000.00

$374,400.00

$393,120.00

$414,741.60

Gift Card

 

$60,000.00

$62,400.00

$65,520.00

$69,123.60

Total Sales

 

$4,000,000.00

$4,160,000.00

$4,368,000.00

$4,608,240.00

Cost of Goods Sold (CGS)

 

 

 

 

 

Deli

 

$2,000,000.00

$2,080,000.00

$2,184,000.00

$2,304,120.00

Dairy

 

$2,000,000.00

$2,080,000.00

$2,184,000.00

$2,304,120.00

Canned Goods

 

$3,000,000.00

$3,120,000.00

$3,276,000.00

$3,456,180.00

Frozen Foods

 

$2,600,000.00

$2,704,000.00

$2,839,200.00

$2,995,356.00

Meats

 

$2,000,000.00

$2,080,000.00

$2,184,000.00

$2,304,120.00

Produce

 

$2,600,000.00

$2,704,000.00

$2,839,200.00

$2,995,356.00

Dry Goods

 

$2,640,000.00

$2,745,600.00

$2,882,880.00

$3,041,438.40

Gift Card

 

$1,200,000.00

$1,248,000.00

$1,310,400.00

$1,382,472.00

Total Cost of Goods Sold (CGS)

 

$18,040,000.00

$18,761,600.00

$19,699,680.00

#############

Gross Profit

 

$14,040,000.00

$14,601,600.00

$15,331,680.00

#############

Operating Expenses

 

 

 

 

 

Sales & Marketing

 

$220,000.00

$228,800.00

$240,240.00

$253,453.20

General & Administrative

 

$350,000.00

$364,000.00

$382,200.00

$403,221.00

Depreciation

 

$20,000.00

$20,000.00

$20,000.00

$20,000.00

Wages

 

$503,000.00

$503,000.00

$503,000.00

$503,000.00

Common Costs

 

$2,106,000.00

$2,190,240.00

$2,299,752.00

$2,426,238.36

Total Operating Expenses

 

$3,199,000.00

$3,306,040.00

$3,445,192.00

$3,605,912.56

Gross Income (Income Before Taxes)

 

$10,841,000.00

$11,295,560.00

$11,886,488.00

#############

Income Taxes

 

$3,794,350.00

$3,953,446.00

$4,160,270.80

$4,399,153.44

Net Income

 

$7,046,650.00

$7,342,114.00

$7,726,217.20

$8,169,856.40

Input Data: Assumptions and Additional Information

Base Period Sales

$4,000,000.00

$4,160,000.00

$4,368,000.00

$4,608,240.00

 

Store Items

% of Sales

CGS

 

 Rates

Deli

6.00%

50.00%

 

2018 Growth

4.00%

Dairy

18.00%

50.00%

 

2019 Growth

5.00%

Canned Goods

12.00%

75.00%

 

2020 Growth

5.50%

Frozen Foods

20.00%

65.00%

 

Tax

35.00%

Meats

21.00%

50.00%

 

 

 

Produce

12.50%

65.00%

 

 

 

Dry Goods

9.00%

66.00%

 

 

 

Gift Card

1.50%

30.00%

 

 

 

 

100.00%

 

 

                  Salaries

Operating Expenses

 

 

 

Luke

15.00%

Sales and Marketing

5.50%

 

 

Manager

$55,000.00

General and Administrative

8.75%

 

 

Asst. Manager

$40,000.00

Depreciation Expense

$20,000.00

 

 

Total Working Hours in a year

2000

Wage

$12.00

 

 

No. of Employees

17

Answer to Question No. 3

Maxi's Food Mart

Pro Forma Income Statement

 

 

Prepared on:

February 1, 2018

 

 

 

 

2017

2018

2019

2020

Deli

 

$240,000.00

$257,400.00

$277,348.50

$300,229.75

Dairy

 

$720,000.00

$772,200.00

$832,045.50

$900,689.25

Canned Goods

 

$480,000.00

$514,800.00

$554,697.00

$600,459.50

Frozen Foods

 

$800,000.00

$858,000.00

$924,495.00

$1,000,765.84

Meats

 

$840,000.00

$900,900.00

$970,719.75

$1,050,804.13

Produce

 

$500,000.00

$536,250.00

$577,809.38

$625,478.65

Dry Goods

 

$360,000.00

$386,100.00

$416,022.75

$450,344.63

Gift Card

 

$60,000.00

$64,350.00

$69,337.13

$75,057.44

Total Sales

 

$4,000,000.00

$4,290,000.00

$4,622,475.00

$5,003,829.19

Cost of Goods Sold (CGS)

 

 

 

 

 

Deli

 

$2,000,000.00

$2,145,000.00

$2,311,237.50

$2,501,914.59

Dairy

 

$2,000,000.00

$2,145,000.00

$2,311,237.50

$2,501,914.59

Canned Goods

 

$3,000,000.00

$3,217,500.00

$3,466,856.25

$3,752,871.89

Frozen Foods

 

$2,600,000.00

$2,788,500.00

$3,004,608.75

$3,252,488.97

Meats

 

$2,000,000.00

$2,145,000.00

$2,311,237.50

$2,501,914.59

Produce

 

$2,600,000.00

$2,788,500.00

$3,004,608.75

$3,252,488.97

Dry Goods

 

$2,640,000.00

$2,831,400.00

$3,050,833.50

$3,302,527.26

Gift Card

 

$1,200,000.00

$1,287,000.00

$1,386,742.50

$1,501,148.76

Total Cost of Goods Sold (CGS)

 

$18,040,000.00

$19,347,900.00

$20,847,362.25

$22,567,269.64

Gross Profit

 

$14,040,000.00

$15,057,900.00

$16,224,887.25

$17,563,440.45

Operating Expenses

 

 

 

 

 

Sales & Marketing

 

$220,000.00

$235,950.00

$254,236.13

$275,210.61

General & Administrative

 

$350,000.00

$375,375.00

$404,466.56

$437,835.05

Depreciation

 

$20,000.00

$20,000.00

$20,000.00

$20,000.00

Wages

 

$571,000.00

$571,000.00

$571,000.00

$571,000.00

Common Costs

 

$1,123,200.00

$1,204,632.00

$1,297,990.98

$1,405,075.24

Total Operating Expenses

 

$2,284,200.00

$2,406,957.00

$2,547,693.67

$2,709,120.90

Gross Income (Income Before Taxes)

 

$11,755,800.00

$12,650,943.00

$13,677,193.58

$14,854,319.55

Income Taxes

 

$4,114,530.00

$4,427,830.05

$4,787,017.75

$5,199,011.84

Net Income

 

$7,641,270.00

$8,223,112.95

$8,890,175.83

$9,655,307.71

Input Data: Assumptions and Additional Information

Base Period Sales

$4,000,000.00

$4,290,000.00

$4,622,475.00

$5,003,829.19

 

Store Items

% of Sales

CGS

 

 Rates

Deli

6.00%

50.00%

 

2018 Growth

7.25%

Dairy

18.00%

50.00%

 

2019 Growth

7.75%

Canned Goods

12.00%

75.00%

 

2020 Growth

8.25%

Frozen Foods

20.00%

65.00%

 

Tax

35.00%

Meats

21.00%

50.00%

 

 

 

Produce

12.50%

65.00%

 

 

 

Dry Goods

9.00%

66.00%

 

 

 

Gift Card

1.50%

30.00%

 

 

 

 

100.00%

 

 

                  Salaries

Operating Expenses

 

 

 

Luke

8.00%

Sales and Marketing

5.50%

 

 

Manager

$55,000.00

General and Administrative

8.75%

 

 

Asst. Manager

$40,000.00

Depreciation Expense

$20,000.00

 

 

Total Working Hours in a year

2000

Wage

$14.00

 

 

No. of Employees

17

Answer to Question No. 4

Input Data: Assumptions and Additional Information

Base Period Sales

$4,000,000.00

$4,290,000.00

$4,622,475.00

$5,003,829.19

 

 

 

 

 

 

 

Store Items

% of Sales

CGS

 

 Rates

Deli

6.00%

50.00%

 

2018 Growth

7.25%

Dairy

18.00%

50.00%

 

2019 Growth

7.75%

Canned Goods

12.00%

75.00%

 

2020 Growth

8.25%

Frozen Foods

20.00%

65.00%

 

Tax

35.00%

Meats

21.00%

50.00%

 

 

 

Produce

12.50%

65.00%

 

 

 

Dry Goods

9.00%

66.00%

 

 

 

Gift Card

1.50%

30.00%

 

 

 

 

100.00%

 

 

                  Salaries

Operating Expenses

 

 

 

Luke

15.00%

Sales and Marketing

5.50%

 

 

Manager

$55,000.00

General and Administrative

8.75%

 

 

Asst. Manager

$40,000.00

Depreciation Expense

$20,000.00

 

 

Total Working Hours in a year

2000

Wage

$14.00

 

 

No. of Employees

19

In the given case Luke Rendall may use the what if analysis to depict the changes in the salaries as per gross profit in different situation. The what if analysis may be used in case the total cost of goods sold is higher than the revenue generated from. If the total cost of goods sold is greater than total sales and the company incurs a loss then the what of analysis will be automatically trace this and generate the result as not applicable. The what if analysis may be beneficial to trace the different types of the results based on the changes in other variables as well. For instance, in the company does not need to any depreciation for a year then it can trace it from beforehand. The What-if analysis in excel may be used in excel for setting the values in one or more formulas to explore the different set of results. In case of building two analysis Luke Rendall ay use “several different sets of values in one or more formulas to explore all the various results” (Rodgers 2016).

This analysis when used in building two budgets may assume specific level of revenue and determine the values which will be produced in excel. “Excel provides several types of the different tools to help a company perform the analysis that fits appropriately to the business needs” (Poatsy et al.2016).

There are different situations which calls for the needs to implement absolute cell referencing. It has been seen that unlike relative references, absolute references do not get altered when copied or filled. The use of this method may be used to keep a row “and/or column constant”. Absolute reference is designed with the addition to a dollar sign ($). This may be used by preceding the column reference, roe reference or both. The use of $A$ is used when the row and column do not change on copying the data (Winston 2016). The use of A$2 is used when the row does not change when copied. The use of $A2 column does not change when copied. The use of absolute cell referencing is appropriate when a specific set of data used needs to be fixed as per the required solution. The absolute cell reference use helps in minimizing the overall time required for copying the data into other cells, hence it saves time by fixing a cell and making the changes in other relevant cells in excel (Liengme 2015).

Recommendation

The main depictions relating to the changes have suggested that there is a positive relation between sales and net income.  This has been understood with the fact that when the sales have increased with higher percentage the net income has also increased accordingly. The hiring of additional employees has also demonstrated an increasing in the operating expenses.

 
References

Liengme, B., 2015. A guide to Microsoft Excel 2013 for scientists and engineers. Academic Press.

Poatsy, M.A., Mulbery, K., Davidson, J. and Grauer, R., 2016. Exploring Microsoft Office Excel 2016 Comprehensive. Pearso

Rodgers, K.A., 2016. Correlation Analysis with Excel Handout.

Winston, W., 2016. Microsoft Excel data analysis and business modeling. Microsoft press.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Maxis Food Mart Pro Forma Income Statement. Retrieved from https://myassignmenthelp.com/free-samples/cois11011-foundations-of-business-computing1.

"Maxis Food Mart Pro Forma Income Statement." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/cois11011-foundations-of-business-computing1.

My Assignment Help (2020) Maxis Food Mart Pro Forma Income Statement [Online]. Available from: https://myassignmenthelp.com/free-samples/cois11011-foundations-of-business-computing1
[Accessed 24 November 2024].

My Assignment Help. 'Maxis Food Mart Pro Forma Income Statement' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/cois11011-foundations-of-business-computing1> accessed 24 November 2024.

My Assignment Help. Maxis Food Mart Pro Forma Income Statement [Internet]. My Assignment Help. 2020 [cited 24 November 2024]. Available from: https://myassignmenthelp.com/free-samples/cois11011-foundations-of-business-computing1.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
close